Deadlock

A lock is a way to claim exclusive access to a row, table, or other resource so two transactions do not corrupt each other's work. A deadlock happens when transactions grab those locks in a different order and then wait on each other in a loop.

The classic example uses two rows, A and B. Transaction 1 locks row A and then asks for row B. At the same time, Transaction 2 locks row B and then asks for row A. Transaction 1 cannot get B because Transaction 2 holds it, and Transaction 2 cannot get A because Transaction 1 holds it. Neither will give up its lock until it finishes, and neither can finish. That is a deadlock.

A deadlock is not the same as slowness or a long wait. In a normal wait, one transaction will eventually get the lock once the holder commits. In a deadlock, the wait is circular, so it would last forever if nothing intervened. There are four conditions that must all hold for a deadlock: mutual exclusion, hold and wait, no preemption, and a circular wait. Remove any one of them and the deadlock cannot form.

Most databases keep a wait-for graph, a small in-memory structure where each node is a transaction and each edge points from a transaction that is waiting to the transaction that holds the lock it wants. When that graph contains a cycle, a deadlock exists.

A background process scans this graph on an interval. In PostgreSQL the check runs after a transaction has been blocked for deadlock_timeout, which defaults to one second. MySQL with InnoDB keeps the wait-for graph updated continuously and detects cycles almost immediately. When a cycle is found, the engine picks a victim, usually the transaction that has done the least work or holds the fewest locks, and aborts it. The victim gets an error such as PostgreSQL SQLSTATE 40P01 or MySQL error 1213, and its locks are released so the survivors can move on.

Some systems use a timeout instead of cycle detection. They do not build a graph at all. They simply give up if any single lock wait exceeds a limit, on the assumption that a very long wait probably means a deadlock. This is cheaper but can falsely abort transactions that were just slow, and it can let a real deadlock sit until the timeout fires.

The most effective fix is consistent lock ordering. If every transaction always locks rows in the same order, say by primary key ascending, a circular wait cannot form, so a deadlock is impossible. The two transactions in the earlier example would both try to lock A before B, and one would simply wait normally for the other to finish.

Other practical defenses are keeping transactions short so locks are held for less time, touching fewer rows per transaction, and lowering the isolation level when serializable guarantees are not needed since higher levels take more and broader locks. Some teams use SELECT ... FOR UPDATE to grab all the rows they will need up front, in a known order, before doing any work.

The trade-off is that you can never fully design deadlocks away in a busy system, so production code must treat them as a normal, retryable error. The standard pattern is to catch the deadlock error code and retry the whole transaction a few times with a short randomized backoff. Aborting one victim is cheap and correct, while trying to prevent every possible deadlock would require so much locking that throughput would collapse.

Picture a banking app transferring money between two accounts. Transfer X moves money from account 100 to account 200. Transfer Y moves money from account 200 to account 100. Both run at the same instant.

Transfer X locks account 100 to debit it, then reaches for account 200 to credit it. Transfer Y has already locked account 200 to debit it and now reaches for account 100. Each holds what the other needs. The database detects the cycle in under a second, aborts one transfer with a deadlock error, and releases its locks. The surviving transfer completes, and the application retries the aborted one, which now succeeds.

The clean fix here is to always lock the lower account number first. Both transfers would then start by locking account 100, and one would wait politely for the other instead of deadlocking. This is exactly why payment and inventory systems sort the resources they touch before locking them.