Diagnosing and Eliminating MySQL Deadlocks in Production

1. How deadlocks form

1.1 Transaction and lock basics

InnoDB uses row‑level locks; a lock is held until the transaction commits or rolls back.

-- Transaction A locks row id=1
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Transaction B locks row id=2
BEGIN;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
-- Each waits for the other → deadlock

1.2 Necessary conditions

Four conditions must hold for a deadlock:

Mutual exclusion : a resource cannot be shared (e.g., a row can be held by only one transaction).

Hold and wait : a transaction holds one lock while requesting another.

No preemption : locks are released only when the owning transaction ends.

Circular wait : transactions form a waiting cycle.

InnoDB detects deadlocks and rolls back the transaction that holds the fewest rows.

1.3 Lock types and compatibility

InnoDB provides several lock modes:

Shared (S) : SELECT ... LOCK IN SHARE MODE, compatible with other S locks, conflicts with X.

Exclusive (X) : SELECT ... FOR UPDATE, conflicts with both S and X.

Record lock : locks a single index record.

Gap lock : locks the interval between records, prevents phantom reads.

Next‑key lock : combination of record and gap lock; default under REPEATABLE READ.

Intention lock : table‑level marker indicating a transaction will acquire row‑level locks.

Next‑key locks are a frequent source of deadlocks during range scans.

-- Transaction A locks rows with id > 10 (next‑key lock on (10, +∞))
BEGIN;
SELECT * FROM orders WHERE user_id > 100 FOR UPDATE;
-- Transaction B tries to insert id=101 and is blocked by the gap lock
INSERT INTO orders (id, user_id, amount) VALUES (NULL, 101, 100);
-- Deadlock occurs

2. Investigation methods

2.1 Enable deadlock logging

Set innodb_print_all_deadlocks = ON (requires SUPER) to record full deadlock information in the error log.

2.2 Query lock metadata

SELECT t.trx_id, t.trx_state, t.trx_started, t.trx_rows_locked, t.trx_query,
       l.lock_id, l.lock_mode, l.lock_type, l.lock_table, l.lock_index,
       l.lock_data
FROM information_schema.INNODB_TRX t
JOIN information_schema.INNODB_LOCKS l ON t.trx_id = l.lock_trx_id
ORDER BY t.trx_started;

2.3 Use performance_schema

Enable lock instruments and query recent lock‑wait events:

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/lock%';
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%events_transactions%';
SELECT * FROM performance_schema.events_waits_history_long
WHERE event_name LIKE '%lock%'
ORDER BY TIMER_END DESC
LIMIT 20;

2.4 Interpret deadlock log

An example log shows three sections per transaction: the SQL that was running, the locks it holds, the lock it is waiting for, and which transaction MySQL chose to roll back. LOCK WAIT – the transaction is waiting for a lock. HOLDS THE LOCK(S) – locks already owned. WE ROLL BACK TRANSACTION – victim chosen by the deadlock detector.

Lock‑mode details such as lock_mode X locks rec but not gap (record lock) or lock_mode X locks gap before rec (gap lock).

3. Common scenarios and fixes

3.1 Different lock order

Problem: Transaction A locks row 1 then 2, while Transaction B locks 2 then 1, creating a cycle.

Solution: Access rows in a deterministic order (e.g., ascending primary‑key).

# Wrong order
def transfer_funds_wrong(from_id, to_id, amount):
    cursor.execute("SELECT balance FROM accounts WHERE id = %s FOR UPDATE", (from_id,))
    cursor.execute("SELECT balance FROM accounts WHERE id = %s FOR UPDATE", (to_id,))

# Correct order
def transfer_funds_correct(from_id, to_id, amount):
    first, second = sorted([from_id, to_id])
    cursor.execute("SELECT balance FROM accounts WHERE id = %s FOR UPDATE", (first,))
    cursor.execute("SELECT balance FROM accounts WHERE id = %s FOR UPDATE", (second,))

3.2 Gap‑lock conflicts from indexes

Range queries or index scans can lock wide intervals. Mitigations:

Use a covering index so the query touches only the index.

Lower the isolation level from REPEATABLE READ to READ COMMITTED to reduce gap locks.

# Create covering index
CREATE INDEX idx_user_id_covering ON orders(user_id, status, amount);
# Query using the covering index
SELECT status, amount FROM orders WHERE user_id = 100;
# Change isolation level
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

3.3 Master‑slave lag

Replication delay means long‑running transactions on the master hold locks longer, increasing deadlock probability. Check SHOW SLAVE STATUS and monitor Seconds_Behind_Master. Speed up replica apply or add more replicas.

3.4 Large transactions

Processing too many rows in a single transaction expands the lock‑hold window.

Solution: Split the work into smaller batches.

# Bad: single transaction updates 100k rows
def batch_update_wrong(ids):
    for id in ids:
        cursor.execute("UPDATE orders SET status='processed' WHERE id = %s", (id,))

# Good: batch size 500
def batch_update_correct(ids, batch_size=500):
    for i in range(0, len(ids), batch_size):
        batch = ids[i:i+batch_size]
        placeholders = ",".join(["%s"] * len(batch))
        cursor.execute(f"UPDATE orders SET status='processed' WHERE id IN ({placeholders})", batch)
        connection.commit()

4. Application‑level defenses

4.1 Global lock ordering

Maintain a deterministic lock order in the application code.

import threading
LOCK_ORDER = {}
class AccountService:
    def __init__(self, db_connection):
        self.conn = db_connection
    def transfer(self, from_id, to_id, amount):
        first, second = sorted([from_id, to_id])
        with self._get_lock(first):
            with self._get_lock(second):
                self._do_transfer(first, second, amount)
    def _get_lock(self, account_id):
        if account_id not in LOCK_ORDER:
            LOCK_ORDER[account_id] = threading.Lock()
        return LOCK_ORDER[account_id]

4.2 Lock timeout

Adjust innodb_lock_wait_timeout (default 50 s) to a lower value, e.g., 10 s, and handle error 1205 in the client.

SET GLOBAL innodb_lock_wait_timeout = 10;
try:
    cursor.execute("SELECT ... FOR UPDATE")
except OperationalError as e:
    if e.args[0] == 1205:
        # retry logic
        raise RetryableError("Lock timeout, should retry")

4.3 Retry logic

Implement a limited number of retries with exponential back‑off when a lock‑wait timeout occurs.

MAX_RETRIES = 3
RETRY_DELAY = 0.5
def transfer_with_retry(...):
    for attempt in range(MAX_RETRIES):
        try:
            # transaction body
            return True
        except OperationalError as e:
            if e.args[0] == 1205:
                connection.rollback()
                time.sleep(RETRY_DELAY * (attempt + 1))
                continue
            raise
    return False

5. Monitoring and prevention

5.1 Key metrics

Track the following InnoDB counters and set alert thresholds:

Innodb_row_lock_waits – alert if > 100 per minute.

Innodb_row_lock_time_avg – alert if > 500 ms.

Threads_connected – alert if > 70 % of max_connections.

Lock_wait_timeout – any occurrence should trigger an alarm.

5.2 Slow‑query correlation

Long‑running queries often cause deadlocks. Regularly analyze the slow‑query log:

# Show slow‑query settings
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
# Top 20 slow queries by time
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log
# Top 20 by count
mysqldumpslow -s c -t 20 /var/log/mysql/slow.log

6. Troubleshooting checklist

Deadlock error : Check error log, analyze lock graph, locate offending SQL, then reorder statements or narrow lock scope.

Lock wait timeout : Verify innodb_lock_wait_timeout, identify long‑holding transaction, split large transactions.

Frequent deadlocks on a table : Review access patterns, improve indexing, consider lowering isolation level.

Replica lag causing deadlocks : Run SHOW SLAVE STATUS, monitor Seconds_Behind_Master, accelerate replica apply or add replicas.

Batch update deadlocks : Ensure batch processing follows primary‑key order and keeps each batch small.

The essential skill is to export snapshots of INNODB_TRX, INNODB_LOCKS and INNODB_LOCK_WAITS, and to enable innodb_print_all_deadlocks immediately when a deadlock occurs, because post‑mortem log analysis is often more reliable than live debugging.