# 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://www.muhammetsafak.com.tr/en/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.

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.

1. **Always acquire locks in the same global order.** This is the most important rule. Lock rows in the same deterministic order everywhere — for example, always by ascending primary key. If both transactions lock X before Y, a cycle can never form; one waits for the other and finishes.
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 as briefly as possible. The longer a transaction stays open, the larger the collision window grows.
3. **Touch the fewest rows, lock targeted.** Instead of broad locks, apply `SELECT ... FOR UPDATE` only to the row you need. Locking more rows than necessary widens the collision surface.
4. **Use a single statement where possible.** Where you can, do the work in a single `UPDATE` / `INSERT ... ON CONFLICT`; then the DB manages the lock ordering for you and you remove the risk of forming a cycle in application code.
5. **Accept that deadlocks will happen anyway, and retry.** Despite every precaution, occasional deadlocks occur; the DB picks one transaction as the "victim" and aborts it. Treat this not as an error but 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://www.muhammetsafak.com.tr/en/just-ask/point-in-time-recovery-with-wal-archiving/) — Just Ask
- [How do I prevent PostgreSQL split-brain with quorum and consensus?](https://www.muhammetsafak.com.tr/en/just-ask/avoiding-postgresql-split-brain-with-quorum-and-consensus/) — Just Ask
- [Should I use a partial index on a queue table where I only ever scan 'pending' rows?](https://www.muhammetsafak.com.tr/en/just-ask/should-i-use-a-partial-index-on-a-queue-table-where/) — Just Ask