Preventing Overselling in Laravel with Transactions and Row Lock

Two buyers press the checkout button when one is left in stock. If the application reads the stock, checks its value, then reduces the stock in a separate query without locking, both can pass. Validation quantity >= 1 only checks inputs; it does not regulate competition between database connections.

This article builds the core of a reservation for a single product. Examples are intended for Laravel 13, PHP 8.5, and MySQL with InnoDB tables. The reader is assumed to have understood migration, Query Builder, and Form Request. PostgreSQL also supports row lock, but concurrency test on the database engine that the application actually uses.

Data contracts and transaction limits

IBus Table Setup products with id and stock nonnegative integers, as well as reservations with id, user_id, product_id, quantity, status, and timestamps. Add the appropriate foreign key. In the Request Form, quantity validation as required|integer|min:1|max:100, and retrieve user ID from authentication session. Do not accept user_id or the final price of the buyer's payload.

The order of operations on the server is to lock the product, check the latest stock, reduce stock, and save reservations. All steps must use the same database connection and transactions.

<?php

namespace App\Services;

use Illuminate\Support\Facades\DB;
use Illuminate\Validation\ValidationException;

final class ReserveStock
{
    public function reserve(int $userId, int $productId, int $quantity): int
    {
        if ($quantity < 1 || $quantity > 100) {
            throw ValidationException::withMessages([
                'quantity' => 'Kuantitas harus antara 1 dan 100.',
            ]);
        }

        return DB::transaction(function () use ($userId, $productId, $quantity): int {
            $product = DB::table('products')
                ->where('id', $productId)
                ->lockForUpdate()
                ->first();

            if ($product === null || $product->stock < $quantity) {
                throw ValidationException::withMessages([
                    'quantity' => 'Stok produk tidak mencukupi.',
                ]);
            }

            DB::table('products')->where('id', $productId)
                ->decrement('stock', $quantity);

            return DB::table('reservations')->insertGetId([
                'user_id' => $userId,
                'product_id' => $productId,
                'quantity' => $quantity,
                'status' => 'pending',
                'created_at' => now(),
                'updated_at' => now(),
            ]);
        }, attempts: 3);
    }
}

Create a class via php artisan make:class Services/ReserveStock --no-interaction. The controller forwards authenticated users and values from $request->validated() to this service. Product or tenant authorization must still be made prior to reservation.

Why is the lock inside the transaction?

The second connection waits for the first connection to remove the key. After the first commit transaction, the second connection reads the already reduced stock. If reservation saving fails, stock reduction follows rollback. Locks installed outside the transaction do not protect the next set of operations.

Do not call payment gateways, send emails, or access external HTTPs inside the closure. The operation extends the duration of the lock and does not follow the rollback. Argument attempts: 3 allows Laravel to retry transactions when deadlocked; closure should therefore be free of external side effects. For multi-product baskets, sort product IDs and keys in a consistent order to reduce the risk of deadlocks.

Reservation not final payment

Reservation requires expiration time and stock release process. When canceling, lock the reservation line, make sure the status is still pending, then change the status and return the stock in one transaction. If two workers cancel the same reservation, only one may return the stock. For multi-product orders, keep the lock order consistent between checkout and cancellation.

Buyer HTTP retry is also different from deadlock retry. A checkout request resubmitted after a response is interrupted can make two reservations. Add an idempotency key with a user-bound unique index if the endpoint must withstand retransmission.

Skenario Pengujian

With stock one, run two checkouts using two database connections: exactly one successful reservation and zero stock. Zero stock test, invalid quantity, rollback after insert failed, and double cancellation. SQLite in-memory does not prove InnoDB row lock behavior. Use isolated MySQL test database for concurrency testing.

API Reference: Pessimistic locking and Database transactions.