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.10 · 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.