Monitoring locks

When production "hangs", the question is always the same: who holds what, and who is waiting for whom? MySQL answers both from performance_schema.

What one UPDATE really holds — and spotting the waiter

A> BEGIN;
Query OK

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

One innocent UPDATE = two locks: an intention-exclusive on the table, an exclusive on the row.

M> SELECT object_name, index_name, lock_type, lock_mode, lock_status, lock_data
   FROM performance_schema.data_locks
   ORDER BY lock_type DESC;
 object_name | index_name | lock_type |   lock_mode   | lock_status | lock_data 
-------------+------------+-----------+---------------+-------------+-----------
 accounts    |            | TABLE     | IX            | GRANTED     |           
 accounts    | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     |         1 
(2 rows)

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

The waiter's tell: a record-lock request with lock_status = WAITING.

M> SELECT object_name, lock_mode, lock_status, lock_data
   FROM performance_schema.data_locks
   WHERE lock_status = 'WAITING';
 object_name |   lock_mode   | lock_status | lock_data 
-------------+---------------+-------------+-----------
 accounts    | X,REC_NOT_GAP | WAITING     |         1 
(1 row)

You rarely need to decode data_locks by hand — sys.innodb_lock_waits names the culprit.

M> SELECT waiting_pid, blocking_pid FROM sys.innodb_lock_waits;
 waiting_pid | blocking_pid 
-------------+--------------
 pid(B)      | pid(A)       
(1 row)

A> COMMIT;
Query OK

⏵ B resumes:
Query OK, 1 row affected

Verified against MySQL 8.4.10 · Run it yourself · Scenario source

The production cheat sheet

-- Who is blocked by whom, with the queries involved:
SELECT * FROM sys.innodb_lock_waits;

-- Every InnoDB row lock held or requested right now:
SELECT * FROM performance_schema.data_locks;

-- Sessions stuck on DDL (metadata locks, not row locks):
SELECT * FROM performance_schema.processlist
WHERE state = 'Waiting for table metadata lock';

-- Kill the blocker's statement / whole connection:
KILL QUERY <processlist_id>;   KILL <processlist_id>;

Don't poll information_schema.innodb_trx

information_schema.innodb_trx (and the older lock views) are served from a cache that refreshes only after it has been idle for 100 ms — a monitoring loop that queries it faster than that keeps reading one stale snapshot and never sees it change. performance_schema.data_locks reads live engine state. (This site's own test harness learned that the hard way.)

Two tables answer the question: performance_schema.data_locks lists every lock, GRANTED and WAITING, while sys.innodb_lock_waits pre-joins the waiter→blocker graph with the offending queries and thread ids. An ordinary UPDATE, seen through them, holds an intention-exclusive (IX) lock on the table plus one X record lock per row it changed, and the intention lock is how row and table locks coexist without checking each other row by row. One blind spot: MDL waits never show up in data_locks, so a stuck migration is visible only in processlist as Waiting for table metadata lock. PostgreSQL asks the same questions of pg_locks and pg_blocking_pids() (compare); with the locking picture complete, the next chapter turns to the machinery that lets readers avoid all of it — MVCC and the undo log.

Further reading

• MySQL docs: The data_locks Table
• MySQL docs: The innodb_lock_waits View
• The same lesson on PostgreSQL