SELECT … FOR UPDATE on non-existent rows

Question

i am interested about locks when "select ... for update on non-existed rows"; currently, the data in table 'test02' is as blow:

id

1

3

8

15

20

the column id is primary key;

when i run :

session1: start transaction; select * from test02 where id = 7 for update;

then i open another session, and runs as fellows:

session2: start transaction; select * from test02 where id = 7 for update;

as i expected, session2 will waiting for gap lock (3, 8); however the two session runs normally. is there anyone can help to explain why session2 does not wait ?

but when i run following sql in session3: insert into test02(id) values (7); the session blocked, it means gap lock in session1 is used. but i don't understand why session2 can run normally.

mysql version 5.7 and 5.8 both show the same result.

Answer 1

From the MySql documentation (version 8.0):

It is also worth noting here that conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.

Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

In your example, session 2 does not wait for session 1 because that is just the way gap locks work in MySQL. To expect otherwise is to expect behavior that contradicts the product documentation.