LOCK IN SHARE MODE locks entire table

Question

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?

Answer 1

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);