MySQL / Concurrency, performance, and operations / Lesson 13

Locking, waits, and deadlock recovery

Reason about concurrent lock ownership and recover a complete business operation within bounded retry limits.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Differentiate row, gap, next-key, and metadata locking.
Diagnose waits and design safe deadlock retries.

Locks belong to transactions

Use two sessions connected only to a disposable copy of the shop fixture. InnoDB locking reads such as SELECT ... FOR UPDATE require an explicit transaction to protect a later operation. A matching unique-key lookup can lock the found record, while range searches under REPEATABLE READ can take next-key locks covering records and gaps. The chosen access path therefore affects both performance and concurrency.

Observe an ordinary blocking wait

A wait is not automatically a deadlock. One transaction can hold a row while another waits without a cycle. Keep the holder short and know how it will finish. Metadata locks protect table definitions separately from InnoDB row locks; a long transaction can delay DDL even after its SELECT has finished. Increasing a timeout does not resolve the underlying ownership or shorten the blocking transaction.

sql Immediate failure rather than waiting

-- Session A
START TRANSACTION;
SELECT id,stock FROM products WHERE id=1 FOR UPDATE;
-- Session B, while A remains open
START TRANSACTION;
SELECT id,stock FROM products WHERE id=1 FOR UPDATE NOWAIT;
ROLLBACK;
-- Session A
ROLLBACK;

Expected output

Session A: id=1, stock=20
Session B: ERROR 3572 (HY000): Do not wait for lock.

A reads 1/20 and holds its record lock. B fails with ERROR 3572 (HY000): Do not wait for lock. Its explicit ROLLBACK abandons the attempt; A then releases its lock. NOWAIT only changes row-lock waiting behavior and is not a universal nonblocking guarantee for every lock type.

A deadlock needs a cycle

If A owns product 1 and requests product 2 while B owns product 2 and requests product 1, neither can proceed. InnoDB normally detects the cycle and rolls back a victim transaction. Victim selection is not a business priority guarantee. Applications must tolerate victimhood, roll back abandoned attempts explicitly, and retry the complete transaction only when external effects and request identity make repetition safe.

sql Read current waits and the latest deadlock evidence

SELECT REQUESTING_ENGINE_TRANSACTION_ID,
       BLOCKING_ENGINE_TRANSACTION_ID
FROM performance_schema.data_lock_waits;
SHOW ENGINE INNODB STATUS;

The wait table is a live snapshot and may be empty after a deadlock has already been resolved. InnoDB status can include the latest detected cycle, statements, and victim details. These diagnostics require appropriate privileges and may expose SQL text, so capture only necessary evidence through an authorized operational channel.

Bound retries and preserve identity

Retry deadlock victims with bounded exponential backoff and jitter, respecting a request deadline and attempt cap. Re-read state within each new transaction rather than reusing stale decisions. A lock wait timeout usually rolls back the statement by default, unlike a deadlock victim rollback; explicitly abandon the whole failed business attempt. Persist an idempotency key so an uncertain COMMIT or external action cannot create duplicate orders.

Reduce contention without losing correctness

Acquire shared resources in a consistent global order, index targeted predicates, and keep transactions short. SKIP LOCKED can help queue workers claim currently available rows, but it deliberately omits locked rows and gives an incomplete view; do not use it for a complete inventory or financial report. Lower isolation may reduce some gap locking but changes correctness assumptions and must be assessed against actual invariants.

Common errors and fixes

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction.

Why it happens: Concurrent transactions acquired overlapping resources in a cycle.
How to fix it: Retry the complete idempotent unit within a bounded policy after cleanup.
Prevent it next time: Use consistent lock order and inspect repeated cycle patterns.

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.

Why it happens: A lock request outlasted its timeout, possibly without any deadlock.
How to fix it: Roll back the abandoned business attempt and diagnose its blocker.
Prevent it next time: Monitor long transactions and enforce transaction lifetime budgets.

SKIP LOCKED produces an apparently missing product.

Why it happens: A concurrent holder caused the row to be intentionally skipped.
How to fix it: Use an appropriate complete read when absence must be authoritative.
Prevent it next time: Restrict skip-locked semantics to workflows that tolerate omissions.

Check your understanding

1. Both transactions need products 1 and 2. Propose a common acquisition order.

Show worked solution

Have every workflow acquire product 1 before product 2, or sort arbitrary requested ids ascending before locking. This removes the specific opposite-order cycle, though other indexes, constraints, and resources can still introduce deadlocks; bounded retries remain necessary.

2. Why can an empty data_lock_waits table follow ERROR 1213?