MySQL ERROR 1213: Deadlock found when trying to get lock; try restarting transaction

Your application logs show ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction. One transaction was rolled back; the other completed normally. InnoDB broke a circular lock wait by selecting the cheaper transaction as the victim. A few deadlocks per hour in high-concurrency OLTP is normal. A sustained storm is not: it means transactions are colliding under load.

The error is raised instantly. There is no timeout. InnoDB detects the cycle in the wait-for graph and rolls back the victim before either transaction waits. Without retry logic, users see failures. With retry logic, a high deadlock rate burns CPU and latency on repeated attempts.

What this means

When two transactions each hold a lock and request a conflicting lock held by the other, InnoDB detects the cycle in the wait-for graph and rolls back the cheaper victim. Victim selection favors the transaction that has modified fewer rows. The survivor proceeds normally.

The most recent deadlock is exposed in SHOW ENGINE INNODB STATUS under LATEST DETECTED DEADLOCK. Only one deadlock is retained in memory; the next one overwrites it. The authoritative cumulative counter is lock_deadlocks in INFORMATION_SCHEMA.INNODB_METRICS, which is not available in SHOW GLOBAL STATUS. By default, deadlocks are not logged to the error log unless you enable innodb_print_all_deadlocks.

A rate below one per hour typically requires no action beyond application-level retry. A sustained rate above one per minute indicates a structural problem such as inconsistent lock ordering, missing indexes causing gap locks, or hot-row contention.

flowchart LR
    T1A["Txn 1 locks row 1"] --> T1W["Txn 1 waits for row 2"]
    T2A["Txn 2 locks row 2"] --> T2W["Txn 2 waits for row 1"]
    T1W --> DET["InnoDB detects cycle"]
    T2W --> DET
    DET --> VIC["Rollback victim"]
    VIC --> SUR["Survivor proceeds"]

Common causes

Quick checks

Run these safe, read-only commands to assess scope.

# Authoritative cumulative deadlock counter
mysql -e "SELECT \`COUNT\` FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_deadlocks';"

# Most recent deadlock retained in memory (transient)
mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A 40 "LATEST DETECTED DEADLOCK"

# Whether all deadlocks are written to the error log
mysql -e "SHOW GLOBAL VARIABLES LIKE 'innodb_print_all_deadlocks';"

# Deadlock detection itself (should be ON)
mysql -e "SHOW GLOBAL VARIABLES LIKE 'innodb_deadlock_detect';"

# Row lock wait counters and average wait time
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';"

# MySQL 8.0+: current lock waits
mysql -e "SELECT * FROM performance_schema.data_lock_waits;"

# Long-running transactions that may be holding locks
mysql -e "SELECT trx_mysql_thread_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds, trx_query FROM information_schema.INNODB_TRX ORDER BY trx_started ASC LIMIT 10;"

How to diagnose it

1. Distinguish deadlock from lock wait timeout. ERROR 1213 is instant deadlock detection. A lock wait timeout produces ERROR 1205 (HY000): Lock wait timeout exceeded. Deadlocks need ordering or indexing fixes; timeouts need shorter transactions or reduced contention.

2. Capture SHOW ENGINE INNODB STATUS immediately after the error. Find the LATEST DETECTED DEADLOCK section. Identify the two (or more) transactions, the SQL statements they were executing, the locks they held (HOLDS THE LOCK), and the locks they were waiting for (WAITS FOR). If the section was overwritten, proceed to step 3.

3. Quantify the rate. Query lock_deadlocks from INFORMATION_SCHEMA.INNODB_METRICS, wait a few minutes, and query again. A rising count confirms an active pattern rather than a one-off event.

4. Enable full deadlock logging. If you need historical context beyond the single in-memory record, run:

   SET GLOBAL innodb_print_all_deadlocks = ON;
   

   This writes every detected deadlock to the error log. Warning: disable it after the incident to prevent unbounded log growth under heavy contention.

5. Map the lock types. In the deadlock output, note whether the locks are X (exclusive), S (shared), rec but not gap (record only), or next key (record plus gap). Next-key locks on unexpected rows strongly suggest a missing index or gap locks from REPEATABLE READ.

6. Check the query plans. Run EXPLAIN on each statement involved. An unindexed WHERE clause under REPEATABLE READ causes InnoDB to lock scanned rows with next-key locks, turning a single-row intention into a wide-range lock.

7. Check for hot rows. If both transactions in the deadlock graph target the same primary key or unique index value, you have hot-row contention. Verify by querying performance_schema.data_locks for multiple transactions holding or waiting on the same OBJECT_NAME and INDEX_NAME entry.

Metrics and signals to monitor

Fixes

Enforce consistent lock ordering. If Transaction A updates account 100 then 200, and Transaction B updates account 200 then 100, they will deadlock under contention. Refactor code so all transactions acquire locks on the same set of rows in the same order, such as ascending by primary key. Tradeoff: this may require restructuring business logic or batching workflows.

Add missing indexes. If EXPLAIN shows a full table scan on an UPDATE or DELETE, add an index that covers the WHERE column. This narrows InnoDB’s lock range from a next-key scan to a specific record lock. Tradeoff: additional indexes increase write overhead and disk usage.

Reduce hot-row updates. For counters, queues, or balance rows that must be updated concurrently, consider splitting the counter across multiple rows, using batched updates, or moving sequence generation outside the transaction. Tradeoff: application complexity increases.

Kill a stuck transaction manually. During an active deadlock storm, identify a long-running transaction with INNODB_TRX and terminate it:

KILL <trx_mysql_thread_id>;

Warning: this is disruptive. The transaction rolls back and loses its work; the application must retry.

Disable deadlock detection only as a last resort. Under extreme concurrency, the cost of deadlock detection itself can become a CPU bottleneck. Setting innodb_deadlock_detect = OFF removes that overhead, but deadlocks are then resolved only by innodb_lock_wait_timeout (default 50 seconds). This changes the failure mode from instant rollback to prolonged hangs. Use this only when profiling confirms detection overhead is the primary bottleneck, and only with application timeout handling.

Prevention

• Application retry logic. Catch ERROR 1213 and retry the transaction. Retries should be bounded, use exponential backoff, and be idempotent. Log retries so a rising rate surfaces as an operational signal.
• Index hygiene review. Periodically audit queries involved in deadlocks. Any UPDATE, DELETE, or SELECT FOR UPDATE that scans a large range without an index is a deadlock amplifier.
• Transaction brevity. Hold locks for the shortest possible time. Do not perform external API calls, user interaction, or heavy computation inside a database transaction.
• Isolation level awareness. REPEATABLE READ (the default) uses gap locks and next-key locks more aggressively than READ COMMITTED. If your application can tolerate non-repeatable reads, switching isolation levels can reduce the deadlock surface. This is an architectural decision, not a quick fix.
• Monitor lock_deadlocks trend. Plot the rate from INFORMATION_SCHEMA.INNODB_METRICS. A flat baseline that suddenly steps upward after a deployment is often the first sign of a new missing index or a changed query pattern.

How Netdata helps

• Charts lock_deadlocks from INFORMATION_SCHEMA.INNODB_METRICS, which SHOW GLOBAL STATUS does not expose.
• Correlates deadlock spikes with Threads_running, Innodb_row_lock_waits, and per-query latency digests to isolate lock ordering, missing indexes, or throughput surges.
• Alerts on sustained deadlock rate thresholds (for example, more than one per minute) without error log parsing.
• Retains historical context after SHOW ENGINE INNODB STATUS has been overwritten.

Related guides

• How MySQL actually works in production: a mental model for operators: /guides/mysql/how-mysql-works-in-production/
• MySQL Aborted_connects and Aborted_clients climbing: diagnosis: /guides/mysql/mysql-aborted-connections/
• MySQL connection exhaustion: detection, diagnosis, and prevention: /guides/mysql/mysql-connection-exhaustion/
• MySQL: got a packet bigger than ‘max_allowed_packet’ bytes - causes and fixes: /guides/mysql/mysql-max-allowed-packet-errors/
• MySQL monitoring checklist: the signals every production instance needs: /guides/mysql/mysql-monitoring-checklist/
• MySQL monitoring maturity model: from survival to expert: /guides/mysql/mysql-monitoring-maturity-model/
• MySQL Threads_created climbing: thread cache churn and missing pooling: /guides/mysql/mysql-thread-cache-churn/
• MySQL Threads_connected vs Threads_running: which one to actually alert on: /guides/mysql/mysql-threads-connected-vs-threads-running/
• MySQL ERROR 1040 (HY000): Too many connections - causes and fixes: /guides/mysql/mysql-too-many-connections/