What rules should I follow to prevent database deadlocks?
Question
Two transactions run concurrently. A locked X in Table1 and wants to update Y in Table2; at the same time B locked Y in Table2 and wants to update X in Table1. They wait on each other forever. When designing DB operations in application code, what rules (lock ordering, short transactions, etc.) should I follow to minimize deadlock risk?
Answer
Short answer
What you’re hitting is a classic lock-ordering cycle: A locks X then Y, B locks Y then X. Break the cycle and you solve the deadlock at the root.
Context
The real issue is this: a deadlock isn’t bad luck, it’s the mathematical consequence of inconsistent lock ordering. When two transactions request locks in different orders, a cycle forms and each waits on the other. Since you’ll absorb the irreducible remainder with retries, the operation has to be idempotent — I covered the payment-side version of that same discipline in the duplicate-notification and idempotency answer.
Why
-
A deadlock is the consequence of inconsistent ordering. If both transactions request locks in the same order, a cycle mathematically cannot form.
-
A long transaction widens the collision window. The longer a lock is held, the more likely someone else runs into it.
-
Raising the isolation level isn’t the fix. It’s the common mistake; it usually doesn’t help and instead adds more locks.
What to do
-
Always acquire locks in the same global order. The most important rule. Lock rows in the same deterministic order everywhere — for example, always by ascending primary key.
-
Keep the transaction short and narrow. Move slow/external work (API calls, file writes, waiting on the user) outside the transaction; acquire the lock as late as possible and hold it briefly.
-
Touch the fewest rows, lock targeted. Apply
SELECT ... FOR UPDATEonly to the row you need. -
Use a single statement where possible. With a single
UPDATE/INSERT ... ON CONFLICTthe DB manages the lock ordering for you. -
Accept that deadlocks will happen anyway, and retry. The DB picks one transaction as the “victim” and aborts it; treat this as an expected condition, make the operation idempotent and retry with backoff.
Bottom line: I’d set up the trio of consistent lock order + short transactions + retry-on-deadlock. A common mistake: trying to solve deadlocks by raising the isolation level. That usually doesn’t help and instead adds more locks. Break the cycle, don’t force the isolation level.
Compiled from questions I have run into in the field or been asked.
Comments
Sign in with your GitHub account to join the discussion. Comments are stored in GitHub Discussions.