Deadlocks, Lock Waits, Retry Design, Idempotency, and Concurrency Testing

Learning outcomes

Lock waits and deadlocks are not the same failure. A lock wait means one transaction is blocked until another releases an incompatible lock (or a timeout/cancel intervenes). A deadlock is a cycle: transaction A waits for B while B waits for A. InnoDB normally detects that cycle and rolls back one transaction so progress can resume.

Standalone prerequisite for this lesson

If you opened Lesson 5 directly, run the Lesson 1 setup first and verify parts_inventory contains part IDs 1 and 2. Use separate terminal windows for Sessions A and B and a third diagnostic session if possible. All timeouts in this lesson are session-scoped and intentionally short only to make the disposable experiment observable.

Experiment 1: a lock wait is a line, not a cycle

Reset the part row to a known value. In Session A:

UPDATE parts_inventory SET on_hand=20 WHERE part_id=1; COMMIT;START TRANSACTION;UPDATE parts_inventory SET on_hand=19 WHERE part_id=1;SELECT CONNECTION_ID() AS session_a;-- keep the transaction open

In Session B, use a short session-only timeout so the teaching experiment finishes quickly:

SET SESSION innodb_lock_wait_timeout=3;START TRANSACTION;UPDATE parts_inventory SET on_hand=18 WHERE part_id=1;-- waits, then normally ERROR 1205 (HY000): Lock wait timeout exceededROLLBACK;

While B is waiting, query from Session C:

SELECT REQUESTING_ENGINE_TRANSACTION_ID,REQUESTING_THREAD_ID,       BLOCKING_ENGINE_TRANSACTION_ID,BLOCKING_THREAD_IDFROM performance_schema.data_lock_waits;SELECT wait_started,wait_age,locked_table,locked_index,       waiting_pid,blocking_pid,waiting_query,blocking_queryFROM sys.innodb_lock_waits;

The Performance Schema relation exposes lock IDs/transactions/threads; the sys.innodb_lock_waits view provides a convenient human-oriented summary when the sys schema is installed. After observation, roll back Session A to release the lock.

A lock-wait timeout normally rolls back the waiting statement, not automatically the entire transaction, unless behavior is changed by server configuration such as innodb_rollback_on_timeout. Application code should therefore roll back the unit of work explicitly when it cannot safely continue.

Experiment 2: deterministic two-session deadlock

We need two rows and opposite lock order. Confirm inventory rows 1 and 2 exist. Then follow the timeline exactly.

START TRANSACTION;UPDATE parts_inventory SET on_hand=on_hand-1 WHERE part_id=1;-- after Session B locks part 2:UPDATE parts_inventory SET on_hand=on_hand-1 WHERE part_id=2;-- this waits until B's next statement creates the cycle

START TRANSACTION;UPDATE parts_inventory SET on_hand=on_hand-1 WHERE part_id=2;-- after A is waiting on part 2:UPDATE parts_inventory SET on_hand=on_hand-1 WHERE part_id=1;-- one participant receives ERROR 1213 (40001): Deadlock found ...

Do not assume in advance which session must be the victim. InnoDB chooses a victim using internal criteria intended to resolve the cycle efficiently. The surviving transaction is not automatically committed; explicitly COMMIT or ROLLBACK it after the experiment.

Capture deadlock evidence

Immediately after the deadlock, from a diagnostic session:

SHOW ENGINE INNODB STATUS\GSHOW VARIABLES LIKE 'innodb_deadlock_detect';SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

The LATEST DETECTED DEADLOCK section records participating transactions, statements, locks, and which transaction was rolled back. If deadlocks are frequent in production, innodb_print_all_deadlocks can emit every deadlock to the server error log, but enabling extra diagnostics should be an intentional operational decision because logs have storage/privacy/noise consequences.

Performance Schema lock tables are excellent for live waits, but a detected deadlock can disappear too quickly to capture there because InnoDB breaks the cycle immediately. SHOW ENGINE INNODB STATUS preserves the latest detected deadlock summary.

Repair the cause: consistent lock order and shorter scope

The deadlock happened because A locked 1→2 while B locked 2→1. If every code path that needs both parts locks them in ascending part_id order, one transaction can wait behind another, but they do not create this two-row cycle.

START TRANSACTION;SELECT part_id,on_handFROM parts_inventoryWHERE part_id IN (1,2)ORDER BY part_idFOR UPDATE;UPDATE parts_inventory SET on_hand=on_hand-1 WHERE part_id=1;UPDATE parts_inventory SET on_hand=on_hand-1 WHERE part_id=2;COMMIT;

The supporting primary-key lookup also keeps locking precise. Broader scans can lock more records/ranges and increase the contention surface. Keep user think-time, network calls, and slow external work outside the lock-holding transaction whenever the business invariant allows it.

Retry design: retry the transaction, not a random statement

A deadlock victim’s transaction is rolled back by InnoDB. The application can retry the whole business unit. But replay is safe only if repeating the request cannot duplicate an external side effect or create a second logical operation.

Our schema has a unique request_key. That can act as an idempotency key for “create this work order” when the application assigns one stable key to one logical request.

START TRANSACTION;INSERT INTO work_orders(request_key,customer_id,status,priority)VALUES ('API-REQ-7f31',1,'open',1)ON DUPLICATE KEY UPDATE  work_order_id = LAST_INSERT_ID(work_order_id);SELECT LAST_INSERT_ID() AS work_order_id;COMMIT;

On the first attempt MySQL inserts a row. On a replay with the same request key, the unique constraint identifies the existing logical request; the LAST_INSERT_ID(expr) assignment makes the session return that row’s ID. The application must also ensure the same key is not reused for conflicting payloads—usually by storing/checking a request fingerprint or business parameters.

max_attempts = 3for attempt in 1..max_attempts:    begin_transaction()    try:        apply_entire_idempotent_business_unit(bound_parameters)        commit()        return success    catch mysql_error as e:        rollback()        if e.code not in {1213, 1205}:            raise        if attempt == max_attempts:            raise        sleep_with_jitter_and_backoff(attempt)

Whether error 1205 should be retried depends on your operation, timeout policy, and transaction state. Always roll back explicitly before reusing the connection. Bound attempts; otherwise persistent overload can turn retries into a retry storm.

Contention testing: prove your application contract under concurrency

A useful concurrency test does more than “run many threads.” It defines the invariant and verifies it after intentionally overlapping transactions. For parts inventory, on_hand >= 0 is enforced by a CHECK constraint, but the application may also require exactly one decrement per accepted reservation.

SELECT part_id,part_name,on_handFROM parts_inventoryORDER BY part_id;SELECT request_key,COUNT(*) AS copiesFROM work_ordersWHERE request_key LIKE 'API-REQ-%'GROUP BY request_keyHAVING COUNT(*) <> 1;SELECT COUNT(*) AS open_transactionsFROM information_schema.innodb_trx;

The second query should return no rows if each idempotency key maps to exactly one stored work order. The final query should not reveal forgotten transactions from your test clients after the test ends.

Cleanup and recovery drill

ROLLBACK;UPDATE parts_inventory SET on_hand=20 WHERE part_id=1;UPDATE parts_inventory SET on_hand=15 WHERE part_id=2;COMMIT;SELECT * FROM parts_inventory ORDER BY part_id;

If a client was terminated while holding a transaction, disconnecting the session causes MySQL/InnoDB to roll back its uncommitted transaction. For a real production incident, do not casually kill sessions before identifying the owning application and business operation; capture evidence first where possible.

A practical contention incident runbook

When users report “the database is stuck,” first determine whether the symptom is a lock wait, metadata lock, overloaded server, slow I/O, or another cause. For row-lock contention, capture the waiting and blocking session IDs, transaction age, SQL text where available, locked object/index, and application owner. The data_locks, data_lock_waits, INNODB_TRX, and sys.innodb_lock_waits views provide complementary live evidence.

Do not immediately kill the blocker. A blocking transaction may be performing legitimate work and could be close to commit. Killing it can trigger rollback work and an application retry. Decide using business ownership, transaction age, blast radius, and runbook policy. If you terminate a session, capture enough evidence first to understand why the transaction was left open or why its lock scope was unexpectedly broad.

For deadlocks, the cycle is already resolved by InnoDB when deadlock detection is enabled. Your task is to inspect the latest deadlock evidence, identify inconsistent lock order or excessive transaction scope, and verify that application retry logic replayed the full transaction safely. Track deadlock rate over time; one occasional deadlock under concurrency is different from a regression that makes a key workflow repeatedly fail.

Retries need backoff and jitter so many clients do not wake at the same instant and collide again. The maximum attempt count should be small and explicit. If the retry budget is exhausted, surface a controlled error or queue the work rather than looping indefinitely. If the transaction calls external systems—charging a card, sending a message, creating a remote resource—database rollback cannot undo those effects; use an outbox/saga/idempotency design appropriate to the architecture.

Chapter 06 summary and bridge to InnoDB internals

You can now connect a write to its concurrency consequences. INSERT/UPDATE/DELETE operate inside transaction boundaries; MVCC controls version visibility for consistent reads; locking reads and DML acquire record/range locks; waits expose blocking dependencies; deadlocks resolve cycles by rolling back a victim; and application retries must replay a well-defined, idempotent transaction rather than an arbitrary statement.

Chapter 07 goes beneath these behaviors into InnoDB Storage Architecture and Transaction Internals: tablespaces, pages, clustered/secondary indexes, buffer pool, redo, undo, doublewrite, checkpoints, crash recovery, purge, and history-list behavior.

Authoritative references

• MySQL 8.4 Reference Manual — Deadlocks in InnoDB
• MySQL 8.4 Reference Manual — How to Minimize and Handle Deadlocks
• MySQL 8.4 Reference Manual — data_lock_waits
• MySQL 8.4 Reference Manual — sys.innodb_lock_waits
• MySQL 8.4 Reference Manual — InnoDB Error Handling