MySQL InnoDB Deadlocks: Diagnosis, Root Causes, and Prevention

An InnoDB deadlock is a cycle: transaction A waits for a lock held by transaction B while B waits, directly or indirectly, for A. InnoDB detects the cycle, chooses one transaction to roll back, and returns error 1213 (ER_LOCK_DEADLOCK) to that client. Deadlocks are expected in a transactional system; repeated patterns are evidence to improve SQL, lock order, indexing, or transaction boundaries.

Read the evidence, not only the victim query

SHOW ENGINE INNODB STATUS;

Find LATEST DETECTED DEADLOCK. For each transaction, record:

• the SQL statement and transaction age;
• the table and index named in each held and requested lock;
• the lock mode, record or gap information, and key values where available;
• which transaction InnoDB rolled back;
• the application endpoint or job that opened each transaction.

The statement shown as waiting is often only the second half of the cycle. The earlier statement in the same transaction acquired the lock that completes it. Correlate the trace with application transaction boundaries and request logs before changing the last SQL line.

Fix lock-order inversion

-- Path A used to update orders, then shipments.
-- Path B used to update shipments, then orders.
-- Make both paths lock in the same documented order.
BEGIN;
SELECT id FROM orders WHERE id = 100 FOR UPDATE;
SELECT order_id FROM shipments WHERE order_id = 100 FOR UPDATE;
UPDATE orders SET status = 'shipped' WHERE id = 100;
UPDATE shipments SET tracking = '1Z...' WHERE order_id = 100;
COMMIT;

Ordering must include multiple rows as well as tables. When a transaction locks a set of IDs, sort the IDs consistently before issuing locking statements. Keep transactions short, avoid user interaction or remote API calls while locks are held, and split unrelated work when atomicity does not require one transaction.

Index the predicate actually being locked

CREATE TABLE jobs (
  id BIGINT PRIMARY KEY,
  status VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL
);

-- Without a supporting index, this can examine and lock many index records.
UPDATE jobs
SET status = 'done'
WHERE status = 'pending'
  AND created_at < NOW();

ALTER TABLE jobs
  ADD INDEX idx_jobs_status_created (status, created_at);

InnoDB locks index records it scans. A selective index that supports the predicate can reduce the lock footprint and transaction time, but adding an index does not guarantee that no deadlock is possible. Confirm the chosen execution plan, data distribution, affected-row count, and concurrent write paths.

Gap locks and isolation level

Under the default REPEATABLE READ isolation level, range searches can use next-key locks, which combine record and gap locking. READ COMMITTED disables gap locking for searches and index scans except where MySQL needs it for foreign-key and duplicate-key checks. It also changes read semantics: each consistent read receives a fresh snapshot. Do not change isolation merely to suppress a symptom; test application invariants and all affected query plans.

Queue consumers with SKIP LOCKED

BEGIN;

SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

UPDATE jobs
SET status = 'running'
WHERE id = ?;

COMMIT;

With a suitable index, concurrent workers can skip a row another worker already holds. This is appropriate for queue-like access where temporarily missing a locked row is expected. The result is an inconsistent view of the data and is unsuitable for general transactional reporting. MySQL also marks statements using SKIP LOCKED as unsafe for statement-based replication.

An ordinary SELECT ... FOR UPDATE that waits is not itself proof of a deadlock. A deadlock requires a cycle. Long waits may instead indicate an oversized transaction, slow dependency, hot row, or missing index.

Log enough, then turn verbose logging back off

SET GLOBAL innodb_print_all_deadlocks = ON;

-- After the investigation window:
SET GLOBAL innodb_print_all_deadlocks = OFF;

When enabled, every detected deadlock is written to the MySQL error log. Restrict log access because SQL text and values may be sensitive, and account for log volume. SHOW ENGINE INNODB STATUS retains only the latest deadlock. The default innodb_deadlock_detect=ON is normally appropriate; disabling detection makes transactions rely on lock-wait timeout and should be considered only for a measured extreme-contention workload with a tested timeout and retry design.

Retry the whole transaction safely

import random
import time
import pymysql

def run_transaction(connect, operation, max_attempts=5):
    for attempt in range(max_attempts):
        connection = connect()
        try:
            connection.begin()
            result = operation(connection)
            connection.commit()
            return result
        except pymysql.err.OperationalError as error:
            connection.rollback()
            is_deadlock = error.args and error.args[0] == 1213
            if not is_deadlock or attempt + 1 == max_attempts:
                raise
            delay = min(0.5, 0.02 * (2 ** attempt))
            time.sleep(random.uniform(0, delay))
        except Exception:
            connection.rollback()
            raise
        finally:
            connection.close()

    raise RuntimeError("transaction retry limit exhausted")

Re-run every statement in the transaction because InnoDB rolled back the victim transaction. Keep the operation idempotent, or use a durable idempotency key, before retrying work that can trigger email, payment, queue publication, or another external side effect. Bound retries so persistent contention becomes a visible error rather than an infinite loop.

Error 1205 (ER_LOCK_WAIT_TIMEOUT) is different. With the default innodb_rollback_on_timeout=OFF, a lock-wait timeout rolls back the current statement, not the whole transaction. An application may still choose to roll back and retry the whole unit, but it must do so explicitly and according to its consistency contract.

Monitor patterns and user impact

SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';

Sample the cumulative counter and calculate a rate. Tag application error 1213 by endpoint or transaction type, and measure retry success, attempts, added latency, and exhausted retries. There is no universal acceptable deadlock rate: a low rate in a payment path can matter more than a higher rate in a cheap idempotent queue. Alert from service objectives and observed baselines.

Prevention checklist

• Acquire tables and row IDs in a consistent order across every code path.
• Keep transactions short and exclude remote calls from locked sections.
• Index selective update, delete, and locking-read predicates.
• Update only the rows and columns required by the transaction.
• Use SKIP LOCKED only for queue semantics and compatible replication.
• Capture full traces during an investigation and protect sensitive logs.
• Retry complete transactions with bounds, jitter, and idempotency.

Official primary sources

• MySQL 8.4 InnoDB deadlocks
• Deadlock minimization and handling
• InnoDB locking reads and SKIP LOCKED
• InnoDB transaction isolation levels
• InnoDB system variables

Working with JusDB on MySQL locking

JusDB helps teams map deadlock traces to application transactions, correct lock order and indexing, and validate bounded retry behavior under production-shaped concurrency.

Explore JusDB MySQL services →  |  Talk to a DBA