2

As far as I know the gap lock is used to prevent phantom read, and I found gap lock is set by locking read in most articles via Google search.

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks

I guess this (set gap lock on locking read) is sufficient. Why update, delete also set gap lock.

UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

And another issue is what happened if there is no suitable index where gap lock can be attached? Does fall back to lock on the entire table?

Here we assumed that using the default transaction isolation level Repeatable Read.

Jacky1205
  • 3,273
  • 3
  • 22
  • 44

1 Answers1

2

It depends on the conditions in your SELECT, UPDATE, or DELETE. They set gap locks to prevent other concurrent sessions from adding rows to the set that would be matched by the conditions.

In InnoDB, locking statements always lock the most recent committed row versions. So they don't really obey the REPEATABLE READ snapshot. They act more like READ-COMMITTED.

Therefore, if you do a statement like this:

UPDATE FROM MyTable SET ... WHERE created_at > '2020-03-22';

It must lock the gap following the highest value of created_at, which will prevent other sessions from adding new rows.

This is to simulate REPEATABLE READ, to make sure that if you run the same UPDATE again, it will affect the same rows, and it won't accidentally affect new rows.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Another issue is what happened if there is no suitable index where gap lock can be attached? Does fall back to lock on the entire table? – Jacky1205 Mar 26 '20 at 23:17
  • 1
    Yes, I answered a similar question the other day: https://stackoverflow.com/a/60857990/20860 – Bill Karwin Mar 26 '20 at 23:35
  • 1
    By the way, how does MySQL lock the entire table, is locking the records in the clustered index one by one in primary key order? – Jacky1205 Mar 27 '20 at 07:59
  • 1
    Yes, it locks every row examined. If that's every row in the table, so be it! :-) I don't know which order the rows are locked, but it doesn't really matter. They're effectively all locked at the same time, because it acts like an atomic operation. If any row fails to be locked, the whole lock request fails. – Bill Karwin Mar 27 '20 at 15:03
  • It seems that If any row fails to be locked, the session would be blocked rather than failed immediately. – Jacky1205 Mar 29 '20 at 10:28
  • Correct — until the lock wait timeout, which is 50 seconds by default. – Bill Karwin Mar 29 '20 at 15:36