If you query data and then insert or update related data within
the same transaction, the regular SELECT
statement does not give enough protection. Other transactions
can update or delete the same rows you just queried.
InnoDB supports two types of locking reads
that offer extra safety:
SELECT ... LOCK IN SHARE MODEsets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.SELECT ... FOR UPDATElocks the rows and any associated index entries, the same as if you issued anUPDATEstatement for those rows. Other transactions are blocked from updating those rows, from doingSELECT ... LOCK IN SHARE MODE, or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying undo logs on an in-memory copy of the record.)
These clauses are primarily useful when dealing with tree-structured or graph-structured data, either in a single table or split across multiple tables.
All locks set by LOCK IN SHARE MODE and
FOR UPDATE queries are released when the
transaction is committed or rolled back.
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.
Usage Examples
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. Your application
code can ensure referential integrity throughout this sequence
of operations.
First, use a consistent read to query the table
PARENT and verify that the parent row exists.
Can you safely insert the child row to table
CHILD? No, because some other session could
delete the parent row in the moment between your
SELECT and your INSERT,
without you being aware of it.
To avoid this potential issue, perform the
SELECT using LOCK IN
SHARE MODE:
SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
After the LOCK IN SHARE MODE query returns
the parent 'Jones', you can safely add the
child record to the CHILD table and commit
the transaction. Any transaction that tries to read or write to
the applicable row in the PARENT table waits
until you are finished, that is, the data in all tables is in a
consistent state.
For another example, consider an integer counter field in a
table CHILD_CODES, used to assign a unique
identifier to each child added to table
CHILD. Do not use either consistent read or a
shared mode read to read the present value of the counter,
because two users of the database could see the same value for
the counter, and a duplicate-key error occurs if two
transactions attempt to add rows with the same identifier to the
CHILD 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.
Here are two ways to implement reading and incrementing the counter without interference from another transaction:
First update the counter by incrementing it by 1, then read it and use the new value in the
CHILDtable. Any other transaction that tries to read the counter waits until your transaction commits. If another transaction is in the middle of this same sequence, your transaction waits until the other one commits.First perform a locking read of the counter using
FOR UPDATE, and then increment the counter: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.