---
# === IDENTITY ===
id: software/debugging/mysql-deadlocks/2026
canonical_question: "How do I diagnose and resolve MySQL deadlocks?"
aliases:
  - "MySQL deadlock"
  - "InnoDB deadlock"
  - "SHOW ENGINE INNODB STATUS deadlock"
  - "MySQL deadlock detection"
  - "MySQL error 1213 deadlock"
  - "MySQL lock wait timeout"
  - "innodb_print_all_deadlocks"
  - "MySQL transaction deadlock retry"
entity_type: software_reference
domain: software > debugging > mysql_deadlocks
region: global
jurisdiction: global
temporal_scope: 2010-2026

# === VERIFICATION ===
last_verified: 2026-05-17
confidence: 0.93
version: 1.1
first_published: 2026-02-20

# === TEMPORAL VALIDITY ===
temporal_validity:
  status: stable
  last_breaking_change: "MySQL 9.6.0 (2026-01-20) — fixed deadlock between FLUSH TABLE FOR EXPORT and concurrent DROP TABLE/DML"
  next_review: 2026-11-13
  change_sensitivity: low

# === CONSTRAINTS ===
constraints:
  - "InnoDB storage engine only — MyISAM and other engines use table-level locking and do not produce row-level deadlocks detectable by InnoDB's wait-for graph"
  - "SHOW ENGINE INNODB STATUS requires the PROCESS privilege — unprivileged users cannot view deadlock output"
  - "performance_schema.data_locks and data_lock_waits require MySQL 8.0+ — on MySQL 5.7, use information_schema.INNODB_LOCKS and INNODB_LOCK_WAITS instead"
  - "Never kill blocking transactions blindly with KILL — InnoDB automatically resolves deadlocks by rolling back the victim; manual KILL risks partial commits and data inconsistency"
  - "Disable innodb_print_all_deadlocks after debugging — leaving it ON permanently causes error log bloat on high-concurrency systems"

# === SKIP CONDITIONS ===
skip_this_unit_if:
  - condition: "Database is PostgreSQL, not MySQL"
    use_instead: "PostgreSQL has different deadlock detection (pg_locks, pg_stat_activity) and different resolution commands"
  - condition: "Error is 1205 (lock wait timeout exceeded), not 1213 (deadlock found)"
    use_instead: "Lock wait timeout is a different problem — check innodb_lock_wait_timeout setting and identify long-running transactions holding locks"
  - condition: "Problem is an application-level race condition (e.g., double-submit, TOCTOU) rather than a database deadlock"
    use_instead: "Application concurrency patterns — use idempotency keys, optimistic locking, or mutex/semaphore at the application layer"

# === AGENT HINTS ===
inputs_needed:
  - key: "mysql_version"
    question: "Which MySQL version are you running (5.7, 8.0, 8.4, 9.x)?"
    type: choice
    options: ["5.7", "8.0", "8.4", "9.x"]
  - key: "deadlock_frequency"
    question: "How often are deadlocks occurring?"
    type: choice
    options: ["rare (< 1/day)", "occasional (1-10/day)", "frequent (> 10/day)", "constant"]

# === DISTRIBUTION ===
canonical_source: "https://knowledgelib.io/software/debugging/mysql-deadlocks/2026"
suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-05-17)"

# === RELATED UNITS ===
related_kos:
  related_to:
    - id: "software/debugging/postgresql-slow-queries/2026"
      label: "PostgreSQL Slow Queries"
    - id: "software/debugging/postgresql-connection-pool/2026"
      label: "PostgreSQL Connection Pool Exhaustion"
    - id: "software/debugging/postgresql-index-bloat/2026"
      label: "PostgreSQL Index Bloat"
    - id: "software/debugging/django-n-plus-1/2026"
      label: "Django N+1 Query Problem"

# === SOURCES (8 authoritative sources) ===
sources:
  - id: src1
    title: "MySQL 8.4 Reference Manual — Deadlocks in InnoDB"
    author: Oracle Corporation
    url: https://dev.mysql.com/doc/refman/8.4/en/innodb-deadlocks.html
    type: official_docs
    published: 2024-04-30
    reliability: authoritative
  - id: src2
    title: "MySQL 8.4 Reference Manual — How to Minimize and Handle Deadlocks"
    author: Oracle Corporation
    url: https://dev.mysql.com/doc/refman/8.4/en/innodb-deadlocks-handling.html
    type: official_docs
    published: 2024-04-30
    reliability: authoritative
  - id: src3
    title: "MySQL 8.4 Reference Manual — Deadlock Detection"
    author: Oracle Corporation
    url: https://dev.mysql.com/doc/refman/8.4/en/innodb-deadlock-detection.html
    type: official_docs
    published: 2024-04-30
    reliability: authoritative
  - id: src4
    title: "MySQL 8.0 Reference Manual — An InnoDB Deadlock Example"
    author: Oracle Corporation
    url: https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlock-example.html
    type: official_docs
    published: 2024-01-01
    reliability: authoritative
  - id: src5
    title: "How to Deal with and Resolve MySQL Deadlocks"
    author: Percona
    url: https://www.percona.com/blog/how-to-deal-with-mysql-deadlocks/
    type: technical_blog
    published: 2023-06-15
    reliability: high
  - id: src6
    title: "Understanding and Resolving MySQL Deadlocks with the InnoDB Engine"
    author: Virtual DBA
    url: https://virtual-dba.com/blog/understanding-mysql-deadlocks-with-innodb-engine/
    type: technical_blog
    published: 2024-03-01
    reliability: high
  - id: src7
    title: "Percona Toolkit — pt-deadlock-logger"
    author: Percona
    url: https://docs.percona.com/percona-toolkit/pt-deadlock-logger.html
    type: official_docs
    published: 2024-01-01
    reliability: high
  - id: src8
    title: "MySQL 9.7 Reference Manual — Changes in MySQL 9.6.0 (2026-01-20)"
    author: Oracle Corporation
    url: https://dev.mysql.com/doc/relnotes/mysql/9.7/en/news-9-6-0.html
    type: official_docs
    published: 2026-01-20
    reliability: authoritative
---

# How Do I Diagnose and Resolve MySQL Deadlocks?

## TL;DR

- **Bottom line**: MySQL/InnoDB deadlocks occur when two or more transactions hold locks that the other needs, creating a circular wait. InnoDB automatically detects deadlocks and rolls back the smallest transaction (the "victim"). Diagnosis uses `SHOW ENGINE INNODB STATUS` to read the `LATEST DETECTED DEADLOCK` section. Prevention follows three rules: keep transactions short, access tables/rows in consistent order, and add indexes to reduce lock scope. [src1, src2]
- **Key tool/command**: `SHOW ENGINE INNODB STATUS\G` — displays the most recent deadlock with full lock details, transaction IDs, and which transaction was rolled back. [src1, src6]
- **Watch out for**: Deadlocks are normal in any RDBMS and are NOT bugs — your application MUST catch MySQL error 1213 and retry the transaction. Trying to eliminate all deadlocks is futile; the goal is to minimize frequency and handle them gracefully. [src2, src5]
- **Works with**: MySQL 5.7+, 8.0, 8.4, 9.x. InnoDB only (MyISAM uses table-level locks and does not deadlock the same way). [src1]

## Constraints
<!-- Agents: read this section before recommending any action from this unit.
     These are hard constraints that must not be violated. -->

- **InnoDB only**: Deadlock detection is an InnoDB feature. MyISAM and other storage engines use table-level locking and do not have row-level deadlocks. [src1]
- **Never ignore error 1213**: Applications MUST catch `ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction` and retry. Treating it as a fatal error is incorrect. [src2, src5]
- **Do not disable deadlock detection without understanding the trade-off**: Setting `innodb_deadlock_detect = OFF` improves throughput on extremely high-concurrency systems but means deadlocks are only resolved by `innodb_lock_wait_timeout` (default 50s), causing long waits. [src3]
- **SHOW ENGINE INNODB STATUS shows only the LAST deadlock**: It does not keep a history. Enable `innodb_print_all_deadlocks = ON` for persistent logging, but disable it after debugging to avoid error log bloat. [src1, src6]
- **LOCK TABLES deadlocks are NOT detected by InnoDB**: Deadlocks involving `LOCK TABLES` statements or locks from non-InnoDB engines cannot be detected by InnoDB's wait-for graph. Use `innodb_lock_wait_timeout` as fallback. [src3]

## Quick Reference

| # | Cause | Likelihood | Signature | Fix |
|---|---|---|---|---|
| 1 | Inconsistent table/row access order | ~40% of cases | Two transactions lock different rows/tables in opposite order | Access tables and rows in the same order across all transactions [src2] |
| 2 | Missing or poor indexes causing full table scans | ~25% of cases | `SHOW ENGINE INNODB STATUS` shows lock on many index records | Add targeted indexes so queries lock fewer rows [src2] |
| 3 | Gap lock conflicts on INSERT | ~15% of cases | `lock_mode X locks gap` in deadlock output | Reduce gap locks by using `READ COMMITTED` isolation or unique key inserts [src1, src3] |
| 4 | Long-running transactions holding locks | ~10% of cases | Transaction age > seconds in `INNODB_TRX` | Keep transactions short; commit immediately after changes [src2] |
| 5 | SELECT ... FOR UPDATE / FOR SHARE escalation | ~5% of cases | S-lock holder tries to upgrade to X-lock while another waits | Use `FOR UPDATE` directly instead of `FOR SHARE` when you plan to write [src4] |
| 6 | Bulk operations (large UPDATE/DELETE) | ~3% of cases | Statement locks large row range, overlaps with concurrent DML | Break into smaller batches with LIMIT; commit between batches [src2] |
| 7 | Foreign key constraint checks | ~2% of cases | Child/parent table locks during cascading operations | Ensure indexes on foreign key columns [src2] |

## Decision Tree

> Full script: [decision-tree.txt](scripts/decision-tree.txt) (27 lines)

```
START — MySQL deadlock detected (ERROR 1213)
├── Step 1: Capture the deadlock info
│   ├── SHOW ENGINE INNODB STATUS\G → read LATEST DETECTED DEADLOCK section [src1]
│   ├── Enable innodb_print_all_deadlocks = ON for persistent logging [src1, src6]
│   └── Use pt-deadlock-logger for continuous monitoring [src7]
# ... (see full script)
```

## Step-by-Step Guide

### 1. Check the most recent deadlock

The first diagnostic step is to examine what InnoDB recorded about the deadlock. [src1, src6]

```sql
-- View the most recent deadlock details
SHOW ENGINE INNODB STATUS\G
```

Look for the `LATEST DETECTED DEADLOCK` section. It shows:
- **Transaction 1**: Which locks it held and which lock it was waiting for
- **Transaction 2**: Which locks it held and which lock it was waiting for
- **WE ROLL BACK TRANSACTION (N)**: Which transaction InnoDB chose as victim

**Verify**: The output should contain a `LATEST DETECTED DEADLOCK` section. If it says "no deadlock detected", the deadlock info was overwritten or none occurred since last restart.

### 2. Enable persistent deadlock logging

`SHOW ENGINE INNODB STATUS` only shows the last deadlock. Enable full logging for continuous capture. [src1, src6]

```sql
-- Log ALL deadlocks to the MySQL error log
SET GLOBAL innodb_print_all_deadlocks = ON;

-- Or add to my.cnf for persistence across restarts:
-- [mysqld]
-- innodb_print_all_deadlocks = 1
```

**Verify**: Trigger a known deadlock scenario and check the MySQL error log (`mysqld.err` or `error.log`) for the deadlock entry.

### 3. Identify conflicting queries and lock types

Parse the deadlock output to understand exactly which rows and indexes are involved. [src1, src5]

```sql
-- Check currently waiting transactions
SELECT * FROM information_schema.INNODB_TRX
WHERE trx_state = 'LOCK WAIT'\G

-- Check current locks (MySQL 8.0+)
SELECT * FROM performance_schema.data_locks\G

-- Check lock waits (MySQL 8.0+)
SELECT * FROM performance_schema.data_lock_waits\G

-- For MySQL 5.7:
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
SELECT * FROM information_schema.INNODB_LOCKS;
```

**Verify**: You should see the specific index records and lock types (S, X, gap, insert intention) involved.

### 4. Fix the root cause based on the pattern

Apply the appropriate fix based on the pattern identified in Step 2. [src2, src5]

```sql
-- If missing index: add index on columns used in WHERE/JOIN
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- If gap lock issue: switch to READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- If bulk operation: break into batches
-- Instead of: DELETE FROM logs WHERE created_at < '2025-01-01';
-- Use:
DELETE FROM logs WHERE created_at < '2025-01-01' LIMIT 1000;
-- Repeat in a loop until 0 rows affected
```

**Verify**: Monitor deadlock frequency for 24-48 hours. Check error log for new deadlock entries.

### 5. Implement application retry logic

This is mandatory regardless of prevention efforts. Deadlocks can always occur. [src2, src5]

```python
import mysql.connector
import time

def execute_with_retry(conn, statements, max_retries=3):
    """Execute transaction with deadlock retry logic."""
    for attempt in range(max_retries):
        try:
            cursor = conn.cursor()
            cursor.execute("START TRANSACTION")
            for stmt, params in statements:
                cursor.execute(stmt, params)
            conn.commit()
            return True
        except mysql.connector.Error as e:
            if e.errno == 1213 and attempt < max_retries - 1:  # Deadlock
                conn.rollback()
                time.sleep(0.1 * (2 ** attempt))  # Exponential backoff
                continue
            raise
    return False
```

**Verify**: Test by intentionally triggering a deadlock in a staging environment and confirming the retry succeeds.

## Code Examples

### Python: deadlock-safe transaction with retry

> Full script: [python-deadlock-safe-transaction-with-retry.py](scripts/python-deadlock-safe-transaction-with-retry.py) (35 lines)

```python
# Input:  MySQL connection, list of (query, params) tuples
# Output: True on success, raises on non-deadlock error
import mysql.connector
import time
import logging
# ... (see full script)
```

### Java: deadlock retry with Spring

```java
// Input:  Runnable transaction logic
// Output: Completes transaction or throws after max retries

import org.springframework.dao.DeadlockLoserDataAccessException;
import org.springframework.retry.annotation.Backoff;
import org.springframework.retry.annotation.Retryable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class OrderService {

    @Retryable(
        retryFor = DeadlockLoserDataAccessException.class,
        maxAttempts = 3,
        backoff = @Backoff(delay = 100, multiplier = 2)
    )
    @Transactional
    public void transferFunds(long fromId, long toId, double amount) {
        // Always lock in consistent order (lower ID first)
        long first = Math.min(fromId, toId);
        long second = Math.max(fromId, toId);

        accountRepo.debit(first == fromId ? first : second, amount);
        accountRepo.credit(first == toId ? first : second, amount);
    }
}
```

### SQL: deadlock diagnostic report

> Full script: [sql-deadlock-diagnostic-report.sql](scripts/sql-deadlock-diagnostic-report.sql) (29 lines)

```sql
-- Input:  MySQL 8.0+ with performance_schema enabled
-- Output: Current lock contention snapshot
-- 1. Show most recent deadlock
SHOW ENGINE INNODB STATUS\G
-- 2. Currently blocked transactions
# ... (see full script)
```

## Anti-Patterns

### Wrong: Ignoring deadlock errors in application code

```python
# BAD — deadlock error crashes the application [src2, src5]
cursor.execute("START TRANSACTION")
cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
conn.commit()
# If ERROR 1213 occurs, the app crashes with an unhandled exception
# The transaction is lost and the user sees an error page
```

### Correct: Always catch error 1213 and retry

```python
# GOOD — retry on deadlock with exponential backoff [src2, src5]
for attempt in range(3):
    try:
        cursor.execute("START TRANSACTION")
        cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
        cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
        conn.commit()
        break
    except mysql.connector.Error as e:
        conn.rollback()
        if e.errno == 1213 and attempt < 2:
            time.sleep(0.1 * (2 ** attempt))
            continue
        raise
```

### Wrong: Accessing tables in inconsistent order

```sql
-- BAD — Transaction A and B access tables in opposite order [src2, src4]
-- Transaction A:
START TRANSACTION;
UPDATE orders SET status = 'shipped' WHERE id = 100;    -- locks orders row
UPDATE inventory SET qty = qty - 1 WHERE product_id = 5; -- waits for inventory

-- Transaction B (concurrent):
START TRANSACTION;
UPDATE inventory SET qty = qty + 1 WHERE product_id = 5; -- locks inventory row
UPDATE orders SET status = 'cancelled' WHERE id = 100;   -- waits for orders
-- DEADLOCK: circular wait
```

### Correct: Always access tables in the same order

```sql
-- GOOD — Both transactions access inventory THEN orders [src2, src4]
-- Transaction A:
START TRANSACTION;
UPDATE inventory SET qty = qty - 1 WHERE product_id = 5;
UPDATE orders SET status = 'shipped' WHERE id = 100;
COMMIT;

-- Transaction B:
START TRANSACTION;
UPDATE inventory SET qty = qty + 1 WHERE product_id = 5;
UPDATE orders SET status = 'cancelled' WHERE id = 100;
COMMIT;
-- No deadlock: both wait in the same direction
```

### Wrong: Large UPDATE without index

```sql
-- BAD — full table scan locks every row in the table [src2]
UPDATE orders SET processed = 1 WHERE status = 'pending';
-- If no index on `status`, InnoDB locks ALL rows (full scan)
-- Any concurrent DML on orders will wait or deadlock
```

### Correct: Add index and batch the operation

```sql
-- GOOD — targeted index locks only matching rows [src2]
CREATE INDEX idx_orders_status ON orders(status);

-- And break large updates into batches:
UPDATE orders SET processed = 1 WHERE status = 'pending' LIMIT 500;
-- Repeat until 0 rows affected; commit between iterations
```

## Common Pitfalls

- **Assuming deadlocks are bugs**: Deadlocks are a normal consequence of concurrent access in any RDBMS. InnoDB handles them automatically by rolling back one transaction. The application must retry. [src2, src5]
- **Not logging deadlocks persistently**: `SHOW ENGINE INNODB STATUS` only retains the last deadlock. If two deadlocks occur in quick succession, the first is lost. Enable `innodb_print_all_deadlocks = ON` during investigation. [src1, src6]
- **SELECT ... FOR SHARE when you plan to UPDATE**: Acquiring a shared lock and then trying to upgrade to exclusive creates deadlock risk when another session does the same. Use `SELECT ... FOR UPDATE` from the start. [src4]
- **Disabling deadlock detection without lowering lock_wait_timeout**: Setting `innodb_deadlock_detect = OFF` without also reducing `innodb_lock_wait_timeout` from the 50s default means transactions wait up to 50 seconds before timeout. Set it to 5-10s if disabling detection. [src3]
- **Long-running transactions in interactive sessions**: Leaving a `START TRANSACTION` open while a user fills out a form holds locks for minutes. Keep transactions to milliseconds; do reads outside the transaction. [src2]
- **Blaming the database when the fix is in the application**: Most deadlocks are caused by application-level access patterns (inconsistent ordering, missing retries), not MySQL configuration issues. [src5]

## Diagnostic Commands

> Full script: [diagnostic-commands.sql](scripts/diagnostic-commands.sql) (34 lines)

```sql
-- === View last deadlock ===
SHOW ENGINE INNODB STATUS\G
-- Look for "LATEST DETECTED DEADLOCK" section
-- === Enable persistent deadlock logging ===
SET GLOBAL innodb_print_all_deadlocks = ON;
# ... (see full script)
```

## Version History & Compatibility

| Feature | Available Since | Notes |
|---|---|---|
| InnoDB deadlock detection | MySQL 3.23.x | Core feature since InnoDB introduction [src1] |
| `SHOW ENGINE INNODB STATUS` | MySQL 4.1 | Replaced `SHOW INNODB STATUS` [src1] |
| `innodb_print_all_deadlocks` | MySQL 5.6.2 | Logs all deadlocks to error log [src1] |
| `innodb_deadlock_detect` | MySQL 8.0.1 | Allows disabling detection for high-concurrency [src3] |
| `performance_schema.data_locks` | MySQL 8.0 | Replaces `INFORMATION_SCHEMA.INNODB_LOCKS` [src3] |
| `performance_schema.data_lock_waits` | MySQL 8.0 | Replaces `INFORMATION_SCHEMA.INNODB_LOCK_WAITS` [src3] |
| Wait-for graph 200-transaction limit | MySQL 5.7+ | Auto-rollback if >200 transactions in wait chain [src3] |
| 1,000,000 lock examination limit | MySQL 5.7+ | Prevents detection from consuming excessive CPU [src3] |
| `pt-deadlock-logger` | Percona Toolkit 2.x+ | Continuous deadlock monitoring and logging tool [src7] |
| `FLUSH TABLE FOR EXPORT` deadlock fix | MySQL 9.6.0 (2026-01-20) | Resolved deadlock between `FLUSH TABLE FOR EXPORT` and concurrent `DROP TABLE` / DML [src8] |

## When to Use / When Not to Use

| Use When | Don't Use When | Use Instead |
|---|---|---|
| ERROR 1213 in application logs | ERROR 1205 (lock wait timeout, not deadlock) | Check `innodb_lock_wait_timeout` and long-running queries |
| Two transactions block each other cyclically | Single query is slow | Query optimization (EXPLAIN, indexes) |
| `LATEST DETECTED DEADLOCK` appears in INNODB STATUS | Table-level locks from MyISAM | Migrate to InnoDB for row-level locking |
| High concurrency on overlapping row sets | Read-only workload | No deadlocks possible with pure reads |
| Gap lock conflicts on inserts | Deadlock on `LOCK TABLES` | InnoDB cannot detect these; use `innodb_lock_wait_timeout` [src3] |

## Important Caveats

- **InnoDB victim selection is based on transaction size**: InnoDB rolls back the transaction that has modified the fewest rows (inserted + updated + deleted), not the one that started later. This may not always be the transaction you want rolled back. [src3]
- **`innodb_deadlock_detect = OFF` is a power-user setting**: Only disable on systems with hundreds of concurrent connections competing for the same rows (e.g., hot-row counters). For most applications, leave it ON. [src3]
- **Gap locks in REPEATABLE READ cause extra deadlocks**: The default isolation level (REPEATABLE READ) uses gap locks to prevent phantom reads. Switching to READ COMMITTED eliminates gap locks but allows phantom reads. Evaluate the trade-off for your workload. [src1, src3]
- **Deadlock information may be truncated**: `SHOW ENGINE INNODB STATUS` output is limited to approximately 1MB. On systems with many concurrent transactions, deadlock information may be truncated. Use `innodb_print_all_deadlocks` for complete records. [src6]
- **Foreign key checks acquire shared locks on parent rows**: `INSERT` into a child table acquires a shared lock on the referenced parent row. If another transaction tries to `UPDATE` or `DELETE` that parent row concurrently, deadlock risk increases. Index foreign key columns to minimize lock scope. [src2]

## Related Units

- [PostgreSQL Slow Queries](/software/debugging/postgresql-slow-queries/2026)
- [PostgreSQL Connection Pool Exhaustion](/software/debugging/postgresql-connection-pool/2026)
- [PostgreSQL Index Bloat](/software/debugging/postgresql-index-bloat/2026)
- [Django N+1 Query Problem](/software/debugging/django-n-plus-1/2026)