All technical articles

Fix MySQL InnoDB Deadlocks and Lock Wait Timeouts

Diagnose InnoDB deadlocks and lock waits with SHOW ENGINE, Performance Schema, consistent lock ordering, proper indexes, short transactions, and safe retries.

Fix MySQL InnoDB Deadlocks and Lock Wait Timeouts

A deadlock occurs when transactions hold resources in conflicting order and form a cycle. InnoDB resolves it by rolling one transaction back. A lock wait timeout is different: a transaction waits too long for another lock. Locks are necessary for consistency; the goal is to reduce their duration and scope and make acquisition order predictable.

Capture the latest InnoDB deadlock

Read LATEST DETECTED DEADLOCK as soon as possible. Record the statements, transaction IDs, lock modes, and index names. Enable deadlock logging when you need history beyond the single latest event.

SHOW ENGINE INNODB STATUS\G
SET GLOBAL innodb_print_all_deadlocks = ON;
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

Map waiters to blockers

Modern MySQL exposes lock relationships through Performance Schema. Identify the blocking transaction and its age before killing anything. An automatic kill without diagnosis may roll back important work and the same pattern will return.

SELECT * FROM performance_schema.data_lock_waits\G
SELECT * FROM performance_schema.data_locks\G
SELECT trx_id, trx_started, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

Keep transactions short

Do not perform remote API calls, file processing, user interaction, or sleeps inside a database transaction. Prepare data first, open the transaction, apply the smallest atomic change, and commit promptly.

START TRANSACTION;
SELECT id FROM orders WHERE id = 42 FOR UPDATE;
UPDATE orders SET status = 'paid' WHERE id = 42;
COMMIT;

Use a consistent lock order

If one code path locks orders before inventory, every competing path should use the same order. When locking multiple rows, sort identifiers first. Consistent ordering removes many wait cycles by design.

Use indexes to reduce lock scope

An UPDATE or SELECT FOR UPDATE with a poor index can scan and lock far more records and gaps than intended. Use EXPLAIN to verify a selective index supports the filter and order. Add indexes from observed queries, not by guesswork.

EXPLAIN SELECT id FROM jobs
WHERE queue = 'email' AND status = 'ready'
ORDER BY id LIMIT 1 FOR UPDATE;

CREATE INDEX idx_jobs_queue_status_id
ON jobs(queue, status, id);

Retry deadlock victims safely

Deadlocks are expected exceptions in concurrent systems. Retry the full transaction with a short randomized backoff and a strict attempt limit. Make the operation idempotent; payments, emails, and webhooks need unique keys so retries do not duplicate effects.

Treat isolation-level changes carefully

REPEATABLE READ uses next-key and gap locks to prevent phantoms. READ COMMITTED may reduce some gap locking but changes read semantics. Test application behavior and workload before changing it globally.

InnoDB lock checklist

  • Save SHOW ENGINE INNODB STATUS output.
  • Identify waiters and blockers in Performance Schema.
  • Remove external I/O from transactions.
  • Use one lock order across code paths.
  • Review indexes for locking queries.
  • Add bounded retry, backoff, and idempotency.

Frequently asked questions

Will increasing innodb_lock_wait_timeout fix it?

Usually not. It only permits longer waits; the blocker, transaction design, or missing index still needs correction.

Does a deadlock mean InnoDB is broken?

No. Occasional deadlocks are normal under concurrency. A high rate indicates inconsistent lock order or oversized transactions.

Should I always kill the blocking session?

Only during an incident after checking the impact. The durable fix belongs in transaction and query design.

Related guides