.NET Architecture & System Design Interview Question #82
What is a database deadlock?
Transactions, CQRS, Event Sourcing & Distributed Consistency Mid-Level Intermediate
Quick Interview Answer
A database deadlock occurs when two or more transactions each hold resources that the others need, creating a cycle in which none can proceed. Database engines commonly detect the cycle and terminate one transaction as the deadlock victim.
Detailed Explanation
A database deadlock occurs when two or more transactions each hold resources that the others need, creating a cycle in which none can proceed. Database engines commonly detect the cycle and terminate one transaction as the deadlock victim.
Reduce deadlocks by keeping transactions short, accessing resources in a consistent order, indexing queries appropriately, and retrying operations when the database reports a transient deadlock.
Code Example
Tx A locks Row 1 -> waits for Row 2
Tx B locks Row 2 -> waits for Row 1
Database detects cycle
-> aborts one transaction