LOCK IN SHARE MODE locks entire table
Documentation:
SELECT ... LOCK IN SHARE MODE sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.
However, some experimentation suggests that it locks more than the rows that are read.
CREATE TABLE example (a int);
START TRANSACTION;
SELECT a FROM example WHERE a = 0 LOCK IN SHARE MODE;
And then on another connection
INSERT INTO example VALUES (1);
The later connection blocks on the lock.
It would seems that LOCK IN SHARE MODE locks more than "any rows that are read".
What exactly does LOCK IN SHARE MODE lock?
Make sure you have an index on the a column. Otherwise, in order to evaluate WHERE a = 0 , it has to read every row in the table, and it will then set a lock on each row as it reads it.
ALTER TABLE example ADD INDEX (a);
上一篇: 当UPDLOCK在SQL服务器中发布时？
下一篇: 锁定共享模式锁定整个表格