Locking

Four levels, from coarsest to finest. Most people know the last one and are surprised by the second.

Every statement that touches a table takes a metadata lock on it, held until the transaction ends — not until the statement ends. Their purpose is to stop the table definition changing underneath a running transaction.

The failure mode is specific and catastrophic, and it is worth walking through carefully because it looks impossible when it happens.

-- 1. Confirm it is MDL. Look for "Waiting for table metadata lock".
SELECT id, user, host, db, command, time, state,
       LEFT(info, 80) AS query
FROM information_schema.processlist
WHERE state LIKE '%metadata lock%'
   OR command = 'Query'
ORDER BY time DESC;

-- 2. Who HOLDS the lock the ALTER wants? (needs the MDL instrument)
SELECT object_type, object_schema, object_name,
       lock_type, lock_status, owner_thread_id
FROM performance_schema.metadata_locks
WHERE object_schema = 'yourdb' AND object_name = 'users';

-- Enable it first if that returns nothing:
UPDATE performance_schema.setup_instruments
   SET enabled = 'YES' WHERE name = 'wait/lock/metadata/sql/mdl';

-- 3. Map the holding thread back to a connection you can kill.
SELECT m.owner_thread_id, t.processlist_id, t.processlist_user,
       t.processlist_host, t.processlist_command, t.processlist_time
FROM performance_schema.metadata_locks m
JOIN performance_schema.threads t ON t.thread_id = m.owner_thread_id
WHERE m.lock_status = 'GRANTED' AND m.object_name = 'users';

-- 4. Prevention: never let an ALTER wait forever.
SET SESSION lock_wait_timeout = 5;    -- seconds, default 31536000 (!)
ALTER TABLE users ADD COLUMN x INT;
--   fails fast instead of taking the site down. Retry in a loop.

A problem: someone wants to lock a whole table. To know whether that is safe, they would have to check every row for existing row locks — which is impossibly slow on a large table.

The solution is to declare intent at the table level first. Before taking a row lock, a transaction takes a lightweight intention lock on the table: IS (intention shared) or IX (intention exclusive). Now a table-level request only has to check one table-level structure.

Crucially, IS and IX do not conflict with each other. Many transactions can hold IX on a table simultaneously; they only conflict at the row level.

Read it as: can the row lock be granted while the column lock is held? The practical takeaway is the IX/IX cell — normal concurrent writing does not conflict at table level at all.

The source of most InnoDB surprises. InnoDB does not only lock rows that exist — it locks the spaces between them, to prevent phantoms.

CREATE TABLE t (id INT PRIMARY KEY, v INT, INDEX idx_v (v));
INSERT INTO t VALUES (1,10),(5,50),(10,100),(20,200);

BEGIN;
SELECT * FROM t WHERE id BETWEEN 5 AND 10 FOR UPDATE;

-- THE query. Shows every lock, its type, and the exact record.
SELECT object_name, index_name, lock_type, lock_mode,
       lock_status, lock_data
FROM performance_schema.data_locks;

-- lock_mode tells you which kind:
--   X                  → next-key  (record + gap before it)
--   X,REC_NOT_GAP       → record only
--   X,GAP               → gap only
--   X,INSERT_INTENTION  → waiting to insert into a gap

ROLLBACK;

An insert into a gap first takes an insert intention lock, signalling "I intend to insert here". Two inserts into the same gap at different positions do not conflict — that is the point, it keeps concurrent inserts fast. But an insert intention lock does conflict with a gap lock held by someone else.

This produces the most famous InnoDB deadlock, and it involves no shared rows at all:

t1 BEGIN;
t2 SELECT * FROM t WHERE id=7
     FOR UPDATE;
     -- no row 7 exists.
     -- takes a GAP LOCK on (5,10)

t5 INSERT INTO t VALUES (7,70);
     -- needs insert intention in (5,10)
     -- B holds a gap lock there → WAIT

     -- ERROR 1213: Deadlock found

t3 BEGIN;
t4 SELECT * FROM t WHERE id=8
     FOR UPDATE;
     -- also no row. ALSO takes a
     -- gap lock on (5,10) — gap locks
     -- do NOT conflict with each other

t6 INSERT INTO t VALUES (8,80);
     -- needs insert intention in (5,10)
     -- A holds a gap lock there → WAIT
     -- both now wait on each other

Generating the next auto-increment value needs coordination. innodb_autoinc_lock_mode chooses how much.

Mode 2 means auto-increment values are not guaranteed to be consecutive, and concurrent multi-row inserts can interleave their ranges. This is fine — but it means you must never treat an auto-increment id as a gapless sequence. Gaps also appear from rolled-back transactions under every mode; the counter does not go backwards.

InnoDB maintains a wait-for graph: an edge from each waiting transaction to the one holding what it wants. A cycle in that graph is a deadlock. Detection runs when a lock wait begins, so deadlocks are found immediately rather than after a timeout.

The victim is whichever transaction has done the least work, measured roughly by the number of rows modified and undo records written. It gets ERROR 1213 and a full rollback; the other proceeds.

This is a skill, not a fact. Here is a real deadlock section, annotated. Once you can read this, deadlocks become diagnosable rather than mysterious.

------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-09-21 14:03:11 0x7f2a1c0e7700
*** (1) TRANSACTION:                        ← the first transaction
TRANSACTION 48211, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 812, OS thread handle ..., query id 99210
  updating
UPDATE accounts SET bal = bal - 100 WHERE id = 2   ← what it was doing

*** (1) HOLDS THE LOCK(S):                  ← what it already had
RECORD LOCKS space id 421 page no 4 n bits 72
  index PRIMARY of table `bank`.`accounts`
  trx id 48211 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: 0: len 4; hex 80000001;  ← id = 1

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:  ← what it wanted
RECORD LOCKS space id 421 page no 4 n bits 72
  index PRIMARY of table `bank`.`accounts`
  trx id 48211 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: 0: len 4; hex 80000002;  ← id = 2

*** (2) TRANSACTION:                        ← the second transaction
TRANSACTION 48212, ACTIVE 2 sec starting index read
UPDATE accounts SET bal = bal + 100 WHERE id = 1   ← opposite order!

*** (2) HOLDS THE LOCK(S):
  ... hex 80000002;                         ← holds id = 2
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  ... hex 80000001;                         ← wants id = 1

*** WE ROLL BACK TRANSACTION (2)            ← the victim

How to read it, in order

1. Read the two queries. They are the only lines that map to your code.
2. Decode the hex in the record locks. hex 80000001 is InnoDB's encoding of a signed INT: the high bit is flipped, so subtract 0x80000000 to get 1. That tells you which row.
3. Compare holds versus waits. Transaction 1 holds row 1 and wants row 2. Transaction 2 holds row 2 and wants row 1. That is the cycle.
4. Read lock_mode. locks rec but not gap is a plain record lock; locks gap before rec is a gap lock; insert intention points at chapter 55's pattern.
5. Name the fix. Here the two transactions update the same two rows in opposite orders — the canonical case. Ordering the updates consistently by primary key eliminates it entirely.

SHOW ENGINE INNODB STATUS shows only the latest deadlock and is hard to parse. Since 8.0 there are proper tables for live lock state.

-- Every lock currently held or requested.
SELECT engine_transaction_id AS trx, object_name AS tbl,
       index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks;

-- The blocking graph: waiter → blocker.
SELECT * FROM performance_schema.data_lock_waits;

-- The one query worth saving. Full picture, ready to act on.
SELECT
  w.requesting_engine_transaction_id AS waiting_trx,
  wt.processlist_id                  AS waiting_conn,
  LEFT(wq.sql_text, 60)               AS waiting_query,
  w.blocking_engine_transaction_id   AS blocking_trx,
  bt.processlist_id                  AS blocking_conn,
  COALESCE(LEFT(bq.sql_text,60), '-- IDLE --') AS blocking_query,
  TIMESTAMPDIFF(SECOND, b_trx.trx_started, NOW()) AS blocker_age_s
FROM performance_schema.data_lock_waits w
JOIN performance_schema.threads wt
  ON wt.thread_id = w.requesting_thread_id
JOIN performance_schema.threads bt
  ON bt.thread_id = w.blocking_thread_id
LEFT JOIN performance_schema.events_statements_current wq
  ON wq.thread_id = w.requesting_thread_id
LEFT JOIN performance_schema.events_statements_current bq
  ON bq.thread_id = w.blocking_thread_id
LEFT JOIN information_schema.innodb_trx b_trx
  ON b_trx.trx_id = w.blocking_engine_transaction_id;

-- sys packages a simpler version of the same thing:
SELECT * FROM sys.innodb_lock_waits\G

-- How many deadlocks has this server had in total?
SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';

-- Log every deadlock to the error log, not just the last one:
SET GLOBAL innodb_print_all_deadlocks = ON;

Retry loops are a necessary backstop, not a fix. These five patterns eliminate most deadlocks structurally.

1. Order your writes consistently

The single highest-value rule. If every transaction touches rows in ascending primary key order, a cycle cannot form.

-- vulnerable: order depends on the caller
UPDATE accounts SET bal = bal - ? WHERE id = @from;
UPDATE accounts SET bal = bal + ? WHERE id = @to;

-- safe: always lock the lower id first
SELECT id FROM accounts
 WHERE id IN (@from, @to)
 ORDER BY id
 FOR UPDATE;      -- both locks acquired in a defined order
-- ...then do the two updates

2. Do not read-then-insert; insert and handle the conflict

-- deadlock-prone (chapter 55's gap lock pattern)
SELECT id FROM t WHERE k = ? FOR UPDATE;
-- if empty: INSERT INTO t ...

-- safe: one atomic statement, no gap lock phase
INSERT INTO t (k, v) VALUES (?, ?)
  ON DUPLICATE KEY UPDATE v = VALUES(v);

3. Keep transactions short, and never span a network call

Every millisecond a lock is held is a millisecond of collision surface. The rule from chapter 45 applies here too: no HTTP calls, no queue publishes, no user input inside a transaction.

4. Index the columns you filter on when locking

An UPDATE ... WHERE unindexed_col = ? must scan, and it locks every row it examines, not only the ones it modifies. A missing index turns a one-row update into a table-wide lock. This is a frequent and invisible cause of contention.

5. Consider READ COMMITTED for high-write workloads

It removes most gap locks, which removes a whole family of deadlocks. The costs: non-repeatable reads become possible, and you must use row-based binary logging. Many high-throughput shops run READ COMMITTED deliberately for exactly this reason.

Parts 4 and 5 covered what you can see and what you can touch. Part 6 covers what survives: the redo log, the doublewrite buffer, and what actually happens between COMMIT returning and the data being safe on disk.