About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.
An application that receives ERROR: deadlock detected has had one of its transactions cancelled by PostgreSQL. Two or more transactions each held a lock the other needed, so none could continue, and the server aborted one so the rest could finish. The error carries SQLSTATE 40P01. The cure is not a server setting. Make every transaction take its locks in the same order, keep transactions short, and have the application retry the aborted transaction.
What the error means
The PostgreSQL manual defines a deadlock as a situation "wherein two (or more) transactions each hold locks that the other wants". It says PostgreSQL "automatically detects deadlock situations and resolves them by aborting one of the transactions involved, allowing the other(s) to complete" and that "Exactly which transaction will be aborted is difficult to predict and should not be relied upon."
Deadlocks are not limited to explicit LOCK TABLE statements. The manual says "deadlocks can also occur as the result of row-level locks (and thus, they can occur even if explicit locking is not used)". Two sessions that update the same two rows in opposite orders are enough.
PostgreSQL does not look for a deadlock the moment a session starts to wait. The deadlock_timeout setting is "the amount of time to wait on a lock before checking to see if there is a deadlock condition". The default is 1s (one second). Until that check runs, a waiting session simply waits. A wait that never forms a cycle is not a deadlock and produces no error, however long it lasts. The manual warns that "a transaction seeking either a table-level or row-level lock will wait indefinitely for conflicting locks to be released" when no deadlock is detected.
The error text comes from PostgreSQL's source code (file src/backend/storage/lmgr/deadlock.c, release REL_17_12), because the manual does not print it. The client receives the message deadlock detected, one detail line for each process in the cycle, and the hint See server log for query details. Each detail line is built from this format, as the source has it: Process %d waits for %s on %s; blocked by process %d. The varying parts are, in order, the waiting process ID, the lock mode name, a description of the object waited for, and the process ID of the blocker. The server log repeats those lines and adds the text of each process's query. The PostgreSQL manual's list of error codes names SQLSTATE 40P01 as deadlock_detected, in class 40, Transaction Rollback.
Likely causes
| Cause | What you see | How to confirm |
|---|---|---|
| Two transactions update the same rows in opposite order | Two processes each waiting on the other's transaction; the two queries touch the same tables with different row order | The log detail names both processes and their queries; compare the order of rows or tables in each |
| Batch job and web request lock overlapping row sets | Errors cluster when a batch job runs | pg_stat_activity shows the batch's xact_start is old and overlaps the error times |
| Transactions held open for long periods | Locks are held far longer than the statements need | pg_stat_activity rows in state idle in transaction with an early xact_start |
| A lock taken in a weaker mode, then a stronger one on the same object | Deadlocks between sessions that lock a row to read, then update it | The log queries show a locking read and then an update of the same rows in both sessions |
Table-level LOCK or DDL inside a transaction that also touches rows |
Deadlocks that involve table-level locks as well as row locks | The two queries in the log include LOCK TABLE or schema-changing statements |
Prove the cause before changing anything
All of the following are read-only.
1. Read the server log around the error time. The log entry for the deadlock lists each process in the cycle, what it waits for and whom it is blocked by, then the query text for each process. The manual's list of log escapes includes %p (process ID) and %x (transaction ID), which help you match a log line to a session when your log_line_prefix includes them. The manual does not show the layout of these log lines, so read them in the form your server prints them.
Good result: two process IDs, each blocked by the other, and two query texts you can compare. Bad result: only one process and no cycle. A single long wait is a blocking problem, not a deadlock, and PostgreSQL would not report deadlock detected for it.
2. Count deadlocks per database. The deadlocks column of pg_stat_database holds the "Number of deadlocks detected in this database".
SELECT datname, deadlocks FROM pg_stat_database ORDER BY deadlocks DESC;
Run it twice a few hours apart. A rising number confirms deadlocks are recurring, and the database with the rising number is the one to study.
3. Look for long transactions that hold locks. pg_stat_activity reports xact_start ("Time when this process' current transaction was started"), state and query.
SELECT pid, state, xact_start, wait_event_type, wait_event, left(query, 80) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;
A session in state idle in transaction is, in the manual's words, "in a transaction, but is not currently executing a query", and any row locks it took stay held. The manual says row-level locks "are released at transaction end or during savepoint rollback".
4. See who blocks whom right now. pg_blocking_pids(pid) returns "an array of the process ID(s) of the sessions that are blocking the server process with the specified process ID from acquiring a lock".
SELECT pid, pg_blocking_pids(pid) AS blocked_by, left(query, 80) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
The manual says frequent calls "could have some impact on database performance", so do not run this in a tight loop.
5. Check whether lock waits are being logged.
SHOW log_lock_waits;
SHOW deadlock_timeout;
log_lock_waits defaults to off. When on, it logs "a log message ... when a session waits longer than deadlock_timeout to acquire a lock". Without it, a slow lock wait that does not end in a deadlock leaves no trace in the log.
Fix
Make every transaction lock in the same order
The manual says the main defence is to avoid deadlocks "by being certain that all applications using a database acquire locks on multiple objects in a consistent order." It adds that "if both transactions had updated the rows in the same order, no deadlock would have occurred."
In practice, sort the keys in the application before updating, so two requests that touch rows 7 and 12 both lock 7 first. For a transaction that locks rows before updating them, SELECT ... FOR UPDATE with an ORDER BY on the key makes the lock order explicit. The manual's SELECT page cautions that at the READ COMMITTED isolation level, a query that uses ORDER BY and a locking clause can return rows out of order if the ordering values change while it waits, because the command sorts first and "might then block trying to obtain a lock".
The manual also advises that "the first lock acquired on an object in a transaction is the most restrictive mode that will be needed for that object". A transaction that will update a row should lock it for update at the start, not read it first and upgrade later.
Keep transactions short
The manual says "it is a bad idea for applications to hold transactions open for long periods of time (e.g., while waiting for user input)". Commit as soon as the related statements are done, and do not hold a transaction open across a network call or a prompt.
Retry the aborted transaction
The manual says "If it is not feasible to verify this in advance, then deadlocks can be handled on-the-fly by retrying transactions that abort due to deadlocks." Make the application catch SQLSTATE 40P01 (or 40001, serialization_failure, in the same class), roll back, and run the whole transaction again from its first statement, with a small cap on attempts. Retry the whole transaction, not the single statement that failed.
Log lock waits while you investigate
If deadlocks are rare and the log detail is thin, turn on lock-wait logging. First print and keep the current values:
SHOW log_lock_waits;
SHOW deadlock_timeout;
Keep that output. If log_lock_waits already prints on, stop here: there is nothing to change and nothing to undo.
Risk: the next two statements change a server-wide setting and reload the configuration for every session on the server. ALTER SYSTEM needs a superuser or a role granted ALTER SYSTEM on the parameter, and the manual says the server signalling functions, which include pg_reload_conf(), are "restricted to superusers by default but access may be granted to others using GRANT". The log will grow by one entry for each lock wait longer than deadlock_timeout, which on a busy server can be many entries.
ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();
The manual says values set with ALTER SYSTEM are effective after the next server configuration reload and that pg_reload_conf() causes the server to reload its configuration files. Run SHOW log_lock_waits; and confirm it now reads on.
To undo it: the saved output showed off, so remove the entry that ALTER SYSTEM wrote, reload, and check the value against the output you saved. The server does the editing, so the file is never replaced while the server runs:
ALTER SYSTEM RESET log_lock_waits;
SELECT pg_reload_conf();
SHOW log_lock_waits;
The manual says the RESET form "removes that configuration entry from the postgresql.auto.conf file". SHOW log_lock_waits; should print the value you saved. If it prints a different value, an earlier entry or postgresql.conf sets it; set the saved value explicitly with ALTER SYSTEM SET log_lock_waits = <saved value>;, reload again and check once more.
The manual notes that deadlock_timeout also sets the delay before a lock-wait message is logged, and that "If you are trying to investigate locking delays you might want to set a shorter than normal deadlock_timeout." Lowering it makes the server run the costly deadlock check more often, so it is not a fix for deadlocks themselves.
Example (illustrative): On db01, an order system updates stock rows. A web request on process 4111 updates stock item 7 and then item 12. A nightly batch on process 4125 updates item 12 and then item 7. The two collide: 4111 holds item 7 and waits for item 12, while 4125 holds item 12 and waits for item 7. After 1 s (the default deadlock_timeout of 1000 ms) PostgreSQL checks, finds the cycle, and aborts one of the two, here 4125, with ERROR: deadlock detected. The log lists both processes and both queries. The fix is a one-line change in the batch job: sort the item numbers ascending before updating, so both processes lock 7 first, then 12. The batch also gains a retry on SQLSTATE 40P01, limited to three attempts.
Confirm the fix
deadlocksinpg_stat_databasestops rising for the database over several days, including the hours when the batch job runs.- The server log no longer holds
deadlock detectedentries. - If you enabled
log_lock_waits, waits longer thandeadlock_timeoutare rare and none involve the same pair of queries. - The application log shows retries on
40P01falling to zero or near zero after the order change.
What to watch afterwards
- A retry that succeeds on every error hides a deadlock problem that still costs time. Count retries and treat a steady rate as a defect.
- New code paths that touch the same tables in a different order bring the problem back. Keep the lock order in one shared function rather than repeating update sequences.
- Watch
idle in transactionsessions. They hold locks without running anything and lengthen every wait. - Switch
log_lock_waitsback off if you only enabled it for the investigation and the log volume matters.
Read next
Related articles you may want next.
- PerformancePostgreSQL Tables Keep Growing: Diagnose Autovacuum and Transaction AgeDistinguish reusable table space, retained row versions and transaction-ID risk before selecting maintenance.Read article
- TroubleshootingSQL Deadlock Error 1205: Read the Graph and Fix the Access PatternUse a deadlock graph to identify competing resources and select a transaction or query change with a bounded retry contract.Read article
- TroubleshootingPostgreSQL "sorry, too many clients already": count connections before raising max_connectionsPostgreSQL 'sorry, too many clients already'? Count connections by state, fix idle-in-transaction leaks, then decide on max_connections.Read article
Risks and limits
- These commands have not been run or lab-tested.
- Which transaction is aborted cannot be predicted, so an application must be able to retry any transaction, not only a "loser" you expect.
- The manual does not show the layout of the log lines; the line formats quoted here come from the server source code at release REL_17_12, and wording may differ in other releases.
ALTER SYSTEMcannot run inside a transaction block or function, and is not allowed for a role without the privilege.- A long wait with no cycle is not a deadlock and does not raise this error; use the blocking-pid query in that case. SQL Server's equivalent error is covered in "SQL Deadlock Error 1205: Read the Graph and Fix the Access Pattern".
Sources
- PostgreSQL 17 documentation: 13.3. Explicit Locking (Deadlocks, Row-Level Locks) — PostgreSQL Global Development Group, no date shown (page shows version 17.12), accessed 22 November 2026
- PostgreSQL 17 documentation: 19.12. Lock Management (deadlock_timeout) — PostgreSQL Global Development Group, no date shown, accessed 22 November 2026
- PostgreSQL 17 documentation: 19.8. Error Reporting and Logging (log_lock_waits, log_line_prefix) — PostgreSQL Global Development Group, no date shown, accessed 22 November 2026
- PostgreSQL 17 documentation: Appendix A. PostgreSQL Error Codes — PostgreSQL Global Development Group, no date shown, accessed 22 November 2026
- PostgreSQL 17 documentation: 27.2. The Cumulative Statistics System (pg_stat_activity, pg_stat_database) — PostgreSQL Global Development Group, no date shown, accessed 22 November 2026
- PostgreSQL 17 documentation: 9.27. System Information Functions (pg_blocking_pids) — PostgreSQL Global Development Group, no date shown, accessed 22 November 2026
- PostgreSQL 17 documentation: SELECT (locking clause) — PostgreSQL Global Development Group, no date shown, accessed 22 November 2026
- PostgreSQL 17 documentation: ALTER SYSTEM — PostgreSQL Global Development Group, no date shown, accessed 22 November 2026
- PostgreSQL 17 documentation: 9.28. System Administration Functions (pg_reload_conf) — PostgreSQL Global Development Group, no date shown, accessed 22 November 2026
- PostgreSQL source code, release REL_17_12: src/backend/storage/lmgr/deadlock.c — PostgreSQL Global Development Group, no date shown, accessed 22 November 2026
This article was originally published on .