GNU Info

(mysql.info)InnoDB Next-key locking

---

Next: InnoDB Locks set Prev: InnoDB locking reads Up: InnoDB transaction model

---

Enter node , (file) or (file)node

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 so that when it searches or scans an
index of a table, it sets shared or exclusive locks on the index
records in encounters. Thus the row level locks are more precisely
called 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, then 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 I want to read and
lock all children with identifier bigger than 100 from table `CHILD',
and update some field in the selected rows.

     SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

Suppose there is an index on table `CHILD' on column `ID'. Our query
will scan that index starting from the first record where `ID' is
bigger than 100.  Now, if the locks set on the index records would not
lock out inserts made in the gaps, a new child might meanwhile be
inserted to the table. If now I in my transaction execute

     SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

again, I will see a new child in the result set the query returns.
This is against 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, then
the new 'phantom' child would break 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 will prevent any insert to the table where `ID'
would be bigger than 100.

You can use the 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.