A deadlock is a cycle, not a slow query
In a deadlock two transactions each hold a lock the other needs, so neither can move. The database notices and rolls one back, and the application sees an error. PostgreSQL states that which transaction is aborted is difficult to predict and should not be relied on. InnoDB likewise picks a victim. The error therefore looks random, which is why teams reach for a retry before understanding it.
The classic cause is two code paths updating the same rows in opposite orders: for example one updates account A and then account B, the other B and then A. The PostgreSQL manual gives a similar example with two accounts updated in opposite orders, and says the best defence is for every application to take locks on multiple objects in a consistent order. MySQL's manual adds that transactions touching several tables or large row ranges should use the same order of operations, and advises creating indexes on the columns used by locking statements.
- Write down, for each transaction, the statements in order and the rows or ranges each locks.
- Look for two paths that touch the same objects in different orders.
Get the database's own record
Do not reconstruct a deadlock from application logs alone. PostgreSQL writes the cycle to its server log when it aborts a transaction. In MySQL, SHOW ENGINE INNODB STATUS shows the most recent deadlock, and setting innodb_print_all_deadlocks writes every one to the error log, which the manual recommends when they are frequent. Remove literal values from any excerpt before sharing it.
For waits that never become deadlocks, ask who is blocking whom. PostgreSQL has pg_blocking_pids, which returns the sessions blocking a given session. MySQL has the sys.innodb_lock_waits view, which lists the waiting and blocking queries; the blocking query is empty when the blocker has gone idle, which itself is a clue: a transaction left open.
- Capture one full occurrence, not a summary.
- On PostgreSQL, enabling log_lock_waits records waits longer than deadlock_timeout, which defaults to one second.
Timeouts: stop one stuck transaction taking everything down
Several PostgreSQL settings bound how long things may wait, and all are disabled by default. statement_timeout aborts a long statement. lock_timeout aborts a statement that waits too long for a lock. idle_in_transaction_session_timeout ends a session that sits idle inside an open transaction, and the manual points out such sessions hold locks and keep vacuum from cleaning up recently dead rows. The manual recommends against setting statement_timeout or lock_timeout globally in the configuration file because they would hit every session; set them per role or per session instead. PostgreSQL 17 and later also has transaction_timeout, which bounds a whole transaction.
MySQL's manual says that if InnoDB deadlock detection is switched off, InnoDB relies on innodb_lock_wait_timeout to roll back transactions, so check the value you actually run with. A timeout is a safety net for the rest of the application, not a cure. Choose values from the longest legitimate transaction you have, and log when they fire.
- Apply timeouts per role for the application user, not for administrators.
- Never hold a transaction open while waiting for a person or a slow outside call.
Retries are the last step, and how the paid job is accepted
Both manuals say a transaction rolled back by a deadlock can be retried, and code should be ready to do so. But a retry belongs after the cause is removed, bounded in count and logged. An unlimited retry loop adds load exactly when the database is struggling, and hides a cycle that will return.
The deadlock outcome is accepted by reproducing the failure on a staging copy first and measuring how often the original code fails in the same concurrent test, then showing the changed code clean with retries disabled over at least 200 runs and at least ten times the runs per failure seen on the original code, with the cycle drawn from your own record. It does not cover a single abandoned session, a data-model redesign or changes to your production settings.
- Measure throughput before and after, so the fix does not simply serialise the work.
- Keep the test script; it is the regression test for the next change.
Sources and limits
- PostgreSQL 18: Explicit locking, deadlocks Checked 2026-10-11.
- PostgreSQL detects deadlocks and aborts one transaction; which one is hard to predict, consistent lock order is the best defence, retrying is the fallback, and long-held transactions are a bad idea.
- MySQL 8.4: Deadlocks in InnoDB Checked 2026-10-11.
- InnoDB detects a deadlock and rolls back a victim; SHOW ENGINE INNODB STATUS shows the latest deadlock and innodb_print_all_deadlocks logs every one; keep transactions small, use a consistent order, index the columns in locking statements and be ready to retry.
- PostgreSQL 18: Client connection defaults, timeouts Checked 2026-10-11.
- statement_timeout, lock_timeout and idle_in_transaction_session_timeout are disabled (0) by default, as is transaction_timeout, which exists from PostgreSQL 17; idle sessions in a transaction hold locks and can hold back vacuum cleanup.
- PostgreSQL 18: Lock management settings Checked 2026-10-11.
- deadlock_timeout defaults to one second and is the wait before a deadlock check; with log_lock_waits enabled it also sets when a lock-wait message is logged.
- PostgreSQL 18: pg_blocking_pids Checked 2026-10-11.
- pg_blocking_pids returns the process IDs of the sessions blocking a given session from getting a lock.
- MySQL 8.4: sys.innodb_lock_waits Checked 2026-10-11.
- The view shows waiting and blocking transactions with their queries and processlist IDs; the blocking query is empty if the blocker is idle.
- PostgreSQL 17 release notes: server configuration Checked 2026-10-11.
- PostgreSQL 17 added the server variable transaction_timeout to restrict the duration of transactions.