# What rules should I follow to prevent database deadlocks?

> Acquire locks in one global order such as ascending PK, keep transactions short, target `FOR UPDATE` at the fewest rows, and retry the rest with backoff.

- Asked: 2026-06-02
- Answered: 2026-06-05
- Asked by: Sinan
- Tags: dayaniklilik, veritabani, postgresql
- Source: https://muhammetsafak.com/just-ask/detecting-and-preventing-database-deadlocks/
- Language: en-US
- Author: Muhammet Şafak

---
**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?


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.

## Short answer

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](/just-ask/webhook-idempotency-and-hmac-for-duplicate-payment-notifications/).

## Why

1. **A deadlock is the consequence of inconsistent ordering.** If both transactions request locks in the same order, a cycle mathematically cannot form.

2. **A long transaction widens the collision window.** The longer a lock is held, the more likely someone else runs into it.

3. **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

1. **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.

2. **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.

3. **Touch the fewest rows, lock targeted.** Apply `SELECT ... FOR UPDATE` only to the row you need.

4. **Use a single statement where possible.** With a single `UPDATE` / `INSERT ... ON CONFLICT` the DB manages the lock ordering for you.

5. **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.

## Related Reading

- [How do I rewind to seconds before a disaster with WAL archiving and PITR?](https://muhammetsafak.com/just-ask/point-in-time-recovery-with-wal-archiving/) — Just Ask
- [How do I prevent PostgreSQL split-brain with quorum and consensus?](https://muhammetsafak.com/just-ask/avoiding-postgresql-split-brain-with-quorum-and-consensus/) — Just Ask
- [When should I switch to a covering index with INCLUDE to get an index-only scan?](https://muhammetsafak.com/just-ask/switch-covering-index-include-get-index-only-scan/) — Just Ask