A deadlock is a state in a multi-process or multi-threaded system where two or more processes are permanently blocked because each is waiting for a resource that is held by another process in the same set. This leads to a circular chain of dependencies, halting all progress. While the concept originates from operating systems, it is a critical issue in database management systems (DBMS) where concurrent transactions compete for database resources (like locks on rows, pages, or tables).

🔧 The Four Necessary Conditions for Deadlock

For a deadlock to occur, all four of the following conditions must be met simultaneously. This is often referred to as the Coffman Conditions:

1. Mutual Exclusion: At least one resource must be held in a non-shareable mode. Only one process can use the resource at a time. This is a fundamental property of locking mechanisms in databases.
2. Hold and Wait: A process is holding at least one resource and is waiting to acquire additional resources that are currently held by other processes.
3. No Preemption: A resource cannot be forcibly taken from a process. It can only be released voluntarily by the process that is holding it after that process has completed its task.
4. Circular Wait: A set of processes {P0​,P1​,…,Pn​} exists such that P0​ is waiting for a resource held by P1​, P1​ is waiting for a resource held by P2​, and so on, with Pn​ waiting for a resource held by P0​. This forms a closed loop of dependencies.

🧪 Real-World Examples

• Databases: Two transactions updating rows in reverse order can deadlock.
• Operating Systems: Competing processes for I/O devices or memory.
• Networks: Devices waiting on each other for socket release.

🧰 Strategies for Handling Deadlocks

There are three primary approaches to handling deadlocks:

1. Deadlock Prevention

This strategy is to design the system in a way that at least one of the four necessary conditions for deadlock can never hold.

• Eliminate Hold and Wait:
  • Method 1: A process must request and be granted all its resources before it begins execution. This can be inefficient as resources may be tied up for a long time.
  • Method 2: A process must release all the resources it is currently holding before requesting any new ones. This can lead to data inconsistency if a process needs to release resources mid-transaction.
• Eliminate Circular Wait:
  • Assign a unique number or priority to all resources. A process can only request resources in a strictly increasing (or decreasing) order of their assigned numbers. This prevents the formation of a circular chain. This is the most practical and common prevention technique in databases.

2. Deadlock Avoidance

This approach uses a “smarter” algorithm to allocate resources only if the system can remain in a “safe state,” meaning there is a sequence in which all processes can finish without causing a deadlock. The most well-known algorithm for this is the Banker’s Algorithm. Deadlock avoidance is more complex and requires advanced knowledge about the maximum resources each process will need.

3. Deadlock Detection and Recovery

This strategy allows deadlocks to occur, and then the system detects them and takes action to resolve them.

• Detection: The system periodically checks for cycles in the resource allocation graph. If a cycle is found, a deadlock is detected.
• Recovery: Once a deadlock is detected, a recovery algorithm is triggered. Common recovery techniques include:
  • Process Termination: Aborting one or more of the deadlocked processes. The “victim” process is chosen based on factors like priority, how long it has been running, or how many resources it holds.
  • Resource Preemption: Forcibly taking a resource from a process and giving it to another. This requires a rollback mechanism to handle the state changes of the preempted process.

🔧 Best Practices to Control Deadlocks

1️⃣ Access Tables in a Consistent Order

• Always access resources (tables/rows) in the same sequence across queries.
• Example: If Table1 is always updated before Table2, it avoids circular waits.

2️⃣ Keep Transactions Short and Fast

• The longer a transaction runs, the higher the chance of locking conflicts.
• Avoid user interaction (e.g., waiting for input) inside transactions.

3️⃣ Use the Lowest Isolation Level Possible

• Higher isolation levels (e.g., SERIALIZABLE) hold locks longer.
• Use READ COMMITTED SNAPSHOT ISOLATION (RCSI) or snapshot isolation to reduce blocking.

ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON;

4️⃣ Avoid Lock Escalation

• Large updates may escalate row/page locks to a table lock.
• Break big updates into smaller batches.

WHILE (1=1)
BEGIN
   DELETE TOP (1000) FROM Orders WHERE Status = 'Closed';
   IF @@ROWCOUNT = 0 BREAK;
END

5️⃣ Proper Indexing

• Missing or poor indexes cause queries to scan large portions of a table, holding locks longer.
• Use covering indexes and seek-based queries to minimize lock contention.

6️⃣ Avoid Unnecessary Hints

• Don’t overuse locking hints (HOLDLOCK, TABLOCKX) unless required.
• They can force more restrictive locks and increase deadlock chances.

7️⃣ Detect and Monitor Deadlocks

• Enable Extended Events or Trace Flags (e.g., 1222) to capture deadlock graphs.

sqlCopyEditDBCC TRACEON(1222, -1);

• Use sys.dm_tran_locks and sys.dm_exec_requests to check active locks.

8️⃣ Retry Logic in Applications

• Since SQL Server automatically chooses a victim, implement retry logic in applications.
• Example in pseudo-code:

try {
   ExecuteTransaction();
}
catch (SqlException ex) {
   if (ex.Number == 1205) // Deadlock victim error
      RetryTransaction();
}

✅ Summary

• Deadlock = two processes blocking each other in a cycle.
• SQL Server automatically resolves it by killing one transaction.
• Best practices to prevent deadlocks:
  • Access resources in the same order
  • Keep transactions short
  • Use proper indexing
  • Enable row-versioning (RCSI)
  • Monitor and log deadlocks
  • Add retry logic in applications