How to Detect Deadlocks
MySQL logs deadlock information internally. To view the most recent deadlock:
Syntax:
SHOW ENGINE INNODB STATUS; This command returns a detailed report including:
- The conflicting transactions
- The SQL statements involved
- The lock types and affected rows
Deadlock Prevention Strategies
- Use Consistent Locking Order: Always acquire locks in the same sequence across transactions.
- Keep Transactions Short and Fast: Minimize the time locks are held by optimizing queries and avoiding unnecessary operations.
- Choose Appropriate Isolation Levels: Use
READ COMMITTEDinstead ofREPEATABLE READwhen strict consistency isn’t required. - Avoid Manual Locking Unless Necessary: Let InnoDB handle row-level locking automatically unless explicit control is required.
- Limit Rows Affected: Use
WHEREclauses andLIMITto reduce the scope of locked data. - Implement Retry Logic in Application Code: If a deadlock occurs, retry the transaction after a short delay using exponential backoff.
Example: Deadlock Detection and Resolution
The following example demonstrates how a deadlock can occur when two transactions attempt to lock rows in different orders. MySQL’s InnoDB engine will automatically detect this situation and resolve it by rolling back one transaction.
Syntax & Example:
-- Transaction A
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Transaction B
START TRANSACTION;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
-- Transaction A waits for row 2
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
-- Transaction B waits for row 1
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; In this scenario, both transactions wait on each other, creating a deadlock.
InnoDB automatically detects the deadlock and rolls back one of the transactions to allow the other to proceed.