Analyzing Deadlocks from Logs: Reconstructing Root Causes Precisely

1. Why deadlocks are never really random

A deadlock occurs when two or more transactions wait on each other for locks that the respective other transaction holds, so that none of the involved transactions can ever proceed. The database detects this state and forcibly aborts one of the transactions to resolve the standstill. From the application's point of view this often looks like a rare, barely reproducible fluke, but in reality almost every deadlock is caused by a deterministic, repeatable lock order in the code.

Exactly this determinism is what makes it worthwhile to analyze deadlocks from logs, instead of accepting them as unavoidable background noise. A deadlock log entry contains the full SQL statements of both involved transactions, the exact locks held and requested, and the order in which they were acquired. Reading this information correctly almost always reveals a concrete spot in the code where two operations lock resources in a different order.

The most common mistake in dealing with deadlocks is handling them exclusively with retry logic in application code, without investigating the underlying cause. Retry logic is a sensible safety net, but it does not replace the actual analysis. Anyone who analyzes deadlocks from logs finds the real root and can fix it permanently, instead of living with an ever growing number of deadlocks per minute as load increases.

2. Enabling and reading InnoDB deadlock output

MySQL and MariaDB with InnoDB log the most recently occurred deadlock by default in the internal status buffer, retrievable via SHOW ENGINE INNODB STATUS in the LATEST DETECTED DEADLOCK section. The problem: only the latest deadlock is kept, an earlier incident is overwritten as soon as a new one occurs. For systematic analysis, innodb_print_all_deadlocks is therefore indispensable, a setting that additionally writes every deadlock to the general error log.

Once this option is enabled, all deadlocks end up permanently in the error log and can be evaluated with standard log analysis tools, instead of manually querying SHOW ENGINE INNODB STATUS at exactly the right moment for every incident. This single configuration change is the most important first step to analyze deadlocks from logs systematically, instead of waiting for the next random occurrence.

-- Write all deadlocks permanently to the error log (MySQL 5.6.15+)
SET GLOBAL innodb_print_all_deadlocks = ON;

-- Retrieve the latest deadlock manually (only the newest is available)
SHOW ENGINE INNODB STATUS\G

-- Excerpt of a typical deadlock entry in the error log:
-- 2026-07-29T10:14:22 0x7f2a3c001700 InnoDB: transactions deadlock detected, dumping detailed information.
-- *** (1) TRANSACTION:
-- TRANSACTION 421839201, ACTIVE 2 sec starting index read
-- mysql tables in use 1, locked 1
-- LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
-- MySQL thread id 812, OS thread handle, query id 91234 checkout update
-- UPDATE orders SET status = 'paid' WHERE id = 4821
-- *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
-- RECORD LOCKS space id 45 page no 12 n bits 80 index PRIMARY of table `shop`.`orders`
-- *** (2) TRANSACTION:
-- TRANSACTION 421839205, ACTIVE 1 sec starting index read
-- UPDATE inventory SET stock = stock - 1 WHERE order_id = 4821
-- *** (2) HOLDS THE LOCK(S):
-- RECORD LOCKS space id 45 page no 12 n bits 80 index PRIMARY of table `shop`.`orders`
-- *** WE ROLL BACK TRANSACTION (1)

3. Decoding the structure of an InnoDB deadlock entry

The InnoDB deadlock entry follows a fixed pattern with two numbered transaction blocks, (1) and (2). Each block first shows the transaction's active query, then either WAITING FOR THIS LOCK TO BE GRANTED (the lock being waited for) or HOLDS THE LOCK(S) (the lock already held). The decisive line at the end, WE ROLL BACK TRANSACTION, shows which of the two transactions the database forcibly terminated.

To analyze deadlocks from logs, one must compare the two blocks side by side: transaction (1) holds lock A and waits for lock B. Transaction (2) holds lock B and waits for lock A. Exactly this crossed pattern is the definition of a deadlock. In the example above, the first transaction locks the order row first and then waits for the inventory, while the second transaction uses the reverse order, a classic inconsistent lock order between two tables.

4. Reading the PostgreSQL deadlock log

PostgreSQL automatically logs deadlocks in the server log, without a separate setting like in MySQL being necessary, as long as normal logging is active. A PostgreSQL deadlock entry begins with ERROR: deadlock detected, followed by a DETAIL section showing the process IDs of both involved sessions, the respective requested locks, and the CONTEXT with the full SQL statement.

The big advantage over InnoDB: PostgreSQL automatically logs every deadlock permanently without additional configuration, since there is no overwrite problem like with SHOW ENGINE INNODB STATUS. Anyone who analyzes deadlocks from logs therefore tends to find a more complete history in PostgreSQL without any prior setup steps.

-- Typical PostgreSQL deadlock entry in the server log:
-- ERROR:  deadlock detected
-- DETAIL:  Process 18234 waits for ShareLock on transaction 891234;
--          blocked by process 18240.
--          Process 18240 waits for ShareLock on transaction 891230;
--          blocked by process 18234.
-- HINT:  See server log for query details.
-- CONTEXT:  while updating tuple (0,5) in relation "orders"
-- STATEMENT:  UPDATE orders SET status = 'paid' WHERE id = 4821;

-- Additionally enable log_lock_waits to see the lead time before the deadlock
-- postgresql.conf:
log_lock_waits = on
deadlock_timeout = '1s'
log_line_prefix = '%m [%p] '

5. Reconstructing the underlying lock order

The decisive analysis step is to reconstruct, from the two query texts in the log, the order in which each transaction locks its resources. Usually a pattern emerges: one code path locks table A before table B, another seemingly unrelated code path locks the same tables in the reverse order. As long as both paths are tested in isolation, they work fine, only under concurrent execution does the deadlock appear.

In practice this root cause is often found in two separate functions or services that were developed independently and both touch the same two tables, but nobody ever coordinated the lock order between them. Anyone who analyzes deadlocks from logs and consistently compares the query order of both transaction blocks usually finds this inconsistency within a few minutes, even without consulting the original code author.

6. Why the database aborts exactly this transaction

The choice of victim in a deadlock, recognizable in the log as WE ROLL BACK TRANSACTION or via the process ID in the PostgreSQL log, does not follow an arbitrary rule but an internal heuristic. InnoDB typically chooses the transaction with the smaller estimated rollback effort, measured by the number of changed rows, as the victim. PostgreSQL tends to choose the younger of the two transactions.

This information is relevant for the analysis because it explains why, with repeated deadlocks involving the same two code paths, sometimes transaction A and sometimes transaction B gets aborted, even though the lock order is the same. Anyone only looking at the error message in application code could mistakenly assume these are two different problems, even though the underlying cause is identical.

7. Capturing deadlocks permanently instead of reading once

A single log entry shows a single incident, but not the frequency or trend over time. For systematic analysis it is advisable to automatically extract all deadlock entries from the error log or server log into a structured table, for example with a regularly running parser script. This makes it possible to answer whether the deadlock rate increased after a deployment, which two tables are most frequently involved, and whether certain times of day are particularly affected.

MySQL additionally offers performance_schema.events_statements_history and an explicit deadlock counter via SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks', which can be used to build a simple monitoring dashboard without external tools. Anyone who analyzes deadlocks from logs and exports this counter into a standard monitoring system like Prometheus notices a rising trend before it becomes an acute production problem.

-- MySQL: retrieve cumulative deadlock counter since server start
SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';

-- PostgreSQL: deadlock counter per database
SELECT datname, deadlocks
FROM pg_stat_database
WHERE datname = current_database();

8. From log analysis to a concrete code change

Once the lock order has been reconstructed from the log, there are three proven fixes. First: bring both code paths to a unified, consistent lock order, for example always locking sorted ascending by primary key first. Second: combine affected updates into a single query where possible, to reduce the number of separate lock operations. Third: request explicit locks with SELECT ... FOR UPDATE at the start of the transaction instead of implicitly through later UPDATE statements, to make the order visible and controllable in the code.

A frequently overlooked but effective fourth approach: add retry logic with exponential backoff as a safety net, after the actual root cause has been fixed. Deadlocks can rarely be excluded a hundred percent in complex systems, but after a clean analysis from the logs their frequency typically drops by orders of magnitude, so retry logic only catches rare remaining cases instead of serving as the main strategy.

9. InnoDB and PostgreSQL deadlock logs compared

Both systems provide the necessary information to analyze deadlocks from logs, but differ significantly in format and accessibility.

Regardless of the system: the log structure is stable enough to build automated evaluation, instead of reading every deadlock manually. Anyone who sets up this automation once can continuously analyze deadlocks from logs, instead of only reacting to complaints.

10. Summary

Analyzing deadlocks from logs turns a seemingly random incident into a precisely reconstructable lock order in application code. InnoDB deadlock output via SHOW ENGINE INNODB STATUS and innodb_print_all_deadlocks, as well as the automatic PostgreSQL deadlock log, both provide all the necessary information: the involved queries, the locks held and requested, and which transaction was aborted. The key is to systematically compare both transaction blocks, instead of only reading the error message in application code.

A deadlock problem is only solved sustainably once the underlying lock order in the code is unified, not through retry logic alone. Anyone who additionally integrates a deadlock counter permanently into monitoring notices a rising trend early and can react before deadlocks become a frequent production problem.

11. FAQ: Analyzing Deadlocks from Logs