Lock queues
In this MySQL 8.4 InnoDB schedule, two UPDATE statements wait for a transaction holding the same primary-key record. sys.innodb_lock_waits identifies B's blocker. After A commits, both updates complete and the asserted balance is 211.
Watch the pile-up
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = balance + 1 WHERE id = 1;
Query OK, 1 row affected
B> UPDATE accounts SET balance = balance + 10 WHERE id = 1;
⏳ B is waiting for a lock…A fourth session, M, can watch the wait live.
M> SELECT waiting_pid, blocking_pid FROM sys.innodb_lock_waits;
waiting_pid | blocking_pid
-------------+--------------
pid(B) | pid(A)
(1 row)
C> UPDATE accounts SET balance = balance + 100 WHERE id = 1;
⏳ C is waiting for a lock…A commits — the waiters drain. (InnoDB's CATS scheduler does not promise strict FIFO order; this run checks both completions, not their order.)
A> COMMIT;
Query OK
⏵ B resumes:
Query OK, 1 row affected
⏵ C resumes:
Query OK, 1 row affected
C> SELECT balance FROM accounts WHERE id = 1; -- 100 + 1 + 10 + 100 — nothing was lost in the pile-up
balance
---------
211
(1 row)Verified against MySQL 8.4.11 · Run it yourself · Scenario source
Grant order: don't bet on FIFO
The transaction scheduling manual documents CATS weights using "the number of transactions that a transaction blocks" and states: "If weights are equal, priority is given to the longest waiting transaction." This contract applies to transactions waiting for locks on the same object. It is not established by the transcript, which checks completion and final state without asserting grant order.
Do not build application ordering or eventual-completion promises on that schedule. A wait can end with a timeout, deadlock, cancellation, or connection loss. Shorten the holding transaction, reduce contention, or choose an appropriate NOWAIT/SKIP LOCKED policy. See monitoring locks.
Further reading
The reconstructed support assessment records the exact manual support and execution limits for this lesson.