How to Find and Fix Database Deadlocks in PostgreSQL and MySQL: Lock Graphs, Retry Logic and Lock Ordering (2026)
A database deadlock is not a capacity problem, and adding hardware will not make it go away. It is an ordering bug: two or more transactions each hold a lock the other needs, so the engine has to kill one of them to let anything move. PostgreSQL reports it as ERROR: deadlock detected with SQLSTATE 40P01; MySQL’s InnoDB reports ERROR 1213 (40001): Deadlock found when trying to get lock. Most teams respond by wrapping the call in a retry loop and moving on, which hides the bug until traffic doubles and the retry storm becomes the outage.
This guide treats deadlocks as a diagnosable defect with a finite set of root causes. You will reproduce the cycle on purpose, capture the wait-for graph from both engines, classify it into one of five shapes, apply the fix that removes the cycle, and only then add a bounded, jittered retry as the safety net.
What this covers: how both engines detect deadlocks, a seven-step fix procedure with SQL repro scripts, a five-class taxonomy, a psycopg retry wrapper, and the monitoring that tells you whether the fix held.
Context and Background
Every relational engine that uses locking for writes can deadlock. PostgreSQL uses multi-version concurrency control (MVCC) so plain reads never block writes, but writes still take row-level locks, and explicit SELECT ... FOR UPDATE statements take more. If you want the full picture of how snapshots and isolation interact with these locks, read our explainer on MVCC, snapshot isolation and write skew first; this post assumes you know what a snapshot is.
InnoDB, the default MySQL storage engine, is also MVCC for consistent reads, but its locking writes behave differently. Under its default REPEATABLE READ isolation level InnoDB uses next-key locks, which lock an index record plus the gap before it, specifically to stop phantom rows. That design choice is the source of a whole class of MySQL deadlocks that simply do not exist in PostgreSQL.
Both engines ship a deadlock detector and both resolve a cycle the same way: pick a victim, roll it back, let the rest proceed. The PostgreSQL manual is blunt about the victim choice: which transaction gets aborted “is difficult to predict and should not be relied upon” (PostgreSQL explicit locking documentation). InnoDB “tries to pick small transactions to roll back,” where size means the number of rows inserted, updated or deleted (MySQL 8.4 deadlock detection).
The official advice from both projects converges on three points. Acquire locks on multiple objects in a consistent order. Keep transactions short. Be prepared to retry a transaction that is rolled back. The MySQL manual goes as far as “Deadlocks are not dangerous. Just try again” (Minimizing and Handling Deadlocks).
That advice is correct but incomplete for production systems in 2026. Connection poolers in transaction mode, ORMs that reorder statements, batch jobs that touch thousands of rows, and microservices that retry at three layers all change the picture. The rest of this post is about turning generic advice into a repeatable diagnosis.
How a Database Deadlock Forms: The Wait-For Graph
A database deadlock happens when the wait-for graph between transactions contains a cycle: transaction T1 waits for a lock held by T2 while T2 waits for a lock held by T1. Neither can finish, so the engine detects the cycle, aborts one transaction with SQLSTATE 40P01 (PostgreSQL) or error 1213 (MySQL), and the others proceed.

Figure 1: The wait-for graph. Each transaction owns one row lock and waits for the other’s; the detector finds the cycle and aborts a victim so the survivor can commit.
Figure 1 shows the minimal two-node cycle from the PostgreSQL manual’s own example. Solid arrows are locks held; dotted arrows are locks requested. A database deadlock is any closed loop that alternates between “waits for” and “owned by”. Real incidents often have three or four transactions in the loop, but the shape is identical, and the fix always targets one edge in the loop.
Locks, not queries, form the graph
The nodes in the graph are transactions and the edges are lock requests, which means the SQL text is only a clue. An UPDATE accounts SET balance = balance + 100 WHERE acctnum = 11111 takes a row lock on that tuple and holds it until commit or rollback. In PostgreSQL the row-level modes are FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE; a plain UPDATE that does not change key columns takes FOR NO KEY UPDATE, while DELETE takes FOR UPDATE.
Table-level locks also participate. PostgreSQL has eight table lock modes, from ACCESS SHARE (taken by every SELECT) up to ACCESS EXCLUSIVE (taken by most ALTER TABLE forms). The manual notes that every mode is a table-level lock “even if the name contains the word row”, and only ACCESS EXCLUSIVE blocks a plain SELECT. A migration waiting on ACCESS EXCLUSIVE behind a long transaction can therefore join a cycle that otherwise involves only row locks.
InnoDB adds lock types PostgreSQL does not have: gap locks, next-key locks, insert intention locks and table-level AUTO-INC locks. Gap locks are “purely inhibitive” and can co-exist, which means two transactions can each hold a gap lock on the same gap. That property is harmless until both try to insert into the gap, at which point each insert intention lock waits on the other’s gap lock.
How PostgreSQL detects a cycle
PostgreSQL does not search for deadlocks on every lock request, because doing so would be expensive. A backend that has been waiting on a lock for deadlock_timeout runs the check; the default is one second, which the manual calls “probably about the smallest value you would want in practice” (lock management settings). Only superusers and roles with the appropriate SET privilege can change it.
The practical consequence is a latency floor. Every PostgreSQL deadlock costs the participants at least deadlock_timeout of wall-clock time before anyone is aborted. At the default, a burst of 20 deadlocks per second means roughly 20 transaction-seconds per second of pure waiting across the pool, which is enough to exhaust a 20-connection pool on its own (illustrative arithmetic, not a benchmark).
The same timer drives log_lock_waits. When enabled, any wait longer than deadlock_timeout writes a log line naming the waiter and the holder, which is the single most useful diagnostic for lock trouble. It is off by default through PostgreSQL 18; the PostgreSQL 19 release notes, at release-candidate stage as of mid-November 2026, list “Enable server variable log_lock_waits by default” and a new pg_stat_lock view for per-lock-type statistics (PostgreSQL 19 release notes).
How InnoDB detects a cycle
InnoDB with innodb_deadlock_detect enabled (the default) checks for a cycle when a lock request has to wait, so a deadlock is typically detected immediately rather than after a timer. The search has hard limits. If the wait-for list exceeds 200 transactions, or the checking thread would have to inspect more than 1,000,000 locks, InnoDB treats the situation as a deadlock and rolls back the transaction doing the check, logging TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH.
On very high concurrency hot-row workloads, the detection itself can become the bottleneck because many threads wait on the same lock and each runs the search. The manual allows disabling it and relying on innodb_lock_wait_timeout instead. The default timeout is 50 seconds, and when it fires only the current statement is rolled back unless innodb_rollback_on_timeout is enabled at startup, an asymmetry that catches many retry wrappers.
InnoDB also has a blind spot. It sees table locks set by LOCK TABLES only when innodb_table_locks = 1 (the default) and autocommit = 0. Cycles that involve LOCK TABLES under other settings, or locks from another storage engine, are invisible to the detector and resolve only by timeout.
Why the engine picks a victim instead of preventing the cycle
Both engines use detection rather than prevention because prevention would require knowing every lock a transaction will ever need before it starts. Classic schemes such as wait-die or wound-wait avoid cycles by aborting based on transaction age, but they abort far more often than real cycles occur. Detection lets the common case, no cycle, run with zero overhead beyond the wait itself.
The cost is pushed to the application. The engine guarantees the cycle is broken; it does not guarantee your business operation completed. That is why every serious treatment, including the PostgreSQL manual’s section on serialization failure handling, says to retry the complete transaction, “including all logic that decides which SQL to issue and/or which values to use.”
How to Fix a Database Deadlock: Step by Step
Here is the procedure in summary. Each step is expanded below with commands and code.
- Reproduce the database deadlock deterministically with two or three scripted sessions.
- Capture the graph from the engine: PostgreSQL logs and
pg_locks, or InnoDB’sLATEST DETECTED DEADLOCK. - Read the report to identify each transaction’s held lock, requested lock and statement.
- Classify the cycle into one of five shapes: lock ordering, foreign key upgrade, gap locks, upsert and duplicate key, or missing index.
- Fix the cause: consistent ordering, locking in sorted order, smaller transactions,
NOWAITorSKIP LOCKED, or advisory locks. - Add a bounded retry with exponential backoff and full jitter around the whole transaction.
- Monitor deadlock counters and lock-wait logs so a regression shows up before customers see it.
Step 1: Reproduce the database deadlock on purpose
You cannot verify a fix for a database deadlock you cannot trigger. The fastest route is two psql or mysql sessions side by side, executing statements in an interleaved order that matches the production report. Start with the textbook cross-update; once you can trigger that reliably, adapt the script to your own tables.
-- PostgreSQL repro: run setup once
CREATE TABLE accounts (acctnum int PRIMARY KEY, balance numeric NOT NULL);
INSERT INTO accounts VALUES (11111, 1000), (22222, 1000);
-- Session A
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE acctnum = 11111;
-- Session B
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance - 100 WHERE acctnum = 11111; -- blocks
-- Session A
UPDATE accounts SET balance = balance - 100 WHERE acctnum = 22222; -- deadlock
-- After ~1s one session receives:
-- ERROR: deadlock detected (SQLSTATE 40P01)
The MySQL equivalent comes straight from the reference manual and uses shared locks to make the cycle explicit. It is worth running because it shows the lock-mode upgrade pattern, shared first and exclusive second, that drives a lot of real InnoDB incidents.
-- MySQL 8.4 repro
SET GLOBAL innodb_print_all_deadlocks = ON;
CREATE TABLE Animals (name VARCHAR(10) PRIMARY KEY, value INT) ENGINE=InnoDB;
CREATE TABLE Birds (name VARCHAR(10) PRIMARY KEY, value INT) ENGINE=InnoDB;
INSERT INTO Animals VALUES ('Aardvark', 10);
INSERT INTO Birds VALUES ('Buzzard', 20);
-- Session A
START TRANSACTION;
SELECT value FROM Animals WHERE name = 'Aardvark' FOR SHARE;
-- Session B
START TRANSACTION;
SELECT value FROM Birds WHERE name = 'Buzzard' FOR SHARE;
UPDATE Animals SET value = 30 WHERE name = 'Aardvark'; -- blocks
-- Session A
UPDATE Birds SET value = 40 WHERE name = 'Buzzard';
-- ERROR 1213 (40001): Deadlock found when trying to get lock;
-- try restarting transaction
For production-shaped repros, script the sessions rather than typing them. A short Python harness with two threads and a threading.Barrier between statements gives you a deterministic interleaving you can put in CI. That test becomes the regression guard once the fix lands.
Step 2: Capture the lock graph from the engine
The deadlock error your application logs is the least useful artifact, because it names only the victim. You need the engine’s view of every participant. Turn on the right logging before the next occurrence, not after.
On PostgreSQL, set log_lock_waits = on (it costs one log line per wait longer than deadlock_timeout) and make sure log_line_prefix includes the process ID and application name. When a database deadlock fires, the server log carries a DETAIL block listing each process, the lock it waited for, and the process that blocked it, followed by the statements. A representative shape is below; exact wording varies by lock type and version.
ERROR: deadlock detected
DETAIL: Process 4121 waits for ShareLock on transaction 9812; blocked by process 4188.
Process 4188 waits for ShareLock on transaction 9811; blocked by process 4121.
Process 4121: UPDATE accounts SET balance = balance - 100 WHERE acctnum = 22222;
Process 4188: UPDATE accounts SET balance = balance - 100 WHERE acctnum = 11111;
HINT: See server log for query details.
CONTEXT: while updating tuple (0,2) in relation "accounts"
“Waits for ShareLock on transaction” is how PostgreSQL expresses a row-lock wait: the waiter wants to see the holder’s transaction ID finish. For live blocking that has not yet become a deadlock, query the catalog directly. pg_blocking_pids() returns the PIDs blocking a given backend, including “soft” blockers that are ahead in the wait queue.
-- PostgreSQL: who is blocking whom right now
SELECT a.pid,
pg_blocking_pids(a.pid) AS blocked_by,
a.wait_event_type, a.wait_event, -- 'Lock' / 'transactionid', 'tuple', 'relation'
now() - a.xact_start AS xact_age,
left(a.query, 120) AS query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY xact_age DESC;
Do not run that query in a tight loop on a busy primary. The manual warns that frequent calls “could have some impact on database performance,” because the function takes exclusive access to the lock manager’s shared state for a short time. Every 10 to 30 seconds from a monitoring agent is fine; every 100 ms from twenty dashboards is not.
On MySQL, SHOW ENGINE INNODB STATUS\G prints the LATEST DETECTED DEADLOCK section, but only the most recent one. Enable innodb_print_all_deadlocks so every database deadlock lands in the error log, and the manual itself suggests disabling it when you finish debugging. For live waits, performance_schema.data_locks and performance_schema.data_lock_waits show held and requested locks, and the sys.innodb_lock_waits view joins them into a readable form.

Figure 2: Timeline of a PostgreSQL deadlock. Both sessions sleep on each other; after deadlock_timeout a waiter runs the cycle check, one session receives 40P01, and its rollback releases the lock the survivor needs.
Figure 2 makes the timing visible. Between the moment session A blocks and the moment the check runs, both transactions hold their locks and every other session that touches either row queues behind them. That queue is why a single deadlock can show up in your latency graphs as a one-second spike across unrelated endpoints.
Step 3: Read the report line by line
An InnoDB deadlock report has a fixed structure, and reading it in the right order saves time. For each numbered transaction you get the TRANSACTION header with its age and state, the statement it was running, HOLDS THE LOCK(S), and WAITING FOR THIS LOCK TO BE GRANTED. The final line, WE ROLL BACK TRANSACTION (n), names the victim.
Read the lock lines carefully because the qualifiers carry the diagnosis. lock_mode X locks rec but not gap is a plain record lock. lock_mode X with no qualifier is a next-key lock covering the record and the gap before it. locks gap before rec is a pure gap lock, and insert intention marks an INSERT waiting to enter a gap. lock mode S on a primary or unique index during an insert usually means a duplicate-key check.
For each transaction, write down three things: the index and key range it holds, the index and key range it wants, and the statement. Then draw the arrows. If T1 holds key 7 on PRIMARY and wants key 9, while T2 holds key 9 and wants key 7, you have an ordering cycle. If both hold gap locks on the same gap and both want insert intention, you have a gap-lock cycle.
PostgreSQL reports are terser but the same method applies. The DETAIL names the lock type: ShareLock on transaction for row waits, ExclusiveLock on tuple for a second waiter queued on the same row, AccessExclusiveLock on relation for DDL, and ExclusiveLock on advisory lock for application locks. The CONTEXT line gives the relation and tuple address being updated.
One subtlety trips people up. The statement shown for each participant is the one that was blocked, not the one that took the lock it holds. The lock it holds came from an earlier statement in the same transaction, which you will only find in application logs or pg_stat_statements. Tag every transaction with an application_name or a SQL comment carrying a request ID so you can stitch the two together.
Step 4: Classify the cycle into one of five shapes
After reviewing enough reports, almost every database deadlock falls into one of five shapes. Classifying first matters because each shape has a different fix, and applying the wrong one, for example adding retries to a missing-index problem, makes the system worse.

Figure 3: A decision tree for classifying a database deadlock report into one of five root-cause classes before choosing a fix.
Class 1, lock ordering. Two code paths touch the same rows or tables in opposite orders. Transfers between accounts, inventory reservations across SKUs, and batch updates whose WHERE id IN (...) lists arrive in different orders are the usual sources. The report shows each transaction holding the key the other wants on the same index.
Class 2, foreign key shared-lock upgrade. In InnoDB, an insert, update or delete that checks a foreign key sets shared record locks on the parent rows it inspects. Two transactions that each insert a child of the same parent both get S on the parent; if both then update that parent row, for example incrementing an order_count, each needs X and each waits for the other’s S. PostgreSQL mostly avoids this specific shape because a child insert takes FOR KEY SHARE on the parent, which does not conflict with the FOR NO KEY UPDATE lock taken by an update that leaves key columns alone. It reappears in PostgreSQL if the update changes a referenced key or the parent is deleted.
Class 3, gap and next-key locks. This one is InnoDB-specific under REPEATABLE READ. The canonical case is the “check then insert” pattern: two sessions run SELECT ... WHERE id = 105 FOR UPDATE for a row that does not exist. Each gets a gap lock on the same gap, which is allowed because gap locks co-exist, then each runs INSERT ... id = 105, and each insert intention lock waits on the other’s gap lock.
Class 4, upsert and duplicate key. InnoDB sets a shared lock on the duplicate index record when an INSERT hits a duplicate-key error. The manual’s three-session example shows the trap: session 1 inserts key 1, sessions 2 and 3 insert the same key and queue for shared locks, and when session 1 rolls back, sessions 2 and 3 both get S, both need X, and deadlock. INSERT ... ON DUPLICATE KEY UPDATE takes exclusive locks instead, and in PostgreSQL multi-row INSERT ... ON CONFLICT DO UPDATE statements can deadlock when two batches contain overlapping keys in different orders.
Class 5, missing index and wide scans. InnoDB locking reads, UPDATE and DELETE lock “every index record that is scanned”, regardless of whether the WHERE clause later excludes the row. Without a suitable index, the statement scans the whole table and “every row of the table becomes locked.” A transaction with that footprint collides with everything, so its reports look random: different keys, different partners, the same statement every time.
The tell for Class 5 is a held-locks section with thousands of record locks, or a TRANSACTION header reporting many lock struct(s) and row lock(s). In PostgreSQL the analogous problem is less severe, since row locks are taken only on rows actually modified or locked, but an unindexed foreign key on the child table makes parent deletes scan the child, lengthening the transaction and widening the window for Class 1 cycles.
Step 5: Fix the cause, not the symptom
The hierarchy of fixes is: remove the cycle, then shrink the window in which a cycle can form, then make waiting impossible where waiting is not needed. Retries come last and are covered in Step 6.
Enforce a global lock ordering
Lock ordering is the only fix that eliminates Class 1 deadlocks rather than making them rarer. Pick a total order, usually primary key ascending within a table and a fixed table order across tables, and make every code path acquire locks in that order. A transfer from account 22222 to 11111 must lock 11111 first, even though the business logic reads “debit then credit.”
In PostgreSQL the reliable way is to take all row locks up front with one ordered locking read, then run the updates. The planner applies the locking clause on top of the sort, so rows are locked in ORDER BY order. Note the manual’s caveat that at READ COMMITTED, a locking read with ORDER BY can return rows out of order if ordering columns change while it waits; ordering by an immutable primary key avoids that.
-- PostgreSQL: lock both accounts in key order, then update in any order
BEGIN;
SELECT acctnum FROM accounts
WHERE acctnum = ANY (ARRAY[22222, 11111])
ORDER BY acctnum
FOR NO KEY UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance + 100 WHERE acctnum = 11111;
COMMIT;
Do not rely on UPDATE ... WHERE id IN (...) to lock in list order; neither engine promises that, and the actual order depends on the plan. For batch jobs, sort the key list in the application before building the statement, and keep batches of a few hundred rows rather than tens of thousands so each transaction holds fewer locks for less time.
The same rule applies to the first lock a transaction takes on a row. The PostgreSQL manual advises that “the first lock acquired on an object in a transaction is the most restrictive mode that will be needed.” Reading a row FOR SHARE and later updating it is a built-in upgrade deadlock whenever two sessions do it concurrently; lock it FOR UPDATE or FOR NO KEY UPDATE from the start.
Fix the InnoDB-specific shapes
For Class 2, remove the shared-then-exclusive upgrade. Either lock the parent row exclusively first with SELECT ... FOR UPDATE before inserting children, or move the denormalised counter to a separate table or an asynchronous aggregate. Many teams find the counter was never needed transactionally.
For Class 3, consider whether READ COMMITTED is acceptable for the workload. Under READ COMMITTED, InnoDB disables gap locking for searches and index scans and uses it “only for foreign-key constraint checking and duplicate-key checking.” That removes most gap-lock cycles, at the cost of allowing phantoms; read the isolation-level trade-offs before switching, and note that statement-based replication has constraints with READ COMMITTED. The alternative is to replace check-then-insert with a plain INSERT that relies on the unique constraint and handles the duplicate-key error.
For Class 4, avoid racing plain INSERTs on the same key from many sessions. Use INSERT ... ON DUPLICATE KEY UPDATE or, in PostgreSQL, INSERT ... ON CONFLICT, and sort multi-row upsert batches by the conflict key so overlapping batches lock in the same order. Deduplicate keys inside a batch before sending it.
For Class 5, add the index. Run EXPLAIN on the locking statement, confirm it uses an index that matches the predicate, and re-run the repro. The MySQL manual’s advice to “add well-chosen indexes … so that your queries scan fewer index records and set fewer locks” is the highest-leverage deadlock fix in InnoDB, because it shrinks every lock footprint at once.
Shorten transactions and keep I/O out of them
The probability that two transactions overlap grows with how long each holds its locks. A transaction that calls an HTTP API, waits on a message broker, or renders a template between BEGIN and COMMIT holds its locks for tens or hundreds of milliseconds instead of a few. Move all external calls outside the transaction, read what you need before BEGIN, and write in one short burst.
A rough illustrative model helps. If two hot transactions each hold a lock for 5 ms and arrive at 200 per second, the chance a given one overlaps another is roughly 200 × 0.010 = 2%. Stretch the hold time to 50 ms with a network call inside and the overlap probability rises roughly tenfold. Not every overlap is a deadlock, but every database deadlock needs an overlap.
Connection pooling interacts here. In PgBouncer, PgCat or RDS Proxy transaction mode, a server connection is pinned for the life of a transaction, so long transactions both hold locks and starve the pool. Session-level advisory locks and SET statements also misbehave in transaction mode, which matters for the next fix.
Refuse to wait: NOWAIT and SKIP LOCKED
Some workloads should never wait at all. Job queues, outbox relays and work-stealing schedulers pick any available row; waiting for a specific locked row is pointless. Both engines support FOR UPDATE SKIP LOCKED, which “removes locked rows from the result set,” and NOWAIT, which fails immediately instead of blocking.
-- Queue consumer: works on PostgreSQL and MySQL 8.0+
START TRANSACTION; -- BEGIN on PostgreSQL
SELECT id, payload FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- process, then:
UPDATE jobs SET status = 'done' WHERE id IN (/* ids above */);
COMMIT;
Both manuals carry the same warning: skipping locked rows “provides an inconsistent view of the data” and is “not suitable for general purpose work.” Use it for queue-like tables only. NOWAIT raises SQLSTATE 55P03 (lock_not_available) in PostgreSQL and ERROR 3572 (HY000): Do not wait for lock in MySQL; treat that error as “busy, try something else,” not as a deadlock. MySQL also flags both clauses as unsafe for statement-based replication.
Serialise with advisory or named locks
When a logical resource spans many rows, for example “the monthly invoice run for tenant 42,” it is often simpler to serialise on the resource than to order every row lock. PostgreSQL’s pg_advisory_xact_lock(key) takes a transaction-scoped lock that is released automatically at commit or rollback; MySQL’s GET_LOCK(name, timeout) provides named locks scoped to the session.
Prefer transaction-scoped advisory locks in PostgreSQL. Session-level ones “do not honor transaction semantics” and survive a rollback, which leaks locks when a pooler in transaction mode hands the connection to another client. Advisory locks also participate in deadlock detection, so two transactions taking two advisory keys in opposite orders still deadlock; sort the keys. For locks that must span processes or survive database failover, see our analysis of distributed locks, leases and fencing tokens.
Step 6: Add a bounded retry with jitter
After the cycle is removed, residual deadlocks remain: new code paths, rare interleavings, DDL. The application must retry them, and the retry must re-run the entire transaction function, not just the failing statement. PostgreSQL’s documentation says it “does not offer an automatic retry facility, since it cannot do so with any guarantee of correctness.”

Figure 4: The database deadlock retry loop. Only retryable SQLSTATEs re-enter the loop; each retry sleeps a random interval bounded by an exponential cap, and the loop gives up after a fixed attempt count.
Figure 4 encodes four rules. Retry only 40P01 and 40001 in PostgreSQL, and 1213 in MySQL. Roll back before sleeping so locks are released while you wait. Sleep a random interval so the two former participants do not collide again in lockstep. Stop after a small number of attempts and surface the error, because unbounded retries convert a database deadlock into a load amplifier.
import random
import time
import logging
from typing import Callable, TypeVar
import psycopg
from psycopg import errors
T = TypeVar("T")
log = logging.getLogger("db.retry")
RETRYABLE = (errors.DeadlockDetected, # SQLSTATE 40P01
errors.SerializationFailure) # SQLSTATE 40001
def run_in_txn(conn: psycopg.Connection,
fn: Callable[[psycopg.Connection], T],
max_attempts: int = 4,
base_s: float = 0.02,
cap_s: float = 0.5) -> T:
"""Run fn inside a transaction, retrying deadlocks with full jitter.
fn must be safe to re-run: it re-reads everything it depends on and
performs no external side effects (HTTP calls, emails, publishes).
The connection should be opened with autocommit=True so that
conn.transaction() owns BEGIN/COMMIT/ROLLBACK.
"""
for attempt in range(1, max_attempts + 1):
try:
with conn.transaction(): # rolls back automatically on exception
return fn(conn)
except RETRYABLE as exc:
log.warning("retryable sqlstate=%s attempt=%d", exc.sqlstate, attempt)
if attempt == max_attempts:
raise
# "Full jitter": uniform in [0, min(cap, base * 2**attempt)]
time.sleep(random.uniform(0, min(cap_s, base_s * 2 ** attempt)))
raise RuntimeError("unreachable")
def transfer(conn: psycopg.Connection, src: int, dst: int, amount: int) -> None:
with conn.cursor() as cur:
# Lock in key order regardless of transfer direction (Class 1 fix)
cur.execute(
"SELECT acctnum FROM accounts WHERE acctnum = ANY(%s) "
"ORDER BY acctnum FOR NO KEY UPDATE",
([src, dst],),
)
cur.execute("UPDATE accounts SET balance = balance - %s WHERE acctnum = %s",
(amount, src))
cur.execute("UPDATE accounts SET balance = balance + %s WHERE acctnum = %s",
(amount, dst))
with psycopg.connect("dbname=app", autocommit=True) as conn:
run_in_txn(conn, lambda c: transfer(c, 22222, 11111, 100))
The class names come from psycopg 3’s SQLSTATE mapping: DeadlockDetected for 40P01, SerializationFailure for 40001, and LockNotAvailable for 55P03, all subclasses of OperationalError, with the code available as exc.sqlstate. Do not catch OperationalError broadly, because that also covers lost connections, where the right response is different and the transaction’s outcome may be unknown.
For MySQL with PyMySQL or mysqlclient, check the server error code instead of a class: exc.args[0] == 1213 for deadlocks. Be deliberate about 1205, the lock wait timeout. By default InnoDB rolls back only the timed-out statement, so the transaction is still open and still holds its earlier locks; your wrapper must issue an explicit ROLLBACK before retrying, or enable innodb_rollback_on_timeout so behaviour matches the deadlock case.
import pymysql
DEADLOCK, LOCK_WAIT_TIMEOUT = 1213, 1205
def is_retryable_mysql(exc: Exception) -> bool:
return isinstance(exc, pymysql.err.MySQLError) and exc.args \
and exc.args[0] in (DEADLOCK, LOCK_WAIT_TIMEOUT)
# In the loop: conn.rollback() on every retryable error, then sleep with jitter.
Choosing numbers is a judgement call, and the values in the code are starting points, not tuned constants. Four attempts with a 20 ms base and 500 ms cap bound the worst-case added delay to well under a second. The deeper reasoning about jitter, retry budgets and why retries at several layers multiply is covered in our guide to exponential backoff, jitter and retry budgets.
Make sure only one layer retries. If the repository function retries four times, the service client retries three times, and the API gateway retries twice, a single persistent database deadlock turns into 24 attempts. Put the deadlock retry closest to the transaction, where it can re-run the full unit of work, and make higher layers treat a surfaced deadlock as non-retryable.
Idempotency is the other half. A deadlock retry is safe only if the transaction function has no side effects outside the database. If a payment call or event publish sits inside it, a retry can duplicate it. Move side effects to after commit, or write them to a transactional outbox table in the same transaction so they are emitted exactly once regardless of retries.
Step 7: Monitor so regressions are visible
A fix is not done until you can see the database deadlock rate. In PostgreSQL, pg_stat_database.deadlocks is a cumulative counter of deadlocks detected per database; scrape it and alert on the rate, not the absolute value. Combine it with log_lock_waits lines, which tell you about long waits that did not become deadlocks yet and are often the leading indicator.
-- PostgreSQL: deadlock counter per database (cumulative since stats reset)
SELECT datname, deadlocks, xact_commit, xact_rollback
FROM pg_stat_database
WHERE datname = current_database();
-- MySQL 8.4: deadlock counter
SELECT name, count FROM information_schema.INNODB_METRICS
WHERE name = 'lock_deadlocks';
In MySQL, the lock_deadlocks metric in INFORMATION_SCHEMA.INNODB_METRICS counts deadlocks, and the error log holds full reports when innodb_print_all_deadlocks is on. Export both to your metrics stack. Most managed services, including Amazon RDS and Aurora, surface the error log in their console or log streams, so the reports are retrievable without shell access.
Express the alert as a ratio. A deadlock rate of 0.01% of transactions may be noise on a hot inventory table; a jump from 0.01% to 0.5% after a deploy is a regression with a specific commit behind it. Tag deadlock log lines with the deploy version so the correlation is one query away.
On application dashboards, chart retries per second, retry exhaustion count, and the p99 latency of transactions that retried. If retries rise while exhaustion stays flat, the safety net is working but the underlying cause is growing. If exhaustion rises, users are seeing errors and the fix is overdue.
Trade-offs, Gotchas, and What Goes Wrong
Retries hide design bugs until they amplify load. A retry loop turns a 0.1% deadlock rate into invisible latency. Under higher contention the rate climbs non-linearly, because each retry is another transaction competing for the same hot rows. Teams discover this during a sale or a batch backfill, when the retry storm saturates the pool. Track retries as a first-class metric and treat a sustained rise as a bug.
Disabling InnoDB deadlock detection trades CPU for latency. Turning off innodb_deadlock_detect can help hot-row workloads where detection itself serialises threads. But every real database deadlock then lasts innodb_lock_wait_timeout, 50 seconds by default, unless you lower it. If you disable detection, lower the timeout to a few seconds and confirm your retry wrapper handles 1205 by rolling back the whole transaction.
Lowering deadlock_timeout below one second is rarely worth it. It resolves PostgreSQL deadlocks faster but runs the check more often for ordinary lock waits, which are far more common than deadlocks. If deadlocks are frequent enough that one second matters, the fix is lock ordering, not a faster detector. Raising it on systems with many legitimate short waits is a more defensible tuning move.
DDL joins cycles you did not expect. An ALTER TABLE waiting for ACCESS EXCLUSIVE sits in the lock queue and blocks every later query on that table, including plain reads. A long-running transaction plus a migration plus normal traffic can produce both deadlocks and a pile-up. Set lock_timeout on migration sessions and follow the expand-contract migration pattern so schema changes take strong locks only briefly.
ORMs reorder writes. Some ORMs flush dirty objects in an order determined by an identity map, not by your code. Two requests that modify the same two entities can flush them in opposite orders. Either lock rows explicitly in key order at the start of the unit of work or configure the ORM’s flush ordering if it supports one.
SERIALIZABLE changes the error mix. In PostgreSQL, serializable snapshot isolation aborts with 40001 to prevent anomalies, often instead of letting a deadlock form. Your retry wrapper must handle both codes, and a spike in 40001 after raising the isolation level is expected behaviour, not a new bug.
Victim selection is not a contract. Do not write code that assumes the “smaller” or “younger” transaction will lose. PostgreSQL explicitly says not to rely on it, and InnoDB’s size heuristic is based on rows modified, which can change with data.
Practical Recommendations
Start with observability, because you cannot classify what you have not captured. Turn on log_lock_waits in PostgreSQL now rather than waiting for the PostgreSQL 19 default, and enable innodb_print_all_deadlocks during any investigation in MySQL. Scrape the database deadlock counters into your metrics system and alert on the rate relative to transactions.
When a database deadlock appears, reproduce it with scripted sessions before changing code, and keep that repro as a regression test. Classify it using the five shapes. Fix ordering problems with an explicit ordered locking read at the start of the transaction, fix InnoDB gap and upsert shapes with index, isolation or statement changes, and use SKIP LOCKED for queue tables. Only then make sure the bounded retry wrapper is in place.
Treat transactions as short, side-effect-free functions. Keep network calls out of them, cap batch sizes, and route external effects through an outbox. In regulated or payment flows, combine the retry wrapper with idempotency keys so a retried transaction can never double-apply.
Checklist:
- [ ]
log_lock_waits = on(PostgreSQL) orinnodb_print_all_deadlocks = ONwhile debugging (MySQL). - [ ] Deadlock counter scraped and alerted as a rate per committed transaction.
- [ ] Every multi-row write locks rows in primary-key order via one ordered locking read.
- [ ] The first lock on any row is the strongest mode the transaction needs.
- [ ] Locking statements use an index; verified with
EXPLAIN. - [ ] No network I/O between
BEGINandCOMMIT; batches capped at a few hundred rows. - [ ] Queue consumers use
FOR UPDATE SKIP LOCKED. - [ ] One retry layer, around the whole transaction, retrying only 40P01, 40001 or 1213, with full jitter and a small attempt cap.
- [ ] MySQL 1205 handled with an explicit rollback, or
innodb_rollback_on_timeoutenabled.
Frequently Asked Questions
What causes a database deadlock?
A database deadlock occurs when two or more transactions each hold a lock that another transaction in the group needs, forming a cycle in the wait-for graph. The most common cause is code paths that lock the same rows or tables in different orders. Others include upgrading a shared lock to an exclusive one, foreign key checks that take shared locks on parent rows, InnoDB gap locks under REPEATABLE READ, racing inserts on the same unique key, and statements that lock far more rows than intended because of a missing index.
How do I fix “deadlock detected” in PostgreSQL?
First enable log_lock_waits and read the server log’s DETAIL block to see which processes and statements formed the cycle. Then reproduce it with two sessions and identify which locks were taken in conflicting order. The durable fix is to acquire locks in a consistent order, typically by running SELECT ... ORDER BY id FOR NO KEY UPDATE on all target rows at the start of the transaction. Finally, wrap the whole transaction in a bounded retry for SQLSTATE 40P01 and 40001, with exponential backoff and jitter.
How do I see the last deadlock in MySQL InnoDB?
Run SHOW ENGINE INNODB STATUS\G and look for the LATEST DETECTED DEADLOCK section. It lists each transaction, the locks it holds, the lock it was waiting for, and which transaction InnoDB rolled back. That section only keeps the most recent deadlock, so set innodb_print_all_deadlocks = ON to write every deadlock to the MySQL error log during an investigation. The lock_deadlocks counter in INFORMATION_SCHEMA.INNODB_METRICS gives you a running count for monitoring and alerting.
Is it safe to automatically retry a deadlocked transaction?
Yes, provided you retry the entire transaction and the transaction has no side effects outside the database. Both PostgreSQL and MySQL recommend retrying deadlock victims, and the rolled-back transaction leaves no partial writes behind. Problems appear when the retry re-runs only the last statement, when an HTTP call or message publish inside the transaction gets duplicated, or when several layers retry and multiply the load. Use a small attempt cap, randomised backoff, and keep external effects behind a transactional outbox or idempotency key.
Should I disable innodb_deadlock_detect?
Usually not. Disabling it helps only on very high concurrency systems where many threads wait on the same hot row and the detection search itself becomes a bottleneck. Without detection, a real deadlock is resolved only when innodb_lock_wait_timeout expires, which defaults to 50 seconds. If you do disable it, lower the timeout to a few seconds, enable innodb_rollback_on_timeout or roll back explicitly on error 1205, and benchmark both settings on your own workload before deciding.
Does READ COMMITTED prevent deadlocks?
It reduces some deadlocks but does not prevent them. In InnoDB, READ COMMITTED disables gap locking for searches and index scans, which eliminates most gap and next-key lock deadlocks, but gap locks are still used for foreign key and duplicate-key checks. Ordinary row lock cycles caused by inconsistent ordering still occur at any isolation level in both engines. Lowering isolation also permits phantoms and other anomalies, so evaluate the correctness impact before switching.
Further Reading
- MVCC and transaction isolation levels: snapshot isolation, write skew and SSI explains the isolation semantics that decide which locks your transactions take.
- PgBouncer vs PgCat vs RDS Proxy: PostgreSQL connection pooling in transaction mode covers how poolers interact with long transactions and session-level locks.
- Retries, exponential backoff, jitter, circuit breakers and retry budgets goes deeper on choosing retry parameters and avoiding retry storms.
- PostgreSQL documentation: Explicit Locking for the full lock mode conflict tables and advisory lock semantics.
- MySQL 8.4 Reference Manual: Deadlocks in InnoDB for detection, examples and handling guidance.
By Riju — about
