Question 199368 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Why SELECT … FOR SHARE is not blocked by another transaction's UPDATE？

Schema:

CREATE TABLE `table0` (
  `pkId` int DEFAULT NULL,
  `pkAttr0` int NOT NULL,
  `coAttr0_0` int DEFAULT NULL,
  PRIMARY KEY (`pkAttr0`),
  KEY `table0index_pk` (`pkAttr0`),
  KEY `table0index_commAttr0` (`coAttr0_0`)
)

insert into table0 values(1,1,1);

Then execute:

session1 > begin;
session1 > update table0 set coAttr0_0=2;
session2 > begin;
session2 > select pkAttr0 from table0 for share; -- is not blocked
session2 > select * from table0 for share; -- blocked

session1 > update table0 set coAttr0_0=2; has locked the row, why is session2 > select pkAttr0 from table0 for share; still able to execute?

Tags:

2

Answers


  1. 0

    Not an expert on locks, but I think it depends on the fact that you’re select-ing an attribute that is not involved in the update operation, while select * will require the lock to be released first, as coAttr0_0 is (possibly) being updated.

    Login or Signup to reply.
  2. 0

    check the autocommit mode:

    Manual:

    If autocommit is set to 1 (the default), the LOCK IN SHARE MODE and FOR UPDATE clauses have no effect.

    https://mariadb.com/kb/en/lock-in-share-mode/

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search