Who is blocking whom

On MySQL 8.4 InnoDB, the controlled incident has one blocker A and one waiter B. The query identifies their connections and asserts that A's processlist command is Sleep with no current statement. This is a row-lock demonstration; metadata-lock diagnosis uses different state.

A

B

M

UPDATE row 1, holds lock

UPDATE row 2, then idle

UPDATE balance = 300→ ⏳ waits

who blocks whom?→ B waits on sleeping A

KILL A

⏵ UPDATE balance = 300→ completes

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

A> UPDATE accounts SET balance = 200 WHERE id = 2;
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). This run ends the connection; production use requires privileges and an application recovery decision.

M> CALL kill_session('A');
Query OK

A'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; -- B's autocommit update completed
 balance 
---------
     300 
(1 row)

B> SELECT balance FROM accounts WHERE id = 2; -- A's separate uncommitted change was rolled back
 balance 
---------
     100 
(1 row)

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

Reading the output

waiting_pid, waiting_query, and blocking_pid identify this wait edge. Sleep alone means an idle connection; combined here with a blocking InnoDB lock, it identifies an idle transaction holding that lock. It does not establish why the application left it open or how long every lock has been held.

The MySQL view manual lists sql_kill_blocking_connection as a generated statement to terminate the blocking session. The example executes KILL CONNECTION through a helper that uses lesson session tags. B's waiting update completes and its separate read of row 2 observes A's change rolled back. The final row 1 value alone would not establish that rollback, because B overwrites the same row.

This proves recovery for the demonstrated edge, not drainage of every production queue. Other blockers, rollback time, deadlocks, and client retries can change recovery. Check authorization and application effects before terminating a real connection; KILL QUERY is a different operation. The KILL manual states: "KILL CONNECTION is the same as KILL with no modifier". Termination and rollback need not complete immediately.

Correct the application path that retains the transaction. The long-transaction detector helps find candidates; it does not automatically decide which sessions are safe to end.

Further reading

• MySQL docs: sys.innodb_lock_waits
• The same lesson on PostgreSQL