Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 32.3Mb
PDF (A4) - 32.3Mb
PDF (RPM) - 30.4Mb
HTML Download (TGZ) - 7.8Mb
HTML Download (Zip) - 7.8Mb
HTML Download (RPM) - 6.7Mb
Man Pages (TGZ) - 142.5Kb
Man Pages (Zip) - 201.7Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb


Pre-General Availability Draft: 2017-05-29

16.5.2.4 Locking Reads

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

    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.

    Note

    SELECT ... FOR SHARE is a replacement for SELECT ... LOCK IN SHARE MODE, but LOCK IN SHARE MODE remains available for backward compatibility. The statements are equivalent. However, FOR SHARE supports OF table_name, NOWAIT, and SKIP LOCKED options. See Locking Read Concurrency with NOWAIT and SKIP LOCKED.

  • SELECT ... FOR UPDATE

    For index records the search encounters, locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... FOR SHARE, 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. You traverse edges or tree branches from one place to another, while reserving the right to come back and change any of these pointer values.

All locks set by FOR SHARE and FOR UPDATE queries are released when the transaction is committed or rolled back.

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.

Locking Read 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 FOR SHARE:

SELECT * FROM parent WHERE NAME = 'Jones' FOR SHARE;

After the FOR SHARE 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 acquire an exclusive lock in the applicable row in the PARENT table waits until you are finished, that is, until 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, FOR SHARE 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.

Locking Read Concurrency with NOWAIT and SKIP LOCKED

If a row is locked by a transaction, a SELECT ... FOR UPDATE or SELECT ... FOR SHARE transaction that requests the same locked row must wait until the blocking transaction releases the row lock. This behavior prevents transactions from updating or deleting rows that are queried for updates by other transactions. However, waiting for a row lock to be released is not necessary if you want the query to return immediately when a requested row is locked, or if excluding locked rows from the result set is acceptable.

To avoid waiting for other transactions to release row locks, NO WAIT and SKIP LOCKED options may be used with SELECT ... FOR UPDATE or SELECT ... FOR SHARE locking read statements.

  • NOWAIT

    A locking read that uses NOWAIT never waits to acquire a row lock. The query executes immediately, failing with an error if a requested row is locked.

  • SKIP LOCKED

    A locking read that uses SKIP LOCKED never waits to acquire a row lock. The query executes immediately, removing locked rows from the result set.

    Note

    Queries that skip locked rows return an inconsistent view of the data. SKIP LOCKED is therefore not suitable for general transactional work. However, it may be used to avoid lock contention when multiple sessions access the same queue-like table.

NO WAIT and SKIP LOCKED only apply to row-level locks.

Statements that use NO WAIT or SKIP LOCKED are unsafe for statement based replication.

The following example demonstrates NOWAIT and SKIP LOCKED. Session 1 starts a transaction that takes a row lock on a single record. Session 2 attempts a locking read on the same record using the NOWAIT option. Because the requested row is locked by Session 1, the locking read returns immediately with an error. In Session 3, the locking read with SKIP LOCKED returns the requested rows except for the row that is locked by Session 1.

# Session 1:

mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

mysql> INSERT INTO t (i) VALUES(1),(2),(3);

mysql> START TRANSACTION;

mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+

# Session 2:

mysql> START TRANSACTION;

mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Do not wait for lock.

# Session 3:

mysql> START TRANSACTION;

mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;
+---+
| i |
+---+
| 1 |
| 3 |
+---+          

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.