Reference · Cheat sheet

InnoDB locking & deadlocks

The one-page compressed essence: locks sit on index records; writes and locking-reads take them; cycles deadlock.

Lesson: 0006Context: InnoDB, MySQL 8.0+

The one idea

Locks are on index records

InnoDB locks index records, always. Which rows lock depends on which index the query walks — so an unindexed WHERE locks everything it scans.

The three lock types

Lock	Covers	Stops
record	one index record	update/delete of that row
gap	gap between records (or before first / after last)	inserts into the gap
next-key	record + gap before it	both — default for scans in RR

Who takes what

plain SELECT	no locks — MVCC snapshot (0005)
SELECT … FOR SHARE	S (shared) — others can read, not write
SELECT … FOR UPDATE	X (exclusive) — as if updating
UPDATE / DELETE	X (exclusive) on rows walked

Deadlocks

A holds row1, wants row2 ┐ ├─ cycle → InnoDB detects → rolls back a VICTIM B holds row2, wants row1 ┘ victim gets ERROR 1213 (ER_LOCK_DEADLOCK)

Handle, don't fear: catch 1213 and retry the txn.
Reduce frequency: small/short txns · same lock order everywhere · index the WHERE columns of UPDATE/FOR UPDATE.
Autopsy: SHOW ENGINE INNODB STATUS\G → LATEST DETECTED DEADLOCK (held vs waited-for locks, and the SQL of each txn).

Diagnose live locks (8.0)

SELECT * FROM performance_schema.data_locks; -- who holds what SELECT * FROM performance_schema.data_lock_waits; -- who waits on whom SHOW ENGINE INNODB STATUS\G -- last deadlock + transactions

Source: MySQL 8.0 Manual — InnoDB Locking · Deadlocks in InnoDB · All lessons