Transactions, Locking & Concurrency in Code · Capstone Case Studies

Capstone: Postmortem of a Deadlock Storm

Investigate a real-looking incident from logs and code, find the root cause, and write the fix and the follow-ups.

Scenario

Example company: a marketplace on MySQL 8 (InnoDB, Repeatable Read). Tuesday 14:05 a release ships "multi-seller carts": checkout now reserves stock for every line and creates one sub-order per seller. From 14:07, checkout errors jump from 0.1% to 6%, p99 goes from 400 ms to 9 s, and the connection pool alarms. Rolling back at 14:40 restores normal service.

Evidence

SHOW ENGINE INNODB STATUS (excerpt, repeated hundreds of times with different SKUs):

*** (1) TRANSACTION: TRANSACTION 9184771, ACTIVE 0 sec starting index read
UPDATE inventory SET reserved = reserved + 1 WHERE sku = 'MUG-RED'
*** (1) HOLDS THE LOCK(S):   RECORD LOCKS … index PRIMARY of table `shop`.`inventory` … lock_mode X locks rec but not gap  (sku 'TEE-BLK-M')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: … lock_mode X locks rec but not gap  (sku 'MUG-RED')
*** (2) TRANSACTION: TRANSACTION 9184775, ACTIVE 0 sec starting index read
UPDATE inventory SET reserved = reserved + 1 WHERE sku = 'TEE-BLK-M'
*** (2) HOLDS THE LOCK(S): … (sku 'MUG-RED')
*** WE ROLL BACK TRANSACTION (2)

Also in the logs: Lock wait timeout exceeded; try restarting transaction (error 1205) for INSERT INTO sub_orders …, and the new code:

DB::transaction(function () use ($cart) {
    $order = Order::create([...]);
    foreach ($cart->lines as $line) {                        // order the customer added items
        Inventory::where('sku', $line->sku)->increment('reserved', $line->qty);
    }
    foreach ($cart->lines->groupBy('seller_id') as $sellerId => $lines) {
        $seller = Seller::find($sellerId);
        $payout = $this->payoutService->quote($seller, $lines);   // HTTP call, 200 ms – 3 s
        SubOrder::create([...]);
    }
});

Deliverables

The root causes (there are two), explained from the evidence.
The immediate fix you would ship to re-enable multi-seller carts.
Longer-term changes to code, configuration and process.
The monitoring/alerting that would have caught this in minutes, or before release.

Hints

Compare the lock order of the two transactions in the deadlock report.
What else is happening inside the transaction while those row locks are held?

Draft each deliverable before revealing the model answer and rubric.

Further reading: MySQL: deadlocks in InnoDB · MySQL: how to minimize and handle deadlocks

Deliverables

What are the two root causes?

Model answer

Inconsistent lock order: inventory rows are updated in cart order. Customer A added TEE then MUG, customer B MUG then TEE → each holds one row and waits for the other → InnoDB deadlock (1213). Multi-line carts made this common.
Long transactions holding locks: a 0.2–3 s HTTP call (payoutService->quote) runs inside the transaction while inventory row locks are held. Popular SKUs queue behind it → lock wait timeouts (1205), slow checkouts and pool exhaustion. Longer lock hold times also widen the deadlock window.

Rubric

Full marks need both: the ordering cycle proven from the report (each transaction holds the row the other wants) and the external call inside the transaction explaining 1205s and pool alarms. Partial: only one cause, or 'add retries' presented as the root cause.

What is the immediate fix?

Model answer

Fetch payout quotes before the transaction (or after commit); the transaction only does database writes.
Reserve inventory in a deterministic order: foreach ($cart->lines->sortBy('sku') as $line).
Keep DB::transaction(..., attempts: 3) so the rare remaining deadlock is retried — safe now that the closure has no side effects.

$quotes = $cart->lines->groupBy('seller_id')->map(fn ($l, $id) => $this->payoutService->quote(Seller::find($id), $l));
DB::transaction(function () use ($cart, $quotes) {
    $order = Order::create([...]);
    foreach ($cart->lines->sortBy('sku') as $line) {
        Inventory::where('sku', $line->sku)->increment('reserved', $line->qty);
    }
    foreach ($quotes as $sellerId => $quote) { SubOrder::create([...]); }
}, attempts: 3);

Rubric

Look for: HTTP call moved out, sorted lock order, retries only once the closure is side-effect free. Misses: only raising innodb_lock_wait_timeout, only adding retries, switching isolation level without addressing ordering.

Which longer-term changes would you make?

Model answer

A shared reserveInventory(lines) helper that always sorts and is the only way to reserve stock.
Guard rails: innodb_lock_wait_timeout lowered for the app user (e.g. 5 s), a statement timeout, and a lint/review rule against HTTP clients inside transaction closures.
Enable innodb_print_all_deadlocks permanently.
Concurrency tests in CI: N parallel checkouts with overlapping multi-line carts in random order; assert no deadlocks/timeouts above a tiny rate.
Canary releases with automatic rollback on error-rate or deadlock-rate regressions.

Rubric

Look for: making the safe path the default (shared helper), configuration guard rails, test coverage for concurrency, and progressive delivery. Misses: one-off fixes without preventing recurrence.

What monitoring would have caught this early?

Model answer

Deadlock rate (from logs with innodb_print_all_deadlocks, or the Innodb_deadlocks status counter) with an alert on a jump vs baseline.
Lock-wait metrics: performance_schema.data_lock_waits sampling, error 1205 counts.
Per-endpoint p99 and error rate with deploy markers; pool wait time.
Traces: HTTP spans nested in DB transaction spans flagged automatically.

Rubric

Look for: database-level contention signals (deadlocks, lock waits), application signals tied to deploys, and tracing that exposes calls inside transactions. Misses: only CPU/memory dashboards.

Open in the app to mark lessons complete, take notes and track your progress.