Building a Stock Reservation System with Pessimistic Locking in Laravel

Picture a flash sale at PHP Architect's merch store: one hoodie left in stock, two customers hit "buy" within the same second. Without protection, both requests read quantity = 1, both decide there's enough stock, both decrement, and you've oversold by one. This tutorial builds a reservation system that closes that race using row-level pessimistic locking.

Step 1: The schema

Schema::create('inventory_items', function (Blueprint $table) {
    $table->id();
    $table->string('sku')->unique();
    $table->unsignedInteger('quantity_on_hand');
    $table->unsignedInteger('quantity_reserved')->default(0);
    $table->timestamps();
});

Schema::create('stock_reservations', function (Blueprint $table) {
    $table->id();
    $table->foreignId('inventory_item_id')->constrained();
    $table->foreignId('order_id')->nullable()->constrained();
    $table->unsignedInteger('quantity');
    $table->timestamp('expires_at');
    $table->timestamps();
});

quantity_on_hand is the physical count. quantity_reserved tracks stock that's been claimed by an in-progress checkout but not yet fulfilled. Available stock is always quantity_on_hand - quantity_reserved.

Step 2: The reservation service

namespace App\Services;

use App\Models\InventoryItem;
use App\Models\StockReservation;
use App\Exceptions\InsufficientStockException;
use Illuminate\Support\Facades\DB;

class StockReservationService
{
    public function reserve(string $sku, int $quantity): StockReservation
    {
        return DB::transaction(function () use ($sku, $quantity) {
            $item = InventoryItem::where('sku', $sku)
                ->lockForUpdate()
                ->first();

            if (! $item) {
                throw new InsufficientStockException("Unknown SKU: {$sku}");
            }

            $available = $item->quantity_on_hand - $item->quantity_reserved;

            if ($available < $quantity) {
                throw new InsufficientStockException(
                    "Only {$available} units of {$sku} available."
                );
            }

            $item->increment('quantity_reserved', $quantity);

            return StockReservation::create([
                'inventory_item_id' => $item->id,
                'quantity' => $quantity,
                'expires_at' => now()->addMinutes(15),
            ]);
        });
    }
}

lockForUpdate() is the entire trick. It appends FOR UPDATE to the query, which tells the database to acquire a row-level lock on the matching inventory_items row for the duration of the transaction. If a second checkout tries to lock the same row concurrently, it blocks — it simply waits until the first transaction commits or rolls back. There's no race to close because there's never a moment where two processes are both reading a stale quantity_reserved value.

Step 3: Releasing expired reservations

Checkouts abandon carts constantly, so reservations need a time-to-live and a way to give the stock back.

namespace App\Console\Commands;

use App\Models\StockReservation;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class ReleaseExpiredReservations extends Command
{
    protected $signature = 'inventory:release-expired';

    public function handle(): void
    {
        StockReservation::where('expires_at', '<', now())
            ->whereNull('order_id')
            ->each(function (StockReservation $reservation) {
                DB::transaction(function () use ($reservation) {
                    $reservation->inventoryItem()
                        ->lockForUpdate()
                        ->first()
                        ?->decrement('quantity_reserved', $reservation->quantity);

                    $reservation->delete();
                });
            });
    }
}

Register it in the scheduler to run every minute:

Schedule::command('inventory:release-expired')->everyMinute();

Step 4: Fulfilling a reservation

When checkout completes, convert the reservation into a real deduction:

public function fulfill(StockReservation $reservation, int $orderId): void
{
    DB::transaction(function () use ($reservation, $orderId) {
        $item = $reservation->inventoryItem()->lockForUpdate()->first();

        $item->decrement('quantity_on_hand', $reservation->quantity);
        $item->decrement('quantity_reserved', $reservation->quantity);

        $reservation->update(['order_id' => $orderId]);
    });
}

Why not just use optimistic locking?

Optimistic locking (a version column plus a conditional UPDATE ... WHERE version = ?) works too, and avoids holding a lock while other logic runs. But it means the loser of the race has to detect the conflict and retry, which adds complexity to the caller. For inventory, where contention on a single hot SKU during a sale is common and reservations are short-lived, pessimistic locking with lockForUpdate() keeps the logic simple: you either get the lock and proceed, or you wait your turn. Just keep the locked transaction short — don't call out to a payment gateway or send an email while holding that row lock, or you'll turn your flash sale into a queue of very slow, very blocked requests.