Deadlock

What is a deadlock?

A deadlock is a standoff between two database transactions: each holds a lock the other needs, and neither will let go. Transaction A waits for a row that B has locked, while B waits for a row that A has locked. The wait runs in a circle, so it never clears on its own.

That is different from an ordinary lock wait, where one transaction waits for another to commit or roll back and then carries on. A deadlock is a closed loop: waiting longer changes nothing, so only the database can break it. It is a cycle in lock ordering, not slowness.

A two-transaction example

Two transactions touch the same tables, orders and inventory, but in the opposite order. Transaction A ships an order, then decrements stock:

UPDATE orders    SET status = 'shipped' WHERE id = 100;
UPDATE inventory SET qty = qty - 1      WHERE sku = 'A-42';

Transaction B corrects the same stock line first, then holds that order:

UPDATE inventory SET qty = qty + 5      WHERE sku = 'A-42';
UPDATE orders    SET status = 'held'    WHERE id = 100;

If each runs its first statement before its second, A holds the orders row and wants inventory, while B holds inventory and wants orders. Each blocks the other: that is the deadlock.

How the database resolves a deadlock

A deadlock will not clear itself, so a background detector watches for these cycles and ends one transaction to break the loop. PostgreSQL aborts one of the transactions so the other can finish, and notes that which one is hard to predict. SQL Server runs a lock monitor thread, picks a deadlock victim (by default the one cheapest to roll back), and returns error 1205 telling you to rerun. MySQL and its InnoDB engine roll back the victim and let the other proceed.

In each case the database ends one transaction and rolls back all of its work. It does not redo that work for you. Your application has to catch the error and retry, and the retry has to be safe to repeat, which is where idempotence earns its place.

How to prevent deadlocks

You cannot make deadlocks impossible, but one rule removes most of them.

1. Take locks in a consistent order. Every transaction that touches orders and inventory should lock them in the same sequence. In the example above, if both had updated orders before inventory, one would just wait for the other, a plain lock wait instead of a cycle. This is the rule that matters most.

2. Keep transactions short. Read, write, commit, and get out. Never hold a transaction open across user input, a network call, or a long calculation.

3. Index the columns you filter on. An UPDATE without a supporting index can lock far more rows than you meant to touch, which widens the window for a collision.

Raising the transaction isolation level does not help here. Isolation changes what your reads are allowed to see; deadlocks come from the order of writes, so a stricter level does not prevent them. They are a property of how ACID transactions take their locks, not of how much isolation you ask for.