Deadlocks, serialization failures and retries

A deadlock forms when transactions wait for each other in a circle: every one of them needs a lock that the next one holds, so none of them can ever continue. Two transfers that update the same two accounts in opposite order are the classic case. Databases detect deadlocks and break them by rolling back one transaction, the victim, with an error your code can recognise: PostgreSQL reports SQLSTATE 40P01 after waiting for its deadlock_timeout, one second by default, MySQL’s InnoDB reports error 1213 at once, and SQLite answers SQLITE_BUSY instead of starting a wait that could never end. Serialization failures, SQLSTATE 40001, are the other refusal that a second attempt can cure: the transaction was stopped because committing it could break the rules of its isolation level. Both call for the same answer: roll back, then run the whole transaction again, with a few retries at most and a random pause before each.

How deadlocks form

Asha sends 100 to Ravi while Ravi sends 200 to Asha. Each transfer updates its sender’s row first, which locks that row until the transaction ends, then asks for the receiver’s row. Transfer 1 holds Asha’s row and waits for Ravi’s; transfer 2 holds Ravi’s row and waits for Asha’s. Waiting longer cannot help, because each is waiting for the other. Deadlocks need two things: transactions that hold locks while they ask for more, and two transactions that take the same locks in a different order. Most deadlocks in applications come from that second point, and most can be designed away.

How each engine breaks a deadlock

The victim’s work is lost and the other transaction carries on as if nothing had happened. That is why the application, not the database, decides whether the victim’s work runs again.

SQLite refuses to wait, on a real database

SQLite locks the whole database file rather than rows, so its deadlocks look different: two transactions that both read, then both try to write. This program runs two such transfers in SQLite’s default rollback-journal mode, with a busy timeout of 2 seconds on each connection:

"""Two transfers that would deadlock, on SQLite in its default rollback-journal mode.

Each transfer reads both balances, then writes. Each connection is willing to wait up to
2 seconds for a lock; SQLite still refuses to wait when waiting could never end.
"""
import os
import sqlite3
import time

DB = "wallet.db"
for suffix in ("", "-journal", "-wal", "-shm"):
    if os.path.exists(DB + suffix):
        os.remove(DB + suffix)

setup = sqlite3.connect(DB, autocommit=True)
setup.execute("CREATE TABLE accounts (id INTEGER PRIMARY KEY, holder TEXT NOT NULL, balance INTEGER NOT NULL)")
setup.execute("INSERT INTO accounts VALUES (1, 'Asha', 1000), (2, 'Ravi', 1000)")
setup.close()

t1 = sqlite3.connect(DB, autocommit=True, timeout=2)  # Asha sends 100 to Ravi
t2 = sqlite3.connect(DB, autocommit=True, timeout=2)  # Ravi sends 200 to Asha
MOVE = "UPDATE accounts SET balance = balance + ? WHERE id = ?"


def took(start):
    s = time.monotonic() - start
    return "at once" if s < 0.5 else f"after waiting about {round(s)} s"


t1.execute("BEGIN")
t2.execute("BEGIN")
t1.execute("SELECT * FROM accounts").fetchall()
t2.execute("SELECT * FROM accounts").fetchall()
print("1. both transfers start and read the balances")

t1.execute(MOVE, (-100, 1))
print("2. transfer 1 writes first and now holds the write lock")

start = time.monotonic()
try:
    t2.execute(MOVE, (-200, 2))
except sqlite3.OperationalError as e:
    print(f"3. transfer 2 tries to write: {e} ({e.sqlite_errorname}), {took(start)}")
    t2.execute("ROLLBACK")
    print("4. transfer 2 rolls back, which releases its read lock")

t1.execute(MOVE, (100, 2))
start = time.monotonic()
t1.execute("COMMIT")
print(f"5. transfer 1 commits, {took(start)}")

t2.execute("BEGIN IMMEDIATE")
t2.execute(MOVE, (-200, 2))
t2.execute(MOVE, (200, 1))
t2.execute("COMMIT")
print("6. transfer 2 runs again from the start and commits")

rows = t1.execute("SELECT holder, balance FROM accounts ORDER BY id").fetchall()
print("   " + ", ".join(f"{h} {b}" for h, b in rows))

1. both transfers start and read the balances
2. transfer 1 writes first and now holds the write lock
3. transfer 2 tries to write: database is locked (SQLITE_BUSY), at once
4. transfer 2 rolls back, which releases its read lock
5. transfer 1 commits, at once
6. transfer 2 runs again from the start and commits
   Asha 1100, Ravi 900

Transfer 2 was refused at once, although it was willing to wait for 2 seconds. It held a read lock and wanted to write while transfer 1 already held the write lock; transfer 1 would have needed transfer 2’s read lock to be released before it could commit, so a wait could never end. SQLite’s documentation describes this case: rather than call the busy handler, it returns SQLITE_BUSY so that one side gives up its lock. Once transfer 2 rolled back, transfer 1 committed at once, and transfer 2 then ran again from the start, this time with BEGIN IMMEDIATE.

What to do when a transaction is refused

• Retry the whole transaction: run every statement again from BEGIN, never only the statement that failed.
• Retry only what can succeed: deadlocks and serialization failures, not constraint violations or syntax errors.
• Limit the retries: three attempts are usually enough; after that, report the failure.
• Add jitter: wait a random time before each retry, so that colliding transactions do not collide again.
• Use an idempotency key: a unique key per request makes a repeated transfer change nothing.
• Lock in one order: take row locks in the same order, for example by id, everywhere.
• Keep transactions short: the shorter a lock is held, the less often anyone waits for it.

Retries hide most deadlocks and serialization failures from users, but a retry loop without limits or jitter can turn a short conflict into a storm of repeated work. Serialization failures in particular are a normal part of running at Serializable, and PostgreSQL’s documentation expects every application at that level to be ready to retry.

Check yourself

Reading deadlock reports, and the lock-timeout trap reproduced Pro: opens with a Pro, Premium or Ultimate pass

A retry wrapper that is safe to run twice, built and tested Pro: opens with a Pro, Premium or Ultimate pass

Fewer deadlocks by design: lock order and short transactions Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• Deadlocks are cycles of lock waits; the engine rolls back a victim with 40P01 (PostgreSQL), error 1213 (MySQL) or SQLITE_BUSY (SQLite).
• Serialization failures (40001) and deadlocks call for a rollback and a retry of the whole transaction, with a limit and random waits.
• Locks taken in one order cannot form a cycle, and short transactions wait less.