From the MySQL manual:
126.96.36.199. Next-Key Locking: Avoiding the Phantom Problem
In row-level locking, InnoDB uses an algorithm called next-key
locking. InnoDB performs 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 having an identifier
value greater than 100, with the intention 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 scans 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 to
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 prevents 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.
As far as I can judge locking FOR UPDATE can effectively save us from
the burden of parse-db-error-messages-and-retry approach, provided
that unique index is set on the corresponding column of course.
I dream I could do like this:
validates_uniqueness_of :something, :lock => true
and this would use the statements like the following:
SELECT .... WHERE something = ? FOR UPDATE
This is not equal to table-level locking as in our case (uniqueness
checking) only access to a certain index gap is serialized and neither
other gaps nor existing rows are locked.