Deadlocks, seen

The log says deadlock detected, one of two transactions was rolled back, and the statements named in the message are two updates that have run unchanged for a year. Neither is wrong, and nothing you change inside either one will help: what failed is a property of the pair — the order they happened to take their locks in — and that order is written down nowhere in the code the error points at.

The cycle, drawn#

A deadlock is not two statements colliding. It is a cycle in a graph of who holds what and who is waiting for what. A chain is fine: A waits for B, B waits for C, and C waits for nobody. C finishes, B moves, A moves. The waiting was slow but it was always going to end.

A cycle has no such ending. Every member is waiting on a lock only another member can release, and that member is waiting too, so no additional waiting changes the state. That is why the database does not wait it out: it breaks the loop by choosing a transaction and rolling it back, releasing everything that one held.

Here are the two transactions that draw that shape. Neither locks anything on purpose: a plain UPDATE takes a row lock on what it modifies and keeps it until the transaction ends, so the locks arrive as a side effect of the work, in whatever order it happens to be written.

-- Two sessions, interleaved. Each statement runs after the one above it.

-- T1 · reserve
BEGIN;
UPDATE stock SET reserved = reserved + 2 WHERE id = 3120;
-- ok. T1 now holds the row lock on stock item 3120 until it ends.

-- T2 · release
BEGIN;
UPDATE stock SET reserved = reserved - 1 WHERE id = 88;
-- ok. T2 now holds the row lock on stock item 88 until it ends.

-- T1 · reserve
UPDATE stock SET reserved = reserved + 1 WHERE id = 88;
-- blocks. T2 holds it. Ordinary wait so far: if T2 committed now, T1 would go on.

-- T2 · release
UPDATE stock SET reserved = reserved - 1 WHERE id = 3120;
-- the cycle closes here. T2 waits for T1, which is already waiting for T2.
-- ERROR:  deadlock detected

The third statement is only a normal wait, the kind that resolves itself thousands of times a day. It is the fourth that turns a wait into a loop — and it is nowhere near the code that will be blamed.

Reading the deadlock your database logged#

Three things to pull out of a deadlock report: the two statements, the lock modes they wanted, and the order each transaction acquired its locks in. Most people read the first two and stop, which is why the report so often looks like it contains nothing useful. The order is the bug; the statements are only where it became visible.

PostgreSQL raises the error at the moment it detects the cycle. The message is deadlock detected, SQLSTATE 40P01, condition name deadlock_detected, in Class 40, Transaction Rollback — the message text is what to grep the logs for, the SQLSTATE what to catch in code. The detail sent to the client is one line per process in the cycle, each of the form Process … waits for … on …; blocked by process …, followed by the hint “See server log for query details.” Take that literally: the wait relationships go to the client, the statements each backend was running go to the server log. Seeing only what the driver surfaced gives you the cycle’s shape and not the queries that made it.

MySQL’s InnoDB reports the same event as error 1213, symbol ER_LOCK_DEADLOCK, SQLSTATE 40001, message “Deadlock found when trying to get lock; try restarting transaction”. The detail stays in the server: SHOW ENGINE INNODB STATUS carries a section headed LATEST DETECTED DEADLOCK, naming each transaction, its statement, the locks it held, the lock it awaited, and which of the two was rolled back. Read latest literally — one slot, overwritten by the next deadlock, so capture it before you reproduce anything. innodb_print_all_deadlocks keeps them all, in the error log.

Now the distinction that saves the most wasted time, because the two are constantly filed as one incident. A lock wait timeout is not a deadlock. A timeout means one transaction held a lock longer than another was prepared to wait: that wait could have ended, and would have, if the holder had committed sooner. A deadlock means it could never have ended at all. MySQL keeps them apart down to the error — a timeout is 1205, ER_LOCK_WAIT_TIMEOUT, SQLSTATE HY000, and the documentation is specific that what gets rolled back is the statement that waited too long, not the entire transaction — a trap of its own if your code catches it and carries on committing.

Their fixes point in opposite directions. A timeout sends you after whatever held a lock that long — usually an open transaction sitting across a network call. A deadlock sends you after the order, which no timeout setting touches.

Detection itself is neither free nor always on. PostgreSQL waits deadlock_timeout before checking for a cycle, since most waits are ordinary; its documentation notes that raising it slows the reporting of real deadlock errors. InnoDB’s detector is enabled by default and can be turned off with innodb_deadlock_detect — with it off, a cycle is broken by innodb_lock_wait_timeout expiring instead, and a real deadlock reaches your logs looking like a timeout. So does one InnoDB cannot see: unless innodb_table_locks is on and autocommit off, its documentation says it cannot detect deadlocks involving a table lock set by LOCK TABLES or a lock set by another storage engine, and points at innodb_lock_wait_timeout to resolve those.

Where it goes wrong#

The version in the code block above — two transactions taking two rows in visibly opposite order — is in every explanation of deadlocks, and it is the legible one: somebody reviewing that diff would have seen it. The one that costs a week is where nobody chose an order at all.

It usually looks like a single function with a loop: take a batch of stock identifiers, update each one. There is only one code path, so there cannot be two orders — except that the order is whatever the collection yields, and the collection is a set, a dictionary built from a JSON body, or a query with no ORDER BY. Two concurrent calls whose batches overlap will sometimes take the same two rows in opposite order. With one worker it never happens; under load it does, and then refuses to happen again by hand, which is what makes it read as a haunting rather than a bug.

Sorting the identifiers before the loop fixes it, and it is one line. It does not reduce the locking; it makes the order total and identical in every caller. A consistent order means the wait graph only runs one way, and a graph that runs one way cannot loop.

Raising the lock timeout is the reflex, and with the detector at its default it does nothing. A lock wait timeout fires on a wait that is still going when the clock runs out, and a deadlock never gets that far: the detector notices the cycle the moment it closes and rolls a transaction back immediately. innodb_lock_wait_timeout is never reached, so its value cannot change a deadlock’s duration by a millisecond — the setting is not in the path. Only with detection off, or on a cycle the detector cannot see — the table-lock case above — does the timeout break it at all. Raising PostgreSQL’s deadlock_timeout does have an effect, and it is the wrong one: the cycle is detected later, so it lasts longer.

The three changes that do work, in order of preference:

Point two has a second half about the plan rather than the code. InnoDB sets record locks on every index record it scans while processing a statement, and its documentation is explicit that it does not matter whether a WHERE condition would have excluded the row; with no index suitable for the statement, MySQL scans the whole table and every row of it becomes locked. That is InnoDB at its default isolation level, REPEATABLE READ; under READ COMMITTED it holds locks only for the rows it updates or deletes, releasing the record locks on non-matching rows once the WHERE has been evaluated — which the documentation says greatly reduces the probability of deadlocks without removing it. So on InnoDB the locks a transaction holds are decided by what its statements read and not only by what they change, and the execution plan decides which. Why your index is not being used is about reading that plan.

This is the last node on the persistence roadmap. The ones before it cover pages and tuples, the write-ahead log, storage engines, indexes and isolation levels.