Who is blocking whom
The most common production page: "everything that touches table X is hanging." Chapter 3 introduced the monitoring views; this is the end-to-end incident — detect, identify, decide, kill:
A updates a row and then… goes to lunch. The transaction stays open.
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = 200 WHERE id = 1;
Query OK, 1 row affected
B> UPDATE accounts SET balance = 300 WHERE id = 1;
⏳ B is waiting for a lock…Someone pages you: 'updates are hanging'. This is the query you paste:
M> SELECT w.waiting_pid, w.waiting_query,
w.blocking_pid, bp.command AS blocker_state, bp.info AS blocker_running
FROM sys.innodb_lock_waits w
JOIN performance_schema.processlist bp ON bp.id = w.blocking_pid;
waiting_pid | waiting_query | blocking_pid | blocker_state | blocker_running
-------------+------------------------------------------------+--------------+---------------+-----------------
pid(B) | UPDATE accounts SET balance = 300 WHERE id = 1 | pid(A) | Sleep |
(1 row)The culprit isn't running anything — command Sleep, no current statement, holding locks. sys.innodb_lock_waits even pre-writes the KILL statement for you (sql_kill_blocking_connection). The fix is blunt:
M> CALL kill_session('A');
Query OKA's transaction dies and rolls back; B gets the lock and finishes at last.
⏵ B resumes:
Query OK, 1 row affected
B> SELECT balance FROM accounts WHERE id = 1; -- A's 200 rolled back with its death; B's 300 committed
balance
---------
300
(1 row)Verified against MySQL 8.4.10 · Run it yourself · Scenario source
Reading the output
The queue view (sys.innodb_lock_waits) answers the three incident questions in a single row. Who's stuck? That's waiting_pid and its exact statement, the query your users are watching hang. Who's responsible? That's blocking_pid — and the join to the processlist is the damning part: command = Sleep, no statement running. The blocker isn't doing anything; it's an open transaction someone's code forgot to close, still holding row locks it acquired ages ago.
And what now? The view even pre-writes the remediation for you (sql_kill_blocking_connection). KILL <id> rolls the blocker's transaction back; the transcript proves the waiter completes and the blocker's uncommitted work vanishes with it.
KILL is the incident-response tool, not the fix. The fix is whatever lets a transaction sit idle while holding locks — the next lesson hunts those down before anyone gets paged.
The shape to remember: one query names waiter, blocker, and the blocker's state, and a blocker parked at command = Sleep is idle-in-transaction, locks with nobody home. Kill it and its transaction rolls back, the queue drains, and the waiter's statement finishes on its own with no retry logic in play. That the fix is a KILL and not a code change is the whole reason the next lesson exists: to find these before the page ever fires.