What Is Deadlock in SQL? (Quick Scoop)

Deadlock in SQL is a situation where two or more transactions block each other forever because each one is holding a resource (row, page, table, memory, etc.) that the other needs, so none of them can move forward until the database intervenes.

🔎 Simple definition

• A deadlock occurs when:
  • Transaction A holds Resource 1 and wants Resource 2.
  • Transaction B holds Resource 2 and wants Resource 1.
  • Both wait on each other, so neither can continue.

• In SQL Server and similar systems, the engine detects this “circular wait” and chooses one transaction as the deadlock victim , rolls it back, and lets the other continue.

Think of two people in a narrow hallway, each refusing to step back; someone from outside has to decide who moves.

⚙️ Deadlock in SQL terms

• In SQL Server, a deadlock happens when two or more tasks hold incompatible locks on resources and try to acquire additional locks that cause a cycle.

• Typical resources:
  • Tables or rows (lock on data)
  • Pages or indexes
  • Memory grants

• The deadlock monitor periodically checks for cycles; once detected, it:
  • Picks a victim.
  • Rolls back that transaction.
  • Releases its locks so other transactions can proceed.

🧠 Classic example scenario

Imagine two transactions running at the same time:

1. Transaction 1
   • Updates TableA, then tries to update TableB.
2. Transaction 2
   • Updates TableB, then tries to update TableA.

Because each transaction now waits on the other’s lock, neither can move ahead, and the database engine has to break the tie by killing one of them.

🔁 Deadlock vs. normal blocking

• Blocking :
  • One transaction is waiting on another, but if the first one finishes, the second can continue normally.
• Deadlock :
  • There is a loop (A waits for B, B waits for A), so no one can ever proceed unless the system intervenes.

🧩 Why deadlocks happen (common patterns)

• Transactions access resources in different orders (e.g., one does TableA → TableB, another does TableB → TableA).

• Long-running transactions holding locks for too long.
• Escalation or promotion of locks (e.g., many row locks to a table lock) causing conflicts.

• Memory-based deadlocks where concurrent queries wait on memory grants that cannot all be satisfied.

🛡️ How SQL Server handles deadlocks

• Automatically detects cyclic dependencies between tasks.

• Chooses a deadlock victim based on:
  • Deadlock priority (LOW, NORMAL, HIGH or numeric -10 to 10).

* Amount of work it would need to roll back.

• Aborts the victim transaction and returns error 1205 (deadlock victim), while others continue.

🧭 Quick practical takeaway

• A deadlock is not just “too much load”; it is a logical conflict of lock ordering.
• The fix is usually:
  • Keep transactions short.
  • Access tables in a consistent order across your code.
  • Add proper indexes and avoid unnecessary locking.
  • Handle error 1205 with retry logic in the application.

TL;DR

Deadlock in SQL is when two or more transactions permanently block each other by each holding a lock on a resource the other needs, forcing the database engine to kill one transaction (the deadlock victim) so the others can continue.

Information gathered from public forums or data available on the internet and portrayed here.