MySQL 5.0 Reference Manual  /  ...  /  SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE Locking Reads

14.2.8.5 SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE Locking Reads

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 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.

  • 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.)

These clauses are primarily useful when dealing with tree-structured or graph-structured data, either in a single table or split across multiple tables.

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.

To implement reading and incrementing the counter, first perform a locking read of the counter using FOR UPDATE, and then increment the counter. For example:

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.

Note

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
  Posted by Viktor Söderqvist on August 26, 2009
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;
  Posted by on May 1, 2010
SELECT FOR UPDATE does not work when used in select statement with a subquery.
  Posted by Yvon Blais on October 28, 2011
After the example:

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

the description:

Any transaction that tries to read or write to the applicable row in the PARENT table waits until you are finished...

should read:

Any transaction that tries to write to the applicable row in the PARENT table waits until you are finished...

Reading by other sessions *is* allowed using SHARE MODE.
  Posted by Miha Svalina on December 12, 2013
I misunderstood select lock in share mode. If second (other) transaction is not blocked to select the same row(s) in share mode than if the first transaction executes update on locked row(s), update query fails because shared lock was successfull for second transaction.

mysql> create table child_codes (counter_field integer);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into child_codes set counter_field = 1;
Query OK, 1 row affected (0.00 sec)

session 2 (terminal 2):

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select counter_field from child_codes lock in share mode;
+---------------+
| counter_field |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)

session 1 (terminal 1):

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select counter_field from child_codes lock in share mode;
+---------------+
| counter_field |
+---------------+
| 1 |
+---------------+

session 2 (terminal 2):

mysql> update child_codes set counter_field = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Sign Up Login You must be logged in to post a comment.