Deadlock Analysis, Lock Waits, Retry Policies, Idempotency, and Contention Testing

Learning outcomes

A lock wait has a direction: transaction A waits for a resource transaction B holds. A deadlock has a cycle: A waits for B while B waits for A (or a longer cycle closes through more transactions). Waiting longer cannot resolve a closed cycle, so InnoDB detects deadlocks and rolls back a victim. The application must be prepared for this even when every SQL statement is individually valid.

This lesson turns deadlocks from “database randomness” into a testable application condition. You will create one safely using two stock rows, read the latest detected deadlock from InnoDB, compare error 1213/SQLSTATE 40001 with lock wait timeout error 1205, then design whole-transaction retries with bounded attempts, jitter and idempotency. The objective is not zero deadlocks at any cost; it is correct behavior under unavoidable concurrency plus schema/query design that reduces unnecessary cycles.

1. Construct a cycle deliberately

The deadlock uses two rows and opposite acquisition order. Session A locks part 1. Session B locks part 2. A then requests part 2 and waits. B requests part 1, closing the cycle. InnoDB can now prove that both cannot make progress, chooses a victim, rolls that transaction back, and lets the survivor continue.

START TRANSACTION;UPDATE parts_stock SET qty_on_hand=qty_on_hand-1 WHERE part_id=1;-- Do not commit.-- After Session B locks part 2, run:UPDATE parts_stock SET qty_on_hand=qty_on_hand-1 WHERE part_id=2;

START TRANSACTION;UPDATE parts_stock SET qty_on_hand=qty_on_hand-1 WHERE part_id=2;-- After Session A is waiting on part 2, run:UPDATE parts_stock SET qty_on_hand=qty_on_hand-1 WHERE part_id=1;

One session should receive ERROR 1213 with SQLSTATE 40001: deadlock found when trying to get lock; try restarting transaction. Do not assume Session A or B will always be the victim. InnoDB considers transaction weight and other internal factors; application correctness must not depend on which transaction survives.

-- In the surviving session:COMMIT;-- In a fresh transaction after both clients are clean:UPDATE parts_stock SET qty_on_hand=10 WHERE part_id IN (1,2);COMMIT;

2. Read the evidence, not just the client error

SHOW ENGINE INNODB STATUS includes a LATEST DETECTED DEADLOCK section after a deadlock occurs. It shows transactions, statements, locks held/requested and the rollback decision. Capture it promptly because “latest” is not an archival history. MariaDB also exposes the cumulative Innodb_deadlocks status counter on current versions.

SHOW ENGINE INNODB STATUS\GSHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';SELECT trx_id,trx_state,trx_started,trx_mysql_thread_id,trx_queryFROM information_schema.innodb_trx\G

For repeated production deadlocks, innodb_print_all_deadlocks can write every deadlock to the error log when enabled, but logging volume and sensitive SQL/data exposure should be considered. Do not enable verbose diagnostics permanently without an operational reason and retention plan.

3. Deadlock is not the same as lock-wait timeout

MariaDB documentation explicitly notes that innodb_lock_wait_timeout does not control deadlocks: deadlocks are detected immediately. Increasing the timeout cannot break a cycle. It can only make ordinary waits linger longer, which may worsen request latency and lock queues.

4. Repair the cause: consistent access order

The simplest way to remove the synthetic cycle is for every transaction to acquire the two stock rows in the same order. If both A and B update part 1 before part 2, one may wait, but there is no two-way cycle: the waiter has not already taken part 2 while requesting part 1.

START TRANSACTION;SELECT part_id,qty_on_handFROM parts_stockWHERE part_id IN (1,2)ORDER BY part_idFOR UPDATE;UPDATE parts_stock SET qty_on_hand=qty_on_hand-1 WHERE part_id=1;UPDATE parts_stock SET qty_on_hand=qty_on_hand-1 WHERE part_id=2;COMMIT;

Canonical ordering is powerful when the application can enumerate all resources first. It does not eliminate every possible deadlock: foreign keys, secondary indexes, triggers, different query plans and multiple tables can introduce other lock orders. Still, a documented ordering rule reduces an avoidable class of cycles.

5. Keep transactions short and access paths selective

Deadlock probability grows when transactions hold locks for longer and when statements touch more records/ranges than the business operation requires. Keep network calls and user think-time outside the transaction. Add/select indexes from evidence so UPDATE/DELETE/locking predicates do not scan and lock unnecessarily broad ranges. Split unrelated work into separate atomic units where business invariants permit.

Do not react to deadlocks by globally weakening isolation or removing indexes/constraints without a reproducible test. Constraints can add locks because they protect correctness; removing them may trade a visible concurrency error for silent data corruption.

6. Retry the transaction, not the failed statement

After error 1213, the database has rolled back the chosen transaction. Retrying only its final UPDATE is wrong because earlier changes in that transaction are gone. The application should begin a fresh transaction and replay the whole unit from a clean state. The same is often prudent after a timeout: explicitly ROLLBACK the old transaction before retrying so no earlier successful statements remain accidentally open.

max_attempts = 4for attempt in 1..max_attempts:    begin transaction    try:        insert idempotency key / load current state        acquire rows in canonical order        apply all business changes        verify affected-row invariants        commit        return success    catch deadlock_or_retryable_conflict:        rollback        if attempt == max_attempts: fail        sleep(exponential_backoff(attempt) + random_jitter)        continue    catch other_error:        rollback        raise

Backoff prevents immediately recreating the same collision. Jitter prevents many workers from waking at the same instant. Bounds prevent an overloaded service from turning a hot row into an infinite retry storm. The retry classifier should use driver error codes/SQLSTATEs and the application’s business semantics, not string matching on localized error text.

7. Idempotency makes retry safe at the business boundary

A database retry is safe only when repeating the transaction cannot duplicate the business effect. Use a stable request/operation key, unique constraint and explicit result contract. For example, a client-provided dispatch request ID can be inserted into request_dedup inside the same transaction before changing work/stock. If the original transaction committed but the network response was lost, a retry can detect that committed key instead of decrementing stock twice.

START TRANSACTION;INSERT INTO request_dedup(request_key,operation_name,work_order_id)VALUES ('consume-parts-1002-v1','consume-parts',1002);SELECT part_id,qty_on_handFROM parts_stockWHERE part_id IN (1,2)ORDER BY part_idFOR UPDATE;UPDATE parts_stock SET qty_on_hand=qty_on_hand-1 WHERE part_id=1 AND qty_on_hand>0;UPDATE parts_stock SET qty_on_hand=qty_on_hand-1 WHERE part_id=2 AND qty_on_hand>0;COMMIT;

A duplicate-key error on the request key must be interpreted by application policy: return the stored prior result, query the existing business state, or reject a conflicting payload. A unique key alone is not a complete idempotency protocol, but it is a durable concurrency primitive on which one can be built.

8. Contention test design

A useful contention test uses many independent sessions issuing the actual transaction shape against a representative dataset, records throughput/latency/error distribution, and captures deadlock/lock-wait evidence. It should vary concurrency intentionally and state hardware, storage, dataset size, isolation, indexes and cache warmth. Do not publish one laptop’s “transactions per second” as a universal MariaDB number.

Acceptance criteria can include: zero invariant violations; deadlocks remain below an agreed operational rate; retries succeed within a bounded budget; p95/p99 latency stays within service targets; transaction age remains bounded; and lock-wait/deadlock logs identify the expected hot resources. A deadlock rate of zero is not automatically proof of a healthy design if the application serialized all work through one global lock.

9. Failure drill, knowledge check and Chapter 06 acceptance criteria

1. Create the two-session deadlock and record which session receives 1213/40001.
2. Capture SHOW ENGINE INNODB STATUS immediately and identify the two statements/locks.
3. Record Innodb_deadlocks before/after.
4. Create an ordinary one-way row lock wait and compare it with a deadlock; use a short session-level innodb_lock_wait_timeout only in the disposable lab if needed.
5. Repair the synthetic cycle by locking parts in ascending part_id order.
6. Write retry pseudocode that retries the whole transaction with rollback, bounds, backoff and jitter.
7. Add the request_dedup key and explain the lost-response retry case.
8. Reset stock rows and end every open transaction.

10. Chapter 06 summary and bridge

Chapter 06 connected MariaDB writes to concurrency mechanisms. You distinguished REPLACE from upsert, made RETURNING version/statement-aware, grouped business changes into explicit InnoDB transactions, exposed implicit-commit DDL boundaries, compared MVCC snapshots and locking reads, diagnosed metadata/record/gap locks, and turned deadlocks into a bounded retry condition rather than an unexplained failure.

Chapter 07 goes below these SQL-level behaviors into InnoDB storage internals: tablespaces/pages, clustered and secondary indexes, buffer pool, redo/undo, doublewrite, checkpoints, crash recovery, purge and long-transaction history. The lock and MVCC observations from this chapter become easier to interpret once you can map them to the storage structures underneath.