Row locks

In MySQL 8.4 InnoDB, record locks protect index records. Shared (S) record locks coexist; exclusive (X) record locks conflict with S and X requests from other transactions. This is a Documented contract, not a compatibility rule for every InnoDB lock type. Gap locks behave differently.

FOR UPDATE blocks writers, not consistent reads

The scenario uses the default REPEATABLE READ isolation and an existing primary-key row. B's nonlocking SELECT reads the committed version while its UPDATE waits for A. Locking reads can wait; plain SELECT in an explicit SERIALIZABLE transaction can also take locks. A consistent read avoids record-lock waits, but can still wait for metadata locks.

A

B

SELECT … FOR UPDATE (locks the row)

consistent SELECT balance→ 100

UPDATE -10→ ⏳ waits

COMMIT (releases the lock)

⏵ UPDATE -10→ completes

A> BEGIN;
Query OK

A> SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
 id | owner | balance 
----+-------+---------
  1 | alice |     100 
(1 row)

B's consistent SELECT does not request a record lock and completes while A holds its lock.

B> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     100 
(1 row)

Writing to it is another story: B's UPDATE must wait for A.

B> UPDATE accounts SET balance = balance - 10 WHERE id = 1;
⏳ B is waiting for a lock…

A> UPDATE accounts SET balance = 150 WHERE id = 1;
Query OK, 1 row affected

A> COMMIT; -- releases the row lock
Query OK

⏵ B resumes:
Query OK, 1 row affected

B> SELECT balance FROM accounts WHERE id = 1; -- B's -10 applied on top of A's committed 150
 balance 
---------
     140 
(1 row)

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

The whole matrix: S and X

The transcript demonstrates S/S compatibility, an UPDATE waiting for S, and FOR SHARE waiting for X. The full record-lock matrix comes from the manual above.

A

B

FOR SHARE (S)

FOR SHARE (S) — coexists

UPDATE (X) — needs the row→ ⏳ waits

COMMIT

⏵ UPDATE (X) — needs the row→ completes

UPDATE (X)

FOR SHARE (S) — vs X→ ⏳ waits

COMMIT

⏵ FOR SHARE (S) — vs X→ completes

Two FOR SHARE locks on the same row coexist happily…

A> BEGIN;
Query OK

A> SELECT id FROM accounts WHERE id = 1 FOR SHARE;
 id 
----
  1 
(1 row)

B> BEGIN;
Query OK

B> SELECT id FROM accounts WHERE id = 1 FOR SHARE;
 id 
----
  1 
(1 row)

B> COMMIT;
Query OK

…but a FOR SHARE still blocks a plain UPDATE (which needs an X lock).

B> UPDATE accounts SET balance = 200 WHERE id = 1;
⏳ B is waiting for a lock…

A> COMMIT;
Query OK

⏵ B resumes:
Query OK, 1 row affected

An X record lock conflicts with FOR SHARE's S request; this locking reader waits.

A> BEGIN;
Query OK

A> UPDATE accounts SET balance = 300 WHERE id = 1;
Query OK, 1 row affected

B> SELECT id FROM accounts WHERE id = 1 FOR SHARE;
⏳ B is waiting for a lock…

A> COMMIT;
Query OK

⏵ B resumes:
 id 
----
  1 
(1 row)

The S/X record-lock contract is documented in the MySQL 8.4 locking manual; gap locks have different compatibility.

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

Foreign keys take row locks for you

For this declared foreign key, the child INSERT's parent check holds a shared record lock. B's non-key UPDATE waits, and the later DELETE fails with 1451 under the default RESTRICT action. Other referential actions, such as CASCADE, change the deletion behavior.

A

B

INSERT child (S-locks parent)

UPDATE parent balance→ ⏳ waits

COMMIT — releases S lock

⏵ UPDATE parent balance→ completes

DELETE parent← 1451 Cannot delete or update a parent row: a foreign key constraint fails (`app`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

A> BEGIN;
Query OK

A> INSERT INTO orders VALUES (1, 1); -- FK check takes an S lock on customers row 1
Query OK, 1 row affected

InnoDB has no key-share granularity: B's harmless balance update needs an X lock and waits.

B> UPDATE customers SET balance = 50 WHERE id = 1;
⏳ B is waiting for a lock…

A> COMMIT;
Query OK

⏵ B resumes:
Query OK, 1 row affected

With this RESTRICT foreign key and committed child, the parent DELETE fails with 1451.

B> DELETE FROM customers WHERE id = 1; -- ER_ROW_IS_REFERENCED_2
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`app`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

B> SELECT balance FROM customers WHERE id = 1; -- the parent survived, with B's update applied after the wait
 balance 
---------
      50 
(1 row)

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

Declare the constraint

MySQL 8.4 parses inline column REFERENCES without enforcing a foreign key. Use a table-level FOREIGN KEY declaration, as the CREATE TABLE manual specifies.

Locking-read locks last until transaction end. Do not generalize that lifetime to all record locks: at READ COMMITTED, locks on nonmatching rows can be released after predicate evaluation. Keep transactions short and inspect the actual query/index path. Next: gap locks.

Further reading

• MySQL docs: Locks Set by Different SQL Statements
• The PostgreSQL record-lock modes

The reconstructed support assessment records the exact manual support and execution limits for this lesson.