How to fix ERROR 1213: Deadlock found when trying to get lock; try restarting transaction in MySQL
MySQL deadlock errors occur when two or more transactions are waiting for each other to release locks. This guide explains why deadlocks happen and provides actionable strategies to prevent, diagnose, and recover from them.
Quick fix
Fixes 1213
SHOW ENGINE INNODB STATUS;- Check SHOW ENGINE INNODB STATUS to diagnose the deadlock
- Implement automatic retry logic in your application
- Add indexes to reduce rows locked during queries
- Keep transactions short and avoid long-running operations
SHOW ENGINE INNODB STATUS;ERROR 1213: Deadlock found when trying to get lock; try restarting transaction
What this error means
A MySQL deadlock (ERROR 1213) happens when two or more concurrent transactions are unable to proceed because each transaction holds a lock that another transaction needs. Since all transactions are waiting for the same resource, none of them can ever release their lock, creating a circular dependency that MySQL must break by rolling back one transaction. This is an InnoDB-specific error that occurs in high-concurrency environments where multiple queries are modifying data simultaneously. The error message tells you to "try restarting transaction" because the automatic recovery strategy is to roll back one transaction and allow the others to proceed.
Typical symptoms
- Intermittent database failures that only occur under load or high concurrency
- Application receives 'Deadlock found' errors unpredictably during peak traffic
- Transactions work fine in development but fail in production with more concurrent users
- CI/CD pipeline or background jobs occasionally fail with deadlock errors
- Error logs show ERROR 1213 mixed with other transaction errors
Common causes
- Multiple transactions updating the same rows in different orders (most common)
- Missing or ineffective indexes causing queries to lock unnecessary rows during table scans
- Long-running transactions holding locks while waiting for other operations
- Foreign key constraints creating shared locks on referenced rows
- Gap locking in range queries when using isolation levels like REPEATABLE READ
- Implicit locking from SELECT ... FOR UPDATE without proper ordering
How to fix "ERROR 1213: Deadlock found when trying to get lock; try restarting transaction"
Connect to MySQL and run:
SHOW ENGINE INNODB STATUS;Look for the "LATEST DETECTED DEADLOCK" section in the output. This shows:
- The two transactions that deadlocked
- The exact SQL statements they were running
- Which locks they held and which they were waiting for
This information is crucial for understanding the access pattern that caused the deadlock. If you see multiple deadlocks, enable detailed logging:
SET GLOBAL innodb_print_all_deadlocks = 1;This logs all deadlocks (not just the latest) to the MySQL error log, helping you identify patterns.
The most important fix is to make your application deadlock-aware. Deadlocks are not bugs—they're expected behavior in concurrent systems. Catch the error (errno 1213 or SQLSTATE 40001) and retry the transaction.
Example in Node.js/JavaScript:
async function updateUserWithRetry(userId, data, maxRetries = 3) {
for (let attempt = 1; attempt <= maxRetries; attempt++) {
try {
await db.query('BEGIN');
// Your transaction logic here
await db.query('UPDATE users SET ... WHERE id = ?', [userId, ...]);
await db.query('COMMIT');
return;
} catch (error) {
if (error.code === 'ER_LOCK_DEADLOCK' && attempt < maxRetries) {
// Deadlock detected, retry with exponential backoff
const delay = Math.pow(2, attempt) * 100; // 200ms, 400ms, 800ms
await new Promise(r => setTimeout(r, delay));
continue;
}
throw error; // Other error or max retries exceeded
}
}
}Most database libraries have built-in deadlock detection (check for errno 1213 or SQLSTATE 40001). Retry with exponential backoff to avoid overwhelming the database.
When MySQL cannot use an index to find rows, it scans the entire table and locks every row it examines. This dramatically increases deadlock risk.
Check which queries are involved in the deadlock using SHOW ENGINE INNODB STATUS, then optimize them:
-- BAD: No index, full table scan locks all rows
SELECT * FROM orders WHERE customer_id = 123 FOR UPDATE;
-- GOOD: Index allows targeting specific rows
CREATE INDEX idx_customer_id ON orders(customer_id);
SELECT * FROM orders WHERE customer_id = 123 FOR UPDATE;Use EXPLAIN to verify your queries use indexes:
EXPLAIN SELECT * FROM orders WHERE customer_id = 123 FOR UPDATE;
-- Look for "Using index" or "ref" in the outputProper indexes reduce the number of rows locked, which is one of the best ways to prevent deadlocks.
The longer a transaction holds locks, the more likely it will deadlock with other transactions. Refactor your code to minimize lock duration:
// BAD: Long transaction with unrelated work
async function badExample() {
await db.query('BEGIN');
// These locks are held for seconds while doing external work
const user = await db.query('SELECT * FROM users WHERE id = ? FOR UPDATE', [userId]);
const result = await slowExternalAPI.call(); // Locks held during this!
await db.query('UPDATE users SET last_api_call = ? WHERE id = ?', [result, userId]);
await db.query('COMMIT');
}
// GOOD: Quick transaction, external work outside
async function goodExample() {
const result = await slowExternalAPI.call(); // NO locks held here
await db.query('BEGIN');
const user = await db.query('SELECT * FROM users WHERE id = ? FOR UPDATE', [userId]);
await db.query('UPDATE users SET last_api_call = ? WHERE id = ?', [result, userId]);
await db.query('COMMIT'); // Locks released immediately
}Move expensive operations (API calls, file I/O, processing) outside of transactions.
One of the most common causes of deadlocks is when transactions access rows in different orders. If Transaction A locks Row 1 then Row 2, while Transaction B locks Row 2 then Row 1, they will deadlock.
// BAD: Inconsistent access order
// Transaction 1
await db.query('UPDATE users SET balance = balance - ? WHERE id = ?', [amount, userId1]);
await db.query('UPDATE users SET balance = balance + ? WHERE id = ?', [amount, userId2]);
// Transaction 2 (accesses in opposite order)
await db.query('UPDATE users SET balance = balance - ? WHERE id = ?', [amount, userId2]);
await db.query('UPDATE users SET balance = balance + ? WHERE id = ?', [amount, userId1]);
// GOOD: Always access rows in the same order (e.g., by ID)
// Both transactions do this:
const [first, second] = [userId1, userId2].sort((a, b) => a - b);
await db.query('UPDATE users SET balance = balance - ? WHERE id = ?', [amount, first]);
await db.query('UPDATE users SET balance = balance + ? WHERE id = ?', [amount, second]);Establish a consistent ordering rule (e.g., always lock by ascending ID) and apply it everywhere in your codebase.
For complex scenarios, you may adjust the transaction isolation level or use MySQL 8.0+ lock hints:
-- Use READ COMMITTED isolation to avoid gap locks (faster but less strict)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- Your queries here use fewer locks
COMMIT;
-- MySQL 8.0+: Use NOWAIT to fail fast instead of waiting for locks
SELECT * FROM orders WHERE id = 123 FOR UPDATE NOWAIT;
-- MySQL 8.0+: Use SKIP LOCKED to ignore locked rows
SELECT * FROM orders WHERE id = 123 FOR UPDATE SKIP LOCKED;These are advanced techniques. Start with the basic solutions (retries, indexes, short transactions, consistent ordering) first.
Advanced notes
Key principle: Deadlocks are not always preventable—they're expected in concurrent systems with fine-grained locking. The key is making your application resilient to them.
For high-traffic systems: If deadlocks persist even after optimization, consider:
- Using READ COMMITTED isolation level instead of REPEATABLE READ (MySQL default) to reduce gap locks
- Disabling deadlock detection in favor of innodb_lock_wait_timeout if you have very high concurrency and deadlock detection overhead is significant
- Using read replicas for read-only queries to reduce write contention
- Partitioning large tables to reduce lock scope per query
Monitoring: Set up alerts for ERROR 1213 in your application logs. A small number of deadlocks is normal and expected; high rates indicate an application design problem that needs investigation.
Foreign key gotcha: Foreign key constraints create shared locks on referenced rows, which can be a common source of deadlocks. If you experience frequent deadlocks with related tables, review your foreign key usage and consider if you need the constraints at the application level instead.
Frequently asked questions
- What does "1213" mean?
- A MySQL deadlock (ERROR 1213) happens when two or more concurrent transactions are unable to proceed because each transaction holds a lock that another transaction needs. Since all transactions are waiting for the same resource, none of them can ever release their lock, creating a circular dependency that MySQL must break by rolling back one transaction. This is an InnoDB-specific error that occurs in high-concurrency environments where multiple queries are modifying data simultaneously.
- How do I fix "1213"?
- MySQL deadlock errors occur when two or more transactions are waiting for each other to release locks. This guide explains why deadlocks happen and provides actionable strategies to prevent, diagnose, and recover from them.
- What causes "1213"?
- Multiple transactions updating the same rows in different orders (most common) Missing or ineffective indexes causing queries to lock unnecessary rows during table scans Long-running transactions holding locks while waiting for other operations
- Is the "1213" error serious?
- It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.
Official resources & further reading
- 📚MySQL Official Documentation
- 📖
- 📖MySQL 8.0 Reference Manual - InnoDB Deadlock Example(Official Docs)
- 📖MySQL 8.0 Server Error Reference(Official Docs)