How Do I Diagnose and Resolve MySQL Deadlocks?

How do I diagnose and resolve MySQL deadlocks?

TL;DR

Constraints

Quick Reference

# Cause Likelihood Signature Fix
1 Inconsistent table/row access order ~40% 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 ~25% Lock on many index records in INNODB STATUS Add targeted indexes so queries lock fewer rows [src2]
3 Gap lock conflicts on INSERT ~15% lock_mode X locks gap in deadlock output Use READ COMMITTED isolation or unique key inserts [src1, src3]
4 Long-running transactions ~10% Transaction age > seconds in INNODB_TRX Keep transactions short; commit immediately [src2]
5 FOR SHARE to FOR UPDATE escalation ~5% S-lock holder tries to upgrade to X-lock Use FOR UPDATE directly when you plan to write [src4]
6 Bulk operations (large UPDATE/DELETE) ~3% Statement locks large row range Break into smaller batches with LIMIT [src2]
7 Foreign key constraint checks ~2% Child/parent table locks during cascading ops Ensure indexes on foreign key columns [src2]

Decision Tree

START — MySQL deadlock detected (ERROR 1213)
├── Step 1: Capture the deadlock info
│   ├── SHOW ENGINE INNODB STATUS\G → LATEST DETECTED DEADLOCK [src1]
│   ├── Enable innodb_print_all_deadlocks = ON [src1, src6]
│   └── Use pt-deadlock-logger for continuous monitoring [src7]
│
├── Step 2: Identify the conflict pattern
│   ├── Two transactions locking rows in different order?
│   │   └── Fix: Reorder operations consistently [src2]
│   ├── Gap lock or next-key lock conflict?
│   │   └── Fix: Use READ COMMITTED isolation [src1, src3]
│   ├── Large number of locked index records?
│   │   └── Fix: Add better indexes [src2]
│   ├── Long-running transaction holding locks?
│   │   └── Fix: Shorten transactions; commit earlier [src2]
│   └── SELECT ... FOR SHARE escalation?
│       └── Fix: Use FOR UPDATE from the start [src4]
│
├── Step 3: Apply prevention
│   ├── Consistent lock ordering across all code paths [src2]
│   ├── Add indexes on WHERE/JOIN columns of locking queries [src2]
│   ├── Keep transactions short [src2]
│   └── Implement application retry with exponential backoff [src5]
│
└── Step 4: Verify
    ├── Monitor deadlock frequency via error log [src6]
    └── If near-zero → done; if not → iterate Step 2

Step-by-Step Guide

1. Check the most recent deadlock

Examine what InnoDB recorded about the deadlock. [src1, src6]

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

Look for the LATEST DETECTED DEADLOCK section showing Transaction 1 and Transaction 2 locks held/waiting, and which was rolled back.

Verify: The output should contain a LATEST DETECTED DEADLOCK section.

2. Enable persistent deadlock logging

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

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

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

Verify: Check MySQL error log for deadlock entries after triggering a known scenario.

3. Identify conflicting queries and lock types

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

-- 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

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

4. Fix the root cause

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

-- If missing index:
CREATE INDEX idx_orders_customer ON orders(customer_id);

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

-- If bulk operation: break into batches
DELETE FROM logs WHERE created_at < '2025-01-01' LIMIT 1000;
-- Repeat until 0 rows affected

Verify: Monitor deadlock frequency for 24-48 hours via error log.

5. Implement application retry logic

Mandatory regardless of prevention efforts. [src2, src5]

import mysql.connector, time

def execute_with_retry(conn, statements, max_retries=3):
    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:
            conn.rollback()
            if e.errno == 1213 and attempt < max_retries - 1:
                time.sleep(0.1 * (2 ** attempt))
                continue
            raise

Verify: Test by triggering a deadlock in staging and confirming retry succeeds.

Code Examples

Python: deadlock-safe transaction with retry

# Input:  MySQL connection, list of (query, params) tuples
# Output: True on success, raises on non-deadlock error

import mysql.connector
import time
import logging

logger = logging.getLogger(__name__)
DEADLOCK_ERROR = 1213
LOCK_WAIT_TIMEOUT = 1205

def deadlock_retry(conn, operations, max_retries=3, base_delay=0.1):
    """Execute transaction with automatic deadlock retry."""
    for attempt in range(max_retries):
        try:
            cursor = conn.cursor()
            cursor.execute("START TRANSACTION")
            for sql, params in operations:
                cursor.execute(sql, params)
            conn.commit()
            cursor.close()
            return True
        except mysql.connector.Error as err:
            conn.rollback()
            if err.errno in (DEADLOCK_ERROR, LOCK_WAIT_TIMEOUT):
                if attempt < max_retries - 1:
                    delay = base_delay * (2 ** attempt)
                    logger.warning(f"Deadlock attempt {attempt+1}, retry in {delay}s")
                    time.sleep(delay)
                    continue
            raise
    raise RuntimeError("Deadlock retries exhausted")

Java: deadlock retry with Spring

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

@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

-- Input:  MySQL 8.0+ with performance_schema
-- Output: Current lock contention snapshot

-- 1. Show most recent deadlock
SHOW ENGINE INNODB STATUS\G

-- 2. Currently blocked transactions
SELECT
    r.trx_id AS blocked_trx,
    r.trx_mysql_thread_id AS blocked_thread,
    r.trx_query AS blocked_query,
    b.trx_id AS blocking_trx,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.INNODB_TRX r
JOIN performance_schema.data_lock_waits w
    ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.INNODB_TRX b
    ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID;

-- 3. All current locks
SELECT ENGINE_TRANSACTION_ID AS trx_id,
       OBJECT_NAME AS tbl, INDEX_NAME AS idx,
       LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks
ORDER BY ENGINE_TRANSACTION_ID, LOCK_STATUS;

Anti-Patterns

Wrong: Ignoring deadlock errors in application code

# 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

Correct: Always catch error 1213 and retry

# 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

-- BAD — opposite order causes circular wait [src2, src4]
-- Transaction A:
UPDATE orders SET status = 'shipped' WHERE id = 100;
UPDATE inventory SET qty = qty - 1 WHERE product_id = 5;

-- Transaction B (concurrent):
UPDATE inventory SET qty = qty + 1 WHERE product_id = 5;
UPDATE orders SET status = 'cancelled' WHERE id = 100;
-- DEADLOCK: circular wait

Correct: Always access tables in the same order

-- GOOD — both access inventory THEN orders [src2, src4]
-- Transaction A:
UPDATE inventory SET qty = qty - 1 WHERE product_id = 5;
UPDATE orders SET status = 'shipped' WHERE id = 100;

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

Wrong: Large UPDATE without index

-- BAD — full table scan locks every row [src2]
UPDATE orders SET processed = 1 WHERE status = 'pending';
-- Without index on status, InnoDB locks ALL rows

Correct: Add index and batch the operation

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

-- 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

Diagnostic Commands

-- View last deadlock
SHOW ENGINE INNODB STATUS\G

-- Enable persistent deadlock logging
SET GLOBAL innodb_print_all_deadlocks = ON;
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

-- Check lock wait timeout
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- Check deadlock detection status
SHOW VARIABLES LIKE 'innodb_deadlock_detect';

-- Currently waiting transactions
SELECT trx_id, trx_state, trx_started, trx_wait_started,
       trx_mysql_thread_id, trx_query
FROM information_schema.INNODB_TRX WHERE trx_state = 'LOCK WAIT';

-- Current locks (MySQL 8.0+)
SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME,
       LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks;

-- Lock waits (MySQL 8.0+)
SELECT * FROM performance_schema.data_lock_waits;

-- Deadlock count (MySQL 8.0+)
SELECT COUNT FROM information_schema.INNODB_METRICS
WHERE NAME = 'lock_deadlocks';

-- Percona Toolkit: continuous deadlock logger
-- pt-deadlock-logger --user=root --password=xxx h=localhost

-- Kill a blocking thread if necessary
-- KILL <thread_id>;

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]
pt-deadlock-logger Percona Toolkit 2.x+ Continuous deadlock monitoring 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 queries
Two transactions block each other cyclically Single query is slow Query optimization (EXPLAIN, indexes)
LATEST DETECTED DEADLOCK 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

Important Caveats