In some circumstances, a consistent (nonlocking) read is not
convenient and a locking read is required instead.
InnoDB
supports two types of locking reads:
SELECT ... LOCK IN
SHARE MODE
sets a shared mode lock on the rows
read. A shared mode lock enables other sessions to read the
rows but not to modify them. The rows read are the latest
available, so if they belong to another transaction that has
not yet committed, the read blocks until that transaction
ends.
For index records the search encounters,
SELECT ... FOR
UPDATE
blocks other sessions from doing
SELECT ... LOCK IN
SHARE MODE
or from reading in certain transaction
isolation levels. Consistent reads will ignore any locks set
on the records that exist in the read view. (Old versions of
a record cannot be locked; they will be reconstructed by
applying undo logs on an in-memory copy of the record.)
Locks set by LOCK IN SHARE MODE
and
FOR UPDATE
reads are released when the
transaction is committed or rolled back.
As an example of a situation in which a locking read is useful,
suppose that you want to insert a new row into a table
child
, and make sure that the child row has a
parent row in table parent
. The following
discussion describes how to implement referential integrity in
application code.
Suppose that you use a consistent read to read the table
parent
and indeed see the parent row of the
to-be-inserted child row in the table. Can you safely insert the
child row to table child
? No, because it is
possible for some other session to delete the parent row from
the table parent
in the meantime 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;
A read performed with LOCK IN SHARE MODE
reads the latest available data and sets a shared mode lock on
the rows read. A shared mode lock prevents others from updating
or deleting the row read. Also, if the latest data belongs to a
yet uncommitted transaction of another session, we wait until
that transaction ends. After we see that the LOCK IN
SHARE MODE
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
. It is not a good idea to use either
consistent read or a shared mode read to read the present value
of the counter 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 it attempts to
update the counter.
In this case, there are two good ways to implement reading and incrementing the counter:
First update the counter by incrementing it by 1, and then read it.
First perform a locking read of the counter using
FOR UPDATE
, and then increment the
counter.
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.
Locking of rows for update using SELECT FOR
UPDATE
only applies when autocommit is disabled
(either by beginning transaction with
START
TRANSACTION
or by setting
autocommit
to 0. If
autocommit is enabled, the rows matching the specification are
not locked.
User Comments
If you just want to lock a bunch of rows, without fetching any data, you can group them together using a dummy GROUP BY clause.
SELECT 1 FROM sometable WHERE somecondition GROUP BY 1 FOR UPDATE;
Add your own comment.