You are getting a handful of deadlock errors an hour under load. How do you find the cause, and how do you stop them?

What the error is telling you

A deadlock is not contention and it is not slowness. It is a cycle: transaction 1 holds a lock on row A and is waiting for row B, transaction 2 holds row B and is waiting for row A. Neither can proceed and neither will time out, because each is waiting on something the other will hold until it commits. The engine's only recourse is to abort one of them, and the error you saw in the application log is that abort.

That distinction is the first thing to establish, because a lock-wait timeout is a different diagnosis with a different fix. A timeout means somebody held a lock too long — a transaction left open across a user interaction, a batch update in one statement, a COMMIT waiting on a slow network. A deadlock means two transactions acquired locks in incompatible orders, and it can happen in microseconds under no particular load. Confusing them sends you off tuning transaction duration when the problem is ordering, or vice versa.

The engines differ in how they get there, and it is worth being specific. PostgreSQL does not check for cycles eagerly: a waiting transaction sleeps for deadlock_timeout, one second by default, and only then walks the wait-for graph. So a genuine deadlock in PostgreSQL costs at least a second of latency before anyone is aborted, and the victim receives SQLSTATE 40P01. InnoDB maintains the wait-for graph and detects cycles immediately, aborting the transaction that has modified the fewest rows — cheapest to undo — with error 1213. InnoDB also lets you switch detection off with innodb_deadlock_detect, in which case cycles resolve as innodb_lock_wait_timeout expiries instead, which is occasionally the right trade on very high-concurrency workloads where the graph walk itself is a bottleneck.

Get the report, not the application error

The application only ever sees its own side of the cycle, which is why guessing from the stack trace fails. Both engines log the whole thing.

In PostgreSQL the deadlock is logged with a DETAIL block naming each process, the lock it waited for, the lock it held, and the full text of both statements. Turn on log_lock_waits as well, so long waits that did not become deadlocks are also visible — those are the near misses that tell you where the ordering is fragile. In MySQL, SHOW ENGINE INNODB STATUS reports the most recent deadlock only, so set innodb_print_all_deadlocks to write every one to the error log; otherwise the one you want is always the one that got overwritten.

Read two things out of the report. Which index the locks were taken on, because that tells you the order rows were visited in, and which statements were involved, because two different statements in the cycle means two code paths and one statement means a single query is deadlocking against itself.

The cause is almost always inconsistent ordering

Two transfers running concurrently in opposite directions is the textbook case, and the textbook case is also the real one:

-- Session A                                  Session B
BEGIN;                                        BEGIN;
UPDATE account SET balance = balance - 100    UPDATE account SET balance = balance + 100
  WHERE id = 1;   -- locks row 1                WHERE id = 2;   -- locks row 2
UPDATE account SET balance = balance + 100    UPDATE account SET balance = balance - 100
  WHERE id = 2;   -- waits for B                WHERE id = 1;   -- waits for A: cycle

The fix is to make every transaction acquire the accounts in one deterministic order, usually ascending primary key, regardless of which is the debit and which the credit. Ordering is a global property, so it has to be a convention the whole codebase follows; one function that locks in the order the business describes rather than the order the convention requires is enough to reintroduce the cycle.

The same shape appears less obviously in several places worth knowing about.

A single multi-row statement acquires locks in the order the plan visits rows, which follows the index it chose. Two concurrent UPDATE ... WHERE status = 'PENDING' statements can therefore deadlock against each other if the plans differ, or if one of them is driven by an IN list in an order the caller supplied. Sorting the list before sending it costs nothing and removes the class of bug. In PostgreSQL you can also make the order explicit by taking the locks first with SELECT ... FROM ... ORDER BY id FOR UPDATE, then updating.

Foreign keys lock rows you did not mention. Inserting a child row takes a shared lock on the referenced parent so it cannot vanish underneath you, so two transactions inserting children of two parents and then touching the parents in the opposite order deadlock without either statement naming a parent. This is the commonest "but my query doesn't lock anything" case.

Under InnoDB's REPEATABLE READ, next-key locking means an INSERT or a locking read takes a gap lock covering the space before a row, and two transactions inserting into the same gap can deadlock even though the rows they insert are distinct. Dropping that transaction to READ COMMITTED removes gap locks and often removes the deadlock outright, which is a legitimate fix once you have satisfied yourself the workload does not need phantom protection. PostgreSQL has no gap locks, so this whole family does not arise there — a genuine behavioural difference to name if the interviewer has not said which engine you are on.

Finally, lock upgrades. Two transactions that each take a shared lock on a row and then try to write it will both block waiting for the other to release, and that is a cycle of two. If a read is a prelude to a write, take the write-strength lock up front with FOR UPDATE rather than FOR SHARE.

Reduce the window, and bound the wait

Structural fixes shrink the opportunity even where ordering is already consistent. Keep transactions short and never span a network call or a user interaction, since a transaction open for 400ms has forty times the collision window of one open for 10ms. Update in one statement rather than a read-modify-write round trip where the semantics allow. And touch fewer rows: a batch job updating a million rows in one transaction will collide with everything, while the same work in committed chunks of a few thousand mostly will not.

For workers competing over a queue table, FOR UPDATE SKIP LOCKED eliminates the contention rather than ordering it. Each worker takes the first rows nobody else holds and never waits, so there is nothing to form a cycle with. It is available in PostgreSQL 9.5 and later and in MySQL 8.0 and later.

Bound the waiting too. PostgreSQL's lock_timeout caps how long a statement waits for a lock without capping its execution time, which is what you want on an interactive path. statement_timeout caps total execution and will also cut a genuinely slow query. deadlock_timeout is neither of those: it is only how long to wait before checking for a cycle, and lowering it to react faster costs a graph walk on every ordinary lock wait.

Retry is part of the design, not an admission of defeat

A deadlock abort is safe to retry by definition: the victim's transaction rolled back entirely, so no partial effect survives, and the other transaction has now committed and released its locks so the retry will very likely succeed. Detect the specific SQLSTATE — 40P01 in PostgreSQL, and 40001 for the serialisation failures you will also get if any transaction runs at SERIALIZABLE — and retry the whole transaction with a short randomised backoff and a small attempt cap.

Two details make the difference between a retry that helps and one that hides a bug. The retry has to re-run the transaction from the beginning, including the reads, because the data it read is gone with the rollback; replaying only the failed statement inside a new transaction reintroduces the anomaly you were protected from. And the retry must be counted and alerted on, not swallowed. A steady few per hour is the workload's normal noise floor; the same code with no metric is how a rate that has quietly grown tenfold gets discovered by a customer.

Read the engine's deadlock report before proposing anything, because it names both statements and both lock modes, and in most cases the fix falls straight out of it: the two paths visit the same rows in different orders, and one of them has to change.