(mysql.info.gz) InnoDB Next-key locking

Info Catalog

(mysql.info.gz) InnoDB locking reads

(mysql.info.gz) InnoDB transaction model

(mysql.info.gz) InnoDB Consistent read example

 
 15.11.5 Next-Key Locking: Avoiding the Phantom Problem
 ------------------------------------------------------
 
 In row-level locking, `InnoDB' uses an algorithm called "next-key
 locking."  `InnoDB' does the row-level locking in such a way that when
 it searches or scans an index of a table, it sets shared or exclusive
 locks on the index records it encounters. Thus the row-level locks are
 actually index record locks.
 
 The locks `InnoDB' sets on index records also affect the "gap" before
 that index record. If a user has a shared or exclusive lock on record
 `R' in an index, another user cannot insert a new index record
 immediately before `R' in the index order.  This locking of gaps is
 done to prevent the so-called "phantom problem." Suppose that you want
 to read and lock all children from the `child' table with an identifier
 value larger than 100, with the intent of updating some column in the
 selected rows later:
 
      SELECT * FROM child WHERE id > 100 FOR UPDATE;
 
 Suppose that there is an index on the `id' column. The query will scan
 that index starting from the first record where `id' is bigger than 100.
 If the locks set on the index records would not lock out inserts made
 in the gaps, a new row might meanwhile be inserted to the table. If you
 execute the same `SELECT' within the same transaction, you would see a
 new row in the result set returned by the query.  This is contrary the
 isolation principle of transactions: A transaction should be able to
 run so that the data it has read does not change during the
 transaction. If we regard a set of rows as a data item, the new
 "phantom" child would violate this isolation principle.
 
 When `InnoDB' scans an index, it can also lock the gap after the last
 record in the index. Just that happens in the previous example: The
 locks set by `InnoDB' prevent any insert to the table where `id' would
 be bigger than 100.
 
 You can use next-key locking to implement a uniqueness check in your
 application: If you read your data in share mode and do not see a
 duplicate for a row you are going to insert, then you can safely insert
 your row and know that the next-key lock set on the successor of your
 row during the read will prevent anyone meanwhile inserting a duplicate
 for your row. Thus the next-key locking allows you to "lock" the
 non-existence of something in your table.
 

Info Catalog

(mysql.info.gz) InnoDB locking reads

(mysql.info.gz) InnoDB transaction model

(mysql.info.gz) InnoDB Consistent read example