Row locks

MVCC keeps plain readers lock-free, but writers — and readers who intend to write — take row locks. InnoDB's are simple: a row lock is either shared (S) or exclusive (X). S coexists with S; everything else conflicts.

FOR UPDATE blocks writers, never readers

A

B

SELECT … FOR UPDATE (locks the row)

SELECT balance→ 100 (readers don't block)

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)

Reading the locked row costs B nothing — MVCC readers don't take row locks.

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.10 · Run it yourself · Scenario source

The whole matrix: S and X

PostgreSQL has a four-mode ladder of row locks. InnoDB has two strengths, and the full compatibility story fits in one demo:

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

And an X lock blocks even the friendliest reader: FOR SHARE has to wait for a running UPDATE.

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)

PostgreSQL's FOR KEY SHARE would coexist with that UPDATE — InnoDB has no lock that weak.

Verified against MySQL 8.4.10 · Run it yourself · Scenario source

Foreign keys take row locks for you

Every INSERT into a child table locks the referenced parent row with an S lock — that's how InnoDB guarantees the parent can't vanish mid-insert. With no weaker lock available, even an innocent update of the parent's other columns has to wait:

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 the order committed, deleting the parent doesn't block — it fails on the spot.

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.10 · Run it yourself · Scenario source

The silent no-op FK

MySQL silently ignores the inline REFERENCES syntax: customer_id int REFERENCES customers (id) creates no constraint at all. Foreign keys must be declared at table level: FOREIGN KEY (customer_id) REFERENCES customers (id).

InnoDB's row-lock story fits in two strengths: S coexists with S, X conflicts with everything, and that's the entire compatibility matrix. FOR SHARE takes S; FOR UPDATE and every write take X; plain SELECTs stay out of it below SERIALIZABLE, which is why readers never wait for writers. The sharp edge is the foreign key — a child insert takes a full S lock on the parent row that even PostgreSQL's FOR KEY SHARE would have let slide, so a hot parent under busy children becomes a queue Postgres wouldn't have. All of these locks live until COMMIT or ROLLBACK, never released mid-transaction, so keeping write transactions short is the whole game. Rows are only half of InnoDB locking, though: at REPEATABLE READ it also locks the empty spaces between them, which is where gap locks come in.

Further reading

• MySQL docs: InnoDB Locking
• MySQL docs: Locks Set by Different SQL Statements
• Gap locks — the other half of InnoDB locking: locking the spaces between rows
• The same lesson on PostgreSQL