The two transactions that lock each other: POS deadlocks and phantom inventory

1. The two code paths that lock in opposite order

A completed sale locks the stock row for the product, then updates the firearm/serialised-inventory row to `sold`. A voided sale does the reverse of what it can see first: it flips the serialised-inventory row back to `in_stock`, then adjusts the aggregate stock level. Read those two sequences next to each other and the deadlock is obvious — one path takes lock A then lock B, the other takes lock B then lock A.

Under `SERIALIZABLE` or even the default `REPEATABLE READ` with `SELECT ... FOR UPDATE`, that is the textbook deadlock. The database detects the cycle, picks the transaction that has done the least work, and rolls it back with `Deadlock found; try restarting transaction`. Nothing is corrupt at the database layer — MySQL did exactly the right thing. The corruption happens one layer up.

// Sale completion — locks stock level FIRST, then the serialised unit.
DB::transaction(function () use ($sale) {
    $level = StockLevel::where('product_id', $sale->productId)
        ->where('branch_id', $sale->branchId)
        ->lockForUpdate()        // lock A
        ->firstOrFail();

    $unit = FirearmInventory::where('id', $sale->unitId)
        ->lockForUpdate()        // lock B
        ->firstOrFail();

    $level->decrement('on_hand');
    $unit->update(['status' => 'sold']);
});

// Void — touches the serialised unit FIRST, then the stock level.
DB::transaction(function () use ($sale) {
    FirearmInventory::where('id', $sale->unitId)
        ->where('status', 'sold')
        ->lockForUpdate()        // lock B
        ->update(['status' => 'in_stock']);

    $this->adjustStock($sale->productId, $sale->branchId, +1); // locks A
});

2. Why the loss is silent, not loud

The obvious symptom — a 500 error at the till — is the harmless one. The operator sees it, retries, and moves on. The dangerous symptom is the partial one. If the void transaction is the victim and rolls back, the operator sees an error and assumes nothing happened. But if the retry logic is naive — or the operator re-voids by hand — the serialised unit can flip to `in_stock` while the aggregate stock adjustment never lands, or lands twice.

Now the ledger and the shelf disagree, and nothing in the application knows. There is no exception in the log for the drift itself; the only trace is a `Deadlock found` line in the MySQL error log that nobody is watching. The variance surfaces weeks later at stocktake, by which point the transactions that caused it are long gone and unattributable.

3. The fix is three disciplines, not one patch

First: impose a single global lock order. Every transaction that touches both stock levels and serialised units must acquire them in the same order — lowest-cardinality table first, or simply alphabetical by table, as long as it is consistent everywhere. This alone removes the cycle. It is a discipline, not a library, which is why it drifts; enforce it with a code-review rule and a single locking helper that both paths call.

Second: retry on deadlock, idempotently. A deadlock is a transient, expected condition, not an error — Laravel's `DB::transaction($callback, $attempts)` will re-run the closure on a deadlock. But retries are only safe if the closure is idempotent, which means the void must be written so that running it twice leaves the same result as running it once (guard on current status, not blind increment).

Third: make the invariant checkable. Add a reconciliation job that asserts `sum(serialised units in stock) == aggregate on_hand` per product per branch, and alerts on drift the same day. You cannot prevent every desync, but you can guarantee you find it in hours instead of at quarter-end.

// One locking helper, one order, every path. Retries on deadlock.
public function withOrderedLocks(int $productId, int $unitId, Closure $fn): mixed
{
    return DB::transaction(function () use ($productId, $unitId, $fn) {
        // Always stock level (lock A) BEFORE serialised unit (lock B).
        $level = StockLevel::where('product_id', $productId)
            ->lockForUpdate()->firstOrFail();
        $unit  = FirearmInventory::where('id', $unitId)
            ->lockForUpdate()->firstOrFail();

        return $fn($level, $unit);
    }, attempts: 3); // deadlock victim is retried, not surfaced as a 500
}