SQL / TRANSACTIONS AND CONCURRENCY
Deadlocks and how to escape them
Recognize a deadlock as a cycle of waits, prevent it with a consistent lock order, and recover by retrying the whole aborted transaction.
What you will learn
- Read a PostgreSQL deadlock DETAIL and name the two sessions that formed the cycle
- Lock rows in one stable order (ascending primary key) so a wait cycle cannot form
- Retry the whole transaction on SQLSTATE 40001/40P01 with a cap and random backoff
- Tell a deadlock apart from a lock wait timeout, which leaves your transaction open
Understanding Deadlocks and how to escape them
Ordinary lock waiting ends when the holder commits. A deadlock never ends on its own: session A holds row 1 and wants row 2 while session B holds row 2 and wants row 1, so the wait-for graph contains a cycle and patience cannot break it. Every serious engine therefore hunts for cycles and kills a participant: PostgreSQL runs its detector once a session has been blocked for deadlock_timeout (1 second by default) and aborts the session whose check found the cycle, while InnoDB tests for a cycle at lock-request time and prefers to roll back the transaction that has changed the fewest rows. The survivor's blocked statement finishes the instant the victim is rolled back.
The cause is the order in which locks are acquired, never the rows themselves. A transfer 1 -> 2 and a transfer 2 -> 1 collide because each locks the account named first in its own request; the same two transfers cannot deadlock if both lock the lower id first, because a cycle would then require some transaction to ask for a lock below one it already holds, which never happens. That is why the fix is mechanical: sort the resources a transaction will touch by a key everyone computes the same way (primary key, table name, partition number) and take the locks in that order before doing any real work.
Ordering is only half the job, because not every cycle comes from row order you control: shared-to-exclusive upgrades (both sides do SELECT ... FOR SHARE then UPDATE), foreign key checks that lock a parent row, unique index insertions, and InnoDB gap locks under REPEATABLE READ all create waits you never wrote. This is why deadlock errors carry a retryable class, SQLSTATE 40001 in MySQL and 40P01 in PostgreSQL: the victim's transaction is rolled back completely, so replaying it from BEGIN is safe and normally succeeds because the winner has finished. Retry the transaction rather than the failed statement, cap the attempts, and add a small random delay so two racing retries do not re-enter in the same order and collide again.
-- PostgreSQL. Needs two psql sessions; run the steps in the numbered order.
-- one-time setup (either session)
CREATE TABLE accounts (id int PRIMARY KEY, balance numeric NOT NULL);
INSERT INTO accounts VALUES (1, 100), (2, 100);
-- Session A, step 1: take row 1
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- Session B, step 2: take row 2
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 2;
-- Session A, step 3: blocks, waiting for B's row 2
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
-- Session B, step 4: asks for A's row 1 and closes the cycle
UPDATE accounts SET balance = balance + 10 WHERE id = 1;
-- Session B, step 5
ROLLBACK;
-- Session A, step 6
COMMIT;
SELECT * FROM accounts ORDER BY id;A deadlock is a cycle in the wait-for graph, so the engine escapes it by aborting an entire transaction, which means you prevent cycles with a consistent lock order and recover by retrying from BEGIN.
Worked examples
Sorting the ids away from the caller
A transfer procedure that always locks the two rows in ascending id order, so opposite-direction transfers queue instead of deadlocking.
CREATE TABLE accounts (id int PRIMARY KEY, balance numeric NOT NULL);
INSERT INTO accounts VALUES (1, 100), (2, 100);
CREATE PROCEDURE transfer(src int, dst int, amt numeric) AS $$
DECLARE k int;
BEGIN
FOR k IN SELECT u FROM unnest(ARRAY[src, dst]) AS u ORDER BY u LOOP
PERFORM 1 FROM accounts WHERE id = k FOR UPDATE;
RAISE NOTICE 'locked id %', k;
END LOOP;
UPDATE accounts SET balance = balance - amt WHERE id = src;
UPDATE accounts SET balance = balance + amt WHERE id = dst;
END;
$$ LANGUAGE plpgsql;
BEGIN;
CALL transfer(2, 1, 25); -- money moves 2 -> 1, but row 1 is locked first
COMMIT;
SELECT * FROM accounts ORDER BY id;Example explained
Line 1unnest(ARRAY[src, dst]) ... ORDER BY u throws away the caller's argument order, so transfer(1,2,...) and transfer(2,1,...) both lock id 1 before id 2.
Line 2PERFORM ... FOR UPDATE takes one row lock per iteration, so the acquisition order comes from the loop and not from whatever scan the planner picks.
Line 3The two NOTICE lines are the proof: run this from two sessions with swapped arguments and the second one waits at 'locked id 1' instead of forming a cycle.
Line 4By the time the UPDATEs run, this transaction already holds every row it will modify, so neither UPDATE can block on anything new.
What the victim loses in InnoDB
The same two-row cycle in MySQL, showing error 1213 and that the victim's earlier statement was rolled back too.
-- MySQL 8, two clients
CREATE TABLE accounts (id INT PRIMARY KEY, balance INT) ENGINE=InnoDB;
INSERT INTO accounts VALUES (1,100),(2,100);
-- client A
START TRANSACTION;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- client B
START TRANSACTION;
UPDATE accounts SET balance = balance - 10 WHERE id = 2;
-- client A: waits for row 2
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
-- client B: closes the cycle
UPDATE accounts SET balance = balance + 10 WHERE id = 1;
-- client B: was only the failing statement undone?
SELECT id, balance FROM accounts WHERE id = 2;Example explained
Line 1Each client updates row 1 and row 2 in the opposite order; no individual statement is wrong, the pairing is.
Line 2InnoDB checks for a cycle when B requests the lock, so B fails in milliseconds instead of waiting out innodb_lock_wait_timeout.
Line 3SQLSTATE 40001 next to the vendor code 1213 is what retry code should match; PostgreSQL uses 40P01 for the same condition.
Line 4The final SELECT returns 100, not 90, because the deadlock discarded B's first UPDATE as well: B has to replay from START TRANSACTION.
Important notes
Which side becomes the victim is the engine's decision, not yours - PostgreSQL aborts whichever session's detector fires and finds the cycle, InnoDB prefers the transaction that changed the fewest rows - so both sides of a workload need retry logic.
In MySQL, error 1213 rolls back the victim's whole transaction, but error 1205 (lock wait timeout) by default rolls back only the failing statement and leaves the transaction open still holding its locks, so the two errors need different handling.
Common mistakes
Catching the deadlock error and continuing to COMMIT instead of replaying the transaction: in PostgreSQL every following command returns 'current transaction is aborted, commands ignored until end of transaction block', and in InnoDB the earlier statements are already undone, so the retried half of a transfer commits alone and money disappears.
Calling 'payer row first, then payee row' a consistent order: two transfers in opposite directions still take the rows in opposite order, so the deadlock rate grows with traffic instead of dropping.
Retrying instantly in a tight uncapped loop: both sides re-enter in the same order and deadlock again, turning a rare error into a busy loop that starves the rest of the workload.
Try it yourself
Change, predict, then run
Create accounts(id, balance) with rows 1 and 2 at 100, then write a transfer procedure that locks the two ids in ascending order with SELECT ... FOR UPDATE and raises a notice for each lock. Call it as transfer(2, 1, 25) and then transfer(1, 2, 5), and confirm both calls report locking id 1 before id 2 while the balances still add up to 200.
Open the SQL workspaceCheck your understanding
A payments service always updates the payer's row first and then the payee's row, inside one transaction. Deadlocks start appearing under load. Which change actually removes them?
- Wrap each transfer in SERIALIZABLE so the engine puts the transactions in order for you
- Lock both account rows in ascending id order, whoever the payer is, before updating either
- Raise the lock wait timeout so the blocked statement has time to acquire its second row
- Do the debit in one transaction and the credit in a second, shorter transaction
Show answer
A cycle can only form if some transaction asks for a lock ordered below one it already holds; sorting the two rows by id removes that possibility, because every transfer queues on the lower id first. SERIALIZABLE is tempting because it sounds like the engine will sequence the work, but it changes conflict checking, not acquisition order - the payer row is still locked first, so the same two-row cycle still happens, now with serialization failures added. Raising a timeout changes nothing since detection is immediate, and splitting the transfer in two removes the cycle only by abandoning atomicity.