Deadlocks

A deadlock occurs when two or more transactions are waiting for each other to release resources, and none of them can continue.

This is a very common database concurrency problem and an important system design interview topic.

Simple Example

Suppose two transactions update two accounts:

• Transaction 1 locks Account A and needs Account B.
• Transaction 2 locks Account B and needs Account A.

sequenceDiagram
    participant T1 as Transaction 1
    participant DB as Database
    participant T2 as Transaction 2

    T1->>DB: Lock Account A
    T2->>DB: Lock Account B

    T1->>DB: Request Account B
    T2->>DB: Request Account A

    Note over DB: T1 waits for T2
    Note over DB: T2 waits for T1

    Note over T1,T2: DEADLOCK

Neither transaction can proceed because each transaction is waiting for the other.

Complete Example — Bank Transfer

Consider two simultaneous money transfers:

Transaction 1:
Account A → Account B

Transaction 2:
Account B → Account A

If both transactions acquire locks in different orders, a deadlock can occur.

sequenceDiagram
    participant T1 as Transfer A → B
    participant T2 as Transfer B → A
    participant DB as Database

    T1->>DB: Lock Account A
    T2->>DB: Lock Account B

    T1->>DB: Request Account B
    T2->>DB: Request Account A

    DB-->>T1: Waiting
    DB-->>T2: Waiting

    Note over DB: Circular wait detected

    DB-->>T1: Deadlock victim / rollback
    T2->>DB: Continue
    T2->>DB: Commit

Why Does a Deadlock Happen?

A typical deadlock has four conditions:

1. Mutual Exclusion — a resource can be held by one transaction.
2. Hold and Wait — a transaction holds one resource while waiting for another.
3. No Preemption — resources cannot simply be taken away.
4. Circular Wait — transactions form a waiting cycle.

flowchart LR
    A[Transaction 1] -->|Holds| B[Resource A]
    B -->|Waiting for| C[Resource B]
    C -->|Held by| D[Transaction 2]
    D -->|Waiting for| A

    style A fill:#fff
    style D fill:#fff

How Databases Detect Deadlocks

Databases can maintain a wait-for graph to identify circular dependencies.

flowchart LR
    T1[Transaction 1] -->|waiting| T2[Transaction 2]
    T2 -->|waiting| T3[Transaction 3]
    T3 -->|waiting| T1

    C[Circular Dependency] --> T1

When a cycle is detected, the database can choose one transaction as the deadlock victim, roll it back, and allow the other transaction to continue.

Deadlock vs Blocking

How to Prevent Deadlocks

1. Always Acquire Locks in the Same Order

This is one of the most important techniques.

Instead of:

Transaction 1: A → B
Transaction 2: B → A

Always use:

Transaction 1: A → B
Transaction 2: A → B

sequenceDiagram
    participant T1 as Transaction 1
    participant T2 as Transaction 2
    participant DB as Database

    T1->>DB: Lock Account A
    T1->>DB: Lock Account B
    T1->>DB: Update
    T1->>DB: Commit

    T2->>DB: Lock Account A
    T2->>DB: Lock Account B
    T2->>DB: Update
    T2->>DB: Commit

    Note over T1,T2: Same lock ordering prevents circular wait

2. Keep Transactions Short

Do not keep database locks while performing unnecessary work such as external API calls, file processing, or long calculations.

flowchart LR
    A[Begin Transaction] --> B[Database Operation]
    B --> C[Commit]
    C --> D[External API Call]

    style A fill:#fff
    style C fill:#fff

Prefer committing the database transaction before performing unrelated long-running operations when the business workflow allows it.

3. Access Data Consistently

If different parts of the application access tables in unpredictable orders, the probability of deadlocks increases.

Define a consistent access pattern across services and repository methods.

4. Use Appropriate Indexes

Poor indexing can cause queries to examine many rows and hold locks longer than necessary.

For example:

UPDATE Orders
SET Status = 'Processed'
WHERE OrderId = 10001;

If OrderId is properly indexed, the database can locate the required row efficiently instead of unnecessarily scanning a large table.

5. Avoid Unnecessary High Isolation

Very strong isolation can increase locking and contention. Choose the isolation level based on the actual business requirement.

Handling Deadlocks in Application Code

Even well-designed systems can experience occasional deadlocks under heavy concurrency.

A common strategy is:

flowchart TD
    A[Start Transaction] --> B[Execute Operation]
    B --> C{Deadlock?}
    C -->|No| D[Commit]
    C -->|Yes| E[Rollback]
    E --> F{Retry Allowed?}
    F -->|Yes| G[Wait with Backoff]
    G --> A
    F -->|No| H[Return Error]

The retry should be limited and use backoff rather than immediately retrying in a tight loop.

Example Retry in C#

for (int attempt = 1; attempt <= 3; attempt++)
{
    try
    {
        await ProcessTransferAsync();
        break;
    }
    catch (SqlException ex) when (IsDeadlock(ex))
    {
        if (attempt == 3)
            throw;

        await Task.Delay(TimeSpan.FromMilliseconds(100 * attempt));
    }
}

In production, the retry policy should also consider jitter, transaction idempotency, and which database errors are genuinely retryable.

Deadlocks in Microservices

Traditional database deadlocks are usually local to a database transaction. In distributed systems, you can also have distributed resource contention.

flowchart LR
    A[Order Service] --> B[Order DB]
    C[Payment Service] --> D[Payment DB]

    A -->|waiting| C
    C -->|waiting| A

    Note[Distributed workflow needs different coordination patterns]

For distributed business workflows, patterns such as Saga, idempotency, timeouts, retries, and compensation are commonly used rather than trying to create one giant distributed database transaction.

Deadlock vs Race Condition

Interview Scenario

Interviewer: Your production SQL Server is frequently reporting deadlocks. What would you do?

Answer:

1. Capture the deadlock graph and identify the participating queries.
2. Find which resources and indexes are involved.
3. Check whether transactions acquire resources in different orders.
4. Keep transactions as short as possible.
5. Review missing or inefficient indexes.
6. Reduce unnecessary locking and contention.
7. Use an appropriate isolation level.
8. Add bounded retry with exponential backoff where the operation is safe to retry.
9. Fix the underlying query/transaction pattern instead of relying only on retries.

Common Interview Questions

• What is a database deadlock?
• Deadlock vs blocking?
• What causes a deadlock?
• How does a database detect a deadlock?
• What is a deadlock victim?
• How can you prevent deadlocks?
• Why does consistent lock ordering help?
• How can indexes reduce deadlocks?
• Should you retry a deadlocked transaction?
• How would you troubleshoot deadlocks in production?

Key Takeaways

• Deadlock = transactions waiting for each other in a cycle.
• Deadlock is different from normal blocking.
• Acquire resources in a consistent order.
• Keep transactions short.
• Use appropriate indexes and isolation levels.
• Databases can detect deadlocks and roll back a victim transaction.
• Use bounded retry with backoff when retrying is safe.
• Do not solve every deadlock by blindly increasing timeouts or retries.

Conclusion

Deadlocks are a natural risk in highly concurrent systems. A good system design prevents most deadlocks through consistent resource ordering, short transactions, efficient queries, appropriate isolation, and controlled retries.