Deadlocks in MySQL – Causes, Detection & Prevention
📌 Introduction
In high-concurrency systems, multiple transactions often try to access the same data simultaneously. This can lead to a serious issue called a deadlock.
👉 A deadlock occurs when two or more transactions are waiting for each other to release locks — and none of them can proceed.
Understanding deadlocks is crucial for backend developers, especially when working with MySQL + PHP / APIs / high-traffic systems.
🔒 What is a Deadlock?
A deadlock is a situation where:
- Transaction A is waiting for Transaction B
- Transaction B is waiting for Transaction A
- ❌ Both are stuck forever
👉 MySQL automatically detects deadlocks and kills one transaction to resolve it.
⚙️ How Deadlock Happens (Example)
🧾 Scenario
Two users updating two rows in reverse order.
💻 Example Queries
🔹 Transaction 1
START TRANSACTION;UPDATE accounts SET balance = balance - 100 WHERE id = 1;-- waiting for row 2
UPDATE accounts SET balance = balance + 100 WHERE id = 2;COMMIT;
🔹 Transaction 2
START TRANSACTION;UPDATE accounts SET balance = balance - 50 WHERE id = 2;-- waiting for row 1
UPDATE accounts SET balance = balance + 50 WHERE id = 1;COMMIT;
🚨 What Happens Internally
- T1 locks row 1
- T2 locks row 2
- T1 waits for row 2
- T2 waits for row 1
👉 💥 Deadlock occurs
🔍 How to Detect Deadlocks in MySQL
✅ 1. Error Message
MySQL throws:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
✅ 2. Show Engine Status
SHOW ENGINE INNODB STATUS;
👉 This gives detailed deadlock logs:
- Which queries were involved
- Which transaction was rolled back
✅ 3. Enable Deadlock Logging
SET GLOBAL innodb_print_all_deadlocks = ON;
👉 Logs all deadlocks in MySQL error log
🧑💻 PHP Handling Example
try {
$conn->begin_transaction(); $conn->query("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
$conn->query("UPDATE accounts SET balance = balance + 100 WHERE id = 2"); $conn->commit();
} catch (mysqli_sql_exception $e) { if ($e->getCode() == 1213) {
// Deadlock retry logic
echo "Deadlock detected, retrying...";
} $conn->rollback();
}
⚠️ Common Causes of Deadlocks
🔹 1. Different Order of Row Access
👉 Most common cause
🔹 2. Long Transactions
👉 Locks are held for too long
🔹 3. Missing Indexes
👉 Full table scans → more locks
🔹 4. High Concurrency
👉 Too many simultaneous updates
🔹 5. Gap Locks (InnoDB)
👉 Occur in range queries
🛡️ How to Prevent Deadlocks
✅ 1. Access Tables in Same Order
👉 Always follow consistent order
✅ 2. Keep Transactions Short
👉 Commit quickly
✅ 3. Use Proper Indexing
👉 Avoid unnecessary locks
✅ 4. Use Smaller Transactions
👉 Break large operations
✅ 5. Retry Logic (Best Practice)
👉 Always retry failed transactions
✅ 6. Use SELECT … FOR UPDATE Carefully
👉 Lock only required rows
⚖️ Deadlock vs Lock Wait Timeout
| Feature | Deadlock | Lock Wait Timeout |
|---|---|---|
| Cause | Circular waiting | Long wait |
| Detection | Automatic | Timeout-based |
| Error Code | 1213 | 1205 |
| Solution | Retry | Optimize query |
📍 Real-World Use Cases
- Banking transactions 💰
- Inventory management 📦
- Order processing systems 🛒
- Ticket booking systems 🎟️