(mysql.info) innodb-locking-reads
Info Catalog
(mysql.info) innodb-consistent-read
(mysql.info) innodb-transaction-model
(mysql.info) innodb-next-key-locking
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.
Info Catalog
(mysql.info) innodb-consistent-read
(mysql.info) innodb-transaction-model
(mysql.info) innodb-next-key-locking
automatically generated byinfo2html