What is a Deadlock?

Deadlocks in Relational Databases

A deadlock is an impasse that occurs when two or more transactions have mutually circular lock dependencies.

Deadlock Cycle

Transaction 1: Holds Lock on Row A  --->  Requests Lock on Row B (Blocked)
                      ^                               |
                      |                               v
Transaction 2: Requests Lock on Row A (Blocked) <---  Holds Lock on Row B

Prevention & Handling Strategies

• Consistent Lock Ordering: Always access and update tables and rows in the identical sequence throughout all application code.
• Short Transactions: Minimize the duration locks are held by eliminating non-database I/O inside transaction blocks.
• Deadlock Victim Detection: The database engine automatically terminates the transaction with lower rollback cost and raises error 40P01.