Skip to main content

šŸ—“ļø 13082024 1600
šŸ“Ž

innodb_gap_locks

  • Lock on a gap between index records
    • or a lock on the gap before the first or after the last index record
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
  • Regardless of whether there is an existing value in the column
  • Gaps between all existing values in the range are locked

Gapā€‹

A gap might span a single index value, multiple index values, or even be empty.

  • Part of the tradeoff between performance and concurrency, and are used in some transaction isolation levels and not others.
  • Gap locking is not needed for statements that lock rows using a unique index to search for a unique row
  • (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.)
  • For example, if theĀ idĀ column has a unique index, the following statement uses only an index-record lock for the row havingĀ idĀ value 100 and it does not matter whether other sessions insert rows in the preceding gap:
SELECT * FROM child WHERE id = 100;

IfĀ idĀ is not indexed or has a nonunique index, the statement does lock the preceding gap.

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.

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level toĀ READ COMMITTED. In this case, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

There are also other effects of using theĀ READ COMMITTEDĀ isolation level. Record locks for nonmatching rows are released after MySQL has evaluated theĀ WHEREĀ condition. ForĀ UPDATEĀ statements,Ā InnoDBĀ does aĀ ā€œsemi-consistentā€Ā read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches theĀ WHEREĀ condition of theĀ UPDATE.

References