What causes deadlocks and how do you prevent them?

Question

IT Interview Practice · Accepted Answer

A **deadlock** occurs when two (or more) transactions each hold a lock the other needs, so neither can proceed — they're stuck waiting for each other forever. Databases detect deadlocks and resolve them by **aborting one transaction** (the "victim"), which must retry. Understanding the causes and prevention is important for concurrent applications.

## How a deadlock happens

```text
Transaction A: locks row 1, then wants row 2
Transaction B: locks row 2, then wants row 1
→ A waits for B (to release row 2), B waits for A (to release row 1) → DEADLOCK
  Neither can proceed; they're waiting on each other in a cycle.
```

```sql
-- Transaction A                    -- Transaction B (concurrent)
BEGIN;                              BEGIN;
UPDATE accounts SET .. WHERE id=1;  UPDATE accounts SET .. WHERE id=2;  -- each locks a row
UPDATE accounts SET .. WHERE id=2;  UPDATE accounts SET .. WHERE id=1;  -- each wants the OTHER's
-- → DEADLOCK: the database aborts one transaction
```

## How databases handle deadlocks

```text
Databases DETECT deadlocks (find the cycle of waiting transactions) and resolve them
by ABORTING one transaction (the victim) with a deadlock error → it must RETRY.
→ Your application must handle the deadlock error and retry the transaction.
```

## Prevention strategies

```text
✓ CONSISTENT LOCK ORDERING — always acquire locks in the SAME order (e.g. always
  lock the lower account id first). This breaks the cycle → the #1 prevention technique.
✓ Keep transactions SHORT — hold locks for less time (less chance to overlap)
✓ Access tables/rows in a consistent order across all transactions
✓ Use lower isolation levels where appropriate (less locking)
✓ Avoid user interaction / long operations inside a transaction (holds locks too long)
✓ Handle deadlock errors with RETRY logic (deadlocks can't be fully eliminated)
```

The **#1 prevention** is **consistent lock ordering**: if all transactions acquire locks in the same order (e.g. always the lower id first), the circular wait can't form.

## The retry pattern

```text
Since deadlocks can still happen, applications should catch the deadlock error and
RETRY the transaction (often a few times with a small delay). This is standard
resilience for concurrent database code.
```

## Why it matters

Deadlocks are an important concurrency issue in databases — they occur in any application with concurrent transactions that modify overlapping data, and understanding them is valuable senior-level knowledge for building robust concurrent systems.

Understanding the **cause** — two transactions each holding a lock the other needs, creating a circular wait where neither can proceed — is the foundation.

Knowing how databases **handle** deadlocks (detecting the cycle and aborting one transaction as a victim, which must retry) is important because it means your **application must handle deadlock errors with retry logic** — deadlocks can't be fully eliminated, so resilient concurrent code catches the deadlock error and retries (a standard, important pattern).

The **prevention strategies** are the key practical knowledge, especially the #1 technique: **consistent lock ordering** (always acquiring locks in the same order across all transactions, which breaks the circular-wait condition that causes deadlocks) — along with keeping transactions short (holding locks briefly), accessing resources in a consistent order, and avoiding long operations within transactions.

Since deadlocks cause real problems (failed transactions, errors, degraded performance) in concurrent database applications, and since understanding their cause (circular lock waits), prevention (especially consistent lock ordering and short transactions), and the necessity of retry logic is what lets you build robust concurrent systems, understanding deadlocks is valuable senior-level knowledge for any application with significant concurrent database access — a frequently-relevant operational concern and a common advanced interview topic demonstrating understanding of database concurrency and resilience.

How a deadlock happens

How databases handle deadlocks

Prevention strategies

The retry pattern

Why it matters

Related questions