14.2.10.5. SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE Locking Reads
In some circumstances, a consistent read is not convenient. For
example, you might want to add a new row into your table
child, and make sure that the child has a
parent in table parent. The following example
shows how to implement referential integrity in your application
code.
Suppose that you use a consistent read to read the table
parent and indeed see the parent of the child
in the table. Can you safely add the child row to table
child? No, because it may happen that
meanwhile some other user deletes the parent row from the table
parent without you being aware of it.
The solution is to perform the SELECT in a
locking mode using LOCK IN SHARE MODE:
SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
Performing a read in share mode means that we read the latest
available data, and set a shared mode lock on the rows we read.
A shared mode lock prevents others from updating or deleting the
row we have read. Also, if the latest data belongs to a yet
uncommitted transaction of another client connection, we wait
until that transaction commits. After we see that the preceding
query returns the parent 'Jones', we can
safely add the child record to the child
table and commit our transaction.
Let us look at another example: We have an integer counter field
in a table child_codes that we use to assign
a unique identifier to each child added to table
child. Obviously, using a consistent read or
a shared mode read to read the present value of the counter is
not a good idea because two users of the database may then see
the same value for the counter, and a duplicate-key error occurs
if two users attempt to add children with the same identifier to
the table.
Here, LOCK IN SHARE MODE is not a good
solution because if two users read the counter at the same time,
at least one of them ends up in deadlock when attempting to
update the counter.
In this case, there are two good ways to implement the reading
and incrementing of the counter: (1) update the counter first by
incrementing it by 1 and only after that read it, or (2) read
the counter first with a lock mode FOR
UPDATE, and increment after that. The latter approach
can be implemented as follows:
SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;
A SELECT ... FOR UPDATE reads the latest
available data, setting exclusive locks on each row it reads.
Thus, it sets the same locks a searched SQL
UPDATE would set on the rows.
The preceding description is merely an example of how
SELECT ... FOR UPDATE works. In MySQL, the
specific task of generating a unique identifier actually can be
accomplished using only a single access to the table:
UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();
The SELECT statement merely retrieves the
identifier information (specific to the current connection). It
does not access any table.
Locks set by IN SHARE MODE and FOR
UPDATE reads are released when the transaction is
committed or rolled back.
