MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.4 Locking Reads

Posted on by mtoplistChuyên mục:Top List

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

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 reads are only possible when autocommit is disabled
(either by beginning transaction with
START
TRANSACTION or by setting
autocommit to 0.

A locking read clause in an outer statement does not lock the
rows of a table in a nested subquery unless a locking read
clause is also specified in the subquery. For example, the
following statement does not lock rows in table
t2. 

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;

To lock rows in table t2, add a locking read
clause to the subquery: 

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;
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,
NOWAIT 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.

NOWAIT and SKIP LOCKED
only apply to row-level locks.

Statements that use NOWAIT 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 |
+---+

mtoplist