In multi-user concurrent databases, a Deadlock is a mutual dependency condition where two or more transactions cannot proceed because each holds a lock that the other transaction requires to continue.
1Transaction 1: Holds Lock on Row A ───> Waiting for Lock on Row B (Held by Tx 2)
2 ▲ │
3 │ ▼
4Transaction 2: Waiting for Lock on Row A <─── Holds Lock on Row B
Neither transaction can proceed; without automated intervention, both transactions would wait indefinitely.
How InnoDB Detects and Resolves Deadlocks
InnoDB features an internal Deadlock Detection Algorithm (controlled by innodb_deadlock_detect = ON):
1
InnoDB maintains a Wait-For Graph of lock requests.
2
If a cycle is detected in the graph, a deadlock exists.
3
InnoDB automatically breaks the deadlock by choosing a Victim Transaction (the transaction with the smallest amount of updated/inserted/deleted rows) and rolls it back!
4
The surviving transaction proceeds to completion, while the victim transaction receives:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction.
Diagnosing Deadlocks with SHOW ENGINE INNODB STATUS
To inspect the exact SQL queries, locks, and transactions involved in the most recent deadlock:
1SHOWENGINEINNODBSTATUS;
Look for the LATEST DETECTED DEADLOCK section in the output:
Transaction 1: Shows the client IP, active SQL statement, and the lock held and requested.
Transaction 2: Shows the conflicting lock request.
Decision: Shows which transaction InnoDB rolled back.
To log all deadlocks permanently into the MySQL server error log:
1SETGLOBAL innodb_print_all_deadlocks =ON;
Best Practices to Prevent and Mitigate Deadlocks
1. Access Tables and Rows in a Consistent Order!
The #1 cause of deadlocks is inverted access order:
If Service A updates Account 1, then Account 2.
And Service B updates Account 2, then Account 1.
Simultaneous execution guarantees a deadlock!
Rule: Always sort IDs in your application before locking:
IDs = [target_id, source_id].sort()
Both transactions will lock the lower ID first, serializing access and eliminating the cycle!
2. Keep Transactions Short and Focused
Do not perform external HTTP API calls, image processing, or heavy calculations inside open transactions.
Acquire locks as late as possible immediately before committing.
3. Ensure All Queries Use Precise Indexes
As learned in Topic 27, unindexed queries lock entire tables and gaps, dramatically magnifying the probability of deadlocks.
4. Application Retry Logic
In distributed systems, deadlocks are not necessarily software bugs; they are a natural consequence of high concurrency. Production applications must always implement Automatic Transaction Retries: