How to Fix MySQL Error 1213: Deadlock Found When Trying to Get Lock

Q: Should I use `SELECT ... FOR UPDATE` to prevent deadlocks?

Not by itself. FOR UPDATE serializes access to a row but can introduce deadlocks if two transactions each hold a FOR UPDATE on different rows and then need the other's row. Use it only when you know which rows you will need up front, and acquire them in a consistent order.

Q: Is it safe to disable `innodb_deadlock_detect`?

Only if lock-wait timeout ( innodb_lock_wait_timeout ) is low enough (≤ 10 s) to bound the wait when an actual cycle forms. Without detection, a real deadlock just waits until timeout — a 50-second wait instead of an immediate rollback.

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction is raised by InnoDB when it detects a circular wait: transaction A holds a lock that transaction B wants, while transaction B holds a lock that transaction A wants. InnoDB resolves the cycle by choosing a victim — typically the transaction with the least undo log work — and rolling it back entirely.

Impact

The rolled-back transaction returns error 1213 to the application. Unlike error 1205 (lock-wait timeout), which only rolls back the current statement by default, a deadlock always rolls back the full transaction. Applications that do not handle 1213 explicitly will surface this as an unexpected failure or a partial write. Under high concurrency a single deadlock-prone query pattern can generate hundreds of deadlocks per minute, consuming CPU on victim selection and creating retry storms.

Common Causes

Two transactions updating the same rows in opposite order (the canonical A→B / B→A cycle)
Implicit gap and next-key locks on range scans under REPEATABLE READ crossing with row locks held by another transaction
Foreign-key checks: inserting a child row acquires a shared lock on the parent row; two inserts to different children of the same parent can deadlock with concurrent parent deletes
Index not present on the WHERE predicate, causing InnoDB to scan and lock more rows than necessary
INSERT ... ON DUPLICATE KEY UPDATE — acquires both a gap lock and a record lock, which can deadlock with a concurrent INSERT at the same key range
REPLACE INTO — internally a DELETE + INSERT, acquiring exclusive locks in two steps
Bulk load or batch UPDATE touching rows in non-primary-key order across concurrent sessions
Triggers that modify additional tables and introduce implicit lock ordering outside the application's control
Application retry logic that re-enters the same transaction body after a deadlock without randomized delay, reproducing the cycle
SELECT ... FOR UPDATE used for optimistic locking with a predicate that returns more rows than expected

Troubleshooting and Resolution Steps

Read the deadlock detail immediately after it occurs:
```
SHOW ENGINE INNODB STATUS\G
```
The LATEST DETECTED DEADLOCK section shows both transactions with their lock structs, the waiting row, the held lock, and which transaction was chosen as victim. InnoDB only stores the most recent deadlock — if more are occurring, use step 2.
Enable deadlock logging to the error log:
```
SET GLOBAL innodb_print_all_deadlocks = ON;
SET PERSIST innodb_print_all_deadlocks = ON;
```
Every deadlock will now appear in the MySQL error log. Do this in production temporarily when diagnosing; disable once the pattern is identified because verbose logging adds I/O overhead.

Inspect the lock graph (MySQL 8.0+):

SELECT
    r.trx_id AS waiting_trx,
    r.trx_mysql_thread_id AS waiting_thread,
    LEFT(r.trx_query, 100) AS waiting_query,
    b.trx_id AS blocking_trx,
    b.trx_mysql_thread_id AS blocking_thread,
    LEFT(b.trx_query, 100) AS blocking_query
FROM information_schema.INNODB_TRX b
JOIN performance_schema.data_lock_waits w
    ON b.trx_id = CAST(w.BLOCKING_ENGINE_TRANSACTION_ID AS CHAR)
JOIN information_schema.INNODB_TRX r
    ON r.trx_id = CAST(w.REQUESTING_ENGINE_TRANSACTION_ID AS CHAR);

Identify the deadlock pattern from the log. The most common signature is two transactions locking the same two rows in reverse order:
```
TRX A: HOLDS LOCK ON row(id=1), WAITS FOR row(id=2)
TRX B: HOLDS LOCK ON row(id=2), WAITS FOR row(id=1)
```
Once you see this, look at the application code that performs these two writes and enforce a consistent lock acquisition order.

Enforce a consistent row-locking order. If transaction A always locks rows in ascending primary-key order and transaction B does the same, no cycle can form:

-- Bad: order depends on application-level iteration, may vary
UPDATE accounts SET balance = balance - 100 WHERE id IN (5, 2);

-- Good: always lock in ascending key order
UPDATE accounts SET balance = balance - 100 WHERE id IN (2, 5) ORDER BY id;

In application code, sort the IDs before updating:

ids = sorted([user_a_id, user_b_id])
cursor.execute("UPDATE accounts SET ... WHERE id = %s", ids[0])
cursor.execute("UPDATE accounts SET ... WHERE id = %s", ids[1])

Reduce gap locking by switching to READ COMMITTED where correctness allows:
```
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
```
READ COMMITTED does not take gap locks, only record locks. It eliminates the majority of deadlocks caused by INSERT vs. range-scan SELECT FOR UPDATE cycles.
Add a covering index on WHERE predicates used in concurrent writes. InnoDB under REPEATABLE READ locks every row it visits during an index scan, not just rows it modifies. A table-scan UPDATE locks the entire table implicitly, making deadlocks with any other write certain.
Redesign INSERT ... ON DUPLICATE KEY UPDATE hotspots. IODKU uses a gap lock + record lock pair that is especially deadlock-prone under concurrent inserts to adjacent key ranges. Alternatives:
- Application-level deduplication before the insert
- A separate SELECT check followed by INSERT inside the transaction (with retry on 1213)
- INSERT IGNORE when losing a duplicate is acceptable

Implement retry with exponential backoff and jitter:

import random, time
import pymysql
from pymysql.err import OperationalError

def run_with_retry(fn, attempts=5):
    for i in range(attempts):
        try:
            return fn()
        except OperationalError as e:
            if e.args[0] == 1213 and i < attempts - 1:
                time.sleep(0.05 * (2 ** i) + random.uniform(0, 0.02))
                continue
            raise

Jitter prevents retry storms where all victims retry simultaneously and deadlock again.

Disable deadlock detection for throughput-critical workloads (use with care):
```
SET GLOBAL innodb_deadlock_detect = OFF;
```
When detection is off, InnoDB does not walk the wait-for graph. Deadlocks are broken only by innodb_lock_wait_timeout. This can improve throughput on workloads with very high lock contention at the cost of longer waits for the victim. Only appropriate after the lock ordering has been fixed so real deadlocks are rare.

Additional Information

InnoDB picks the deadlock victim by comparing trx_weight, which is the sum of the number of locks held and the number of rows modified. The lighter transaction is rolled back.
The 40001 SQLSTATE (serialization failure) is the standard SQL signal for deadlocks and should be handled separately from other OperationalError codes in application drivers.
Unlike lock-wait timeouts (error 1205), a deadlock always rolls back the entire transaction regardless of innodb_rollback_on_timeout.
Deadlocks between DDL and DML are reported as metadata lock (MDL) conflicts, not as error 1213. Those appear in performance_schema.metadata_locks.
Monitoring: SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks' gives the cumulative deadlock count since the last restart. Alert when the rate exceeds your baseline.

Frequently Asked Questions

Q: My code only runs one UPDATE statement — how can there be a deadlock? A: Secondary index updates generate two lock acquisitions: one on the secondary index record and one on the primary key record. Two concurrent updates on different secondary index entries of the same row can deadlock at the primary key step.

Q: Can I prevent deadlocks entirely? A: In practice, no — any workload with concurrent writers can deadlock under adversarial timing. The goal is to make them rare (consistent lock ordering, good indexes, short transactions) and to handle them cleanly (retry on 1213).

Q: Should I use SELECT ... FOR UPDATE to prevent deadlocks? A: Not by itself. FOR UPDATE serializes access to a row but can introduce deadlocks if two transactions each hold a FOR UPDATE on different rows and then need the other's row. Use it only when you know which rows you will need up front, and acquire them in a consistent order.

Q: The deadlock log shows a GAP lock — what is that? A: A gap lock protects the space between index records against phantom inserts under REPEATABLE READ. Two transactions can each hold a gap lock at the same position (gap locks are compatible with each other) but a gap lock blocks an insert intent lock, which can create a cycle with another session's insert.

Q: After the deadlock, my application continued as if the transaction succeeded. Why? A: The application driver returned the exception but the calling code caught it too broadly (bare except Exception) or ignored the return value. All rows written in the rolled-back transaction were undone — the data was silently lost. Add explicit handling for SQLSTATE 40001 / error 1213.

Q: Is it safe to disable innodb_deadlock_detect? A: Only if lock-wait timeout (innodb_lock_wait_timeout) is low enough (≤ 10 s) to bound the wait when an actual cycle forms. Without detection, a real deadlock just waits until timeout — a 50-second wait instead of an immediate rollback.