Lock queues
When a row lock is taken, later writers don't fail; they line up and wait. Under load, waiters pile up behind one holder, and the pile-up itself is visible live in sys.innodb_lock_waits.
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, but every update lands.)
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
PostgreSQL grants a released lock strictly to the first waiter in line. InnoDB since 8.0.20 uses CATS, Contention-Aware Transaction Scheduling: each waiter carries a weight computed from how many other transactions it in turn blocks, and the waiter that blocks the most can jump the queue. Under light load it usually looks FIFO, but it's a scheduling heuristic, not a promise. Never build ordering guarantees on lock-grant order.
None of this queue is visible to your application until it's already in trouble, which is why sys.innodb_lock_waits is worth watching (monitoring locks): it names each waiter and its blocker. Every queued update does land eventually, barring a timeout or deadlock, but the order it lands in is the scheduler's call, not yours. One slow transaction on a hot row is all it takes to turn a healthy system into N stuck sessions, and the fixes come down to two: shorter transactions, or refusing to wait at all with NOWAIT and SKIP LOCKED.