Part 129: Deadlocks: Detection, Prevention & Resolution

A Deadlock occurs when two transactions are each waiting for the other to release a lock, creating a circular dependency.

1. The Deadlock Scenario

Transaction 1: Holds Lock on Row A ──► Waiting for Lock on Row B
                                              │
Transaction 2: Holds Lock on Row B ──► Waiting for Lock on Row A

Neither transaction can ever make progress!

2. Resolution & Prevention

• Automatic Resolution: The database engine runs a background Wait-For Graph detector. Upon detecting a deadlock cycle, it aborts the cheaper transaction (the victim) with an error (40P01: deadlock detected).
• Prevention Best Practice: Always lock resources in the exact same alphabetical or ID order across all application code!

Key Takeaways

• Enforce consistent acquisition ordering across all queries to prevent deadlocks.

In Part 130, we explore Pessimistic vs Optimistic Concurrency Control: SELECT FOR UPDATE!