Two cashiers, one last bottle of kecap: fixing overselling in a Laravel POS with row locks

One of my clients is a small minimarket chain, 4 stores, 6 cashiers per store at peak. Last Lebaran, the day before the holiday, the stock report showed -14 bottles of kecap manis. Negative stock. The owner asked me, very politely, how we sold 14 bottles that do not exist. Good question, mas.

The bug

$product = Product::find($id);

if ($product->stock < $qty) {
    throw new OutOfStock($product);
}

$product->decrement('stock', $qty);

Looks fine. In testing with one cashier it is fine. With two cashiers scanning the last bottle at the same moment: both read stock = 1, both pass the check, both run UPDATE products SET stock = stock - 1. The decrement itself is atomic, but the check happened before, on old data. Stock becomes -1. Multiply by Lebaran.

Fix 1: put the check inside the UPDATE

For simple "take N from stock" this is the best and fastest way. One query, no lock held in PHP:

$affected = Product::whereKey($id)
    ->where('stock', '>=', $qty)
    ->decrement('stock', $qty);

if ($affected === 0) {
    throw new OutOfStock($id);
}

MySQL evaluates stock >= qty and the decrement on the same row under its lock. If two cashiers race for the last bottle, one UPDATE changes 1 row, the other changes 0 rows and gets the out of stock message. Correct.

Fix 2: when you must read and then decide, lock the rows

For the full cart we need more logic (bundle promo, expiry batch FIFO for some items), so we read rows, compute, then write. For that we lock:

DB::transaction(function () use ($items) {
    $ids = collect($items)->pluck('product_id')->unique()->sort()->values();

    $products = Product::whereIn('id', $ids)
        ->orderBy('id')
        ->lockForUpdate()
        ->get()
        ->keyBy('id');

    foreach ($items as $item) {
        $p = $products[$item['product_id']];

        if ($p->stock < $item['qty']) {
            throw new OutOfStock($p);
        }

        $p->decrement('stock', $item['qty']);
    }
}, attempts: 3);

Things that matter here:

• lockForUpdate() only works inside a transaction. Outside a transaction MySQL autocommits the SELECT and releases the lock immediately. No error, no warning, your test passes, production oversells. This one is very dangerous because it looks correct.
• Lock in the same order every time. Cart A has kecap then sugar, cart B has sugar then kecap. A locks kecap, B locks sugar, both wait for each other: deadlock. Sorting by id and orderBy('id') means everybody locks in the same order.
• attempts: 3 makes Laravel retry the whole closure if MySQL still reports a deadlock. With the ordering above it almost never happens, but almost is not never.
• Use the primary key. whereIn('id', ...) locks exactly those rows. If you lock using a column without an index, InnoDB locks every row it scans, and suddenly all 6 cashiers wait for each other.

Result

Since we deployed this: zero negative stock, including one more Lebaran and two promo days. Checkout time went up about 4 ms. The owner is happy, the kecap count is honest, and I sleep during mudik season. Monggo copy if you have the same problem 🙏