Database develop. life cycle - Deadlock Handling

What is Deadlock?

  • A deadlock occurs in a database or multi-threaded system when two or more transactions are waiting for each other’s resources, and none can proceed.

  • It’s like two people meeting in a narrow hallway, each waiting for the other to move, and both being stuck.

Example in databases:

  • Transaction 1 locks Row A and waits for Row B.

  • Transaction 2 locks Row B and waits for Row A.

  • Both wait forever → deadlock.

Deadlock Handling Techniques

There are two main strategies: Prevention/Avoidance and Detection/Recovery.

1. Deadlock Prevention

The idea is to design the system so deadlocks never occur.

  • Ordering resources: Transactions must request resources in a predefined global order.

  • Request all resources at once: A transaction must acquire all it needs upfront, avoiding circular waiting.

  • Timeouts: If a transaction waits too long, it’s aborted and rolled back.

2. Deadlock Avoidance

Here, the system analyzes each resource request and decides whether granting it could lead to deadlock.

  • Uses the Banker’s Algorithm: ensures the system always stays in a “safe state.”

  • Example: The system checks before granting a lock if future resource allocation might cause deadlock.

3. Deadlock Detection and Recovery

  • In this approach, deadlocks are allowed to happen but are detected and resolved.

  • Detection: The system builds a wait-for graph (nodes = transactions, edges = waiting for resource). A cycle in the graph = deadlock.

  • Recovery: Once detected, the system can:

    • Abort transactions (usually the one with least cost or least progress).

    • Preempt resources (force a transaction to release some resources and roll back).

Deadlock Handling in the Data Development Cycle

  • Design phase: Use resource ordering and normalization to minimize risk.

  • Development phase: Implement proper transaction management and timeouts.

  • Testing phase: Simulate high-concurrency workloads to check for deadlocks.

  • Deployment phase: Use database engine features (like SQL Server’s deadlock detection or Oracle’s timeout settings) for automatic handling.