Transaction/For update lock for an eCommerce project

Question

When users want to purchase their orders, The quantity of the products should be decreased. Now I'm planning to use a Transaction and a FOR UPDATE lock on the products and decrease the quantity of each product.

Laravel psudo code:

// Check quantities before entering the transaction
...

DB::transaction(function () use ($cartItems) {
    $ids = $cartItems->pluck('product_id')->toArray(); // Extract product_ids -> [50, 100, 99, ...]

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

    // Check quantities
    foreach ($cartItems as $item) {
        // Find the associated product
        $product = $products->first(fn ($product) => $product->id === $item->product_id);

        // Throw an exception if the product quantity is less than the quantity of the cart item
        if ($product->quantity < $item->quantity) {
            throw new \Exception("Quantity error for Product #{$product->id}");
        }
    }

    // Decrease quantities for each product
    foreach ($cartItems as $item) {
        $product = $products->first(fn ($product) => $product->id === $item->product_id);
        
        // SET `quantity` = `quantity` - ?
        $product->decrement('quantity', $item->quantity);

        $product->save();
    }

    ...
});

...

// Go to paypal

Why the FOR UPDATE lock?
Because of race-conditions. Multiple users could order the same product at the same time while the product quantity was 1.

I tried to log sql queries of Woocommerce and some other eCommerce projects but they don't use Transactions or the for update lock. I also asked ChatGPT to generate the code it gave me a similar piece of code to mine.

Please correct me and my code if i'm wrong.

Update

An update for one of the comments.

In the following code we have 3 routes.

1. /lock-and-sleep Selects a product with the for update lock then sleeps for 20 seconds.
2. /test-without-lock Selects the same product without a lock. (Independent)
3. /test-with-lock Selects the product with a for update. This is dependent to the first route.

// Response time: 20s~
Route::get('/lock-and-sleep', function () {
    DB::transaction(function () {
        Product::where('id', 5)->lockForUpdate()->first();

        sleep(20);
    });
});

// Response time: 0.XYZms (Not Blocked)
Route::get('/test-without-lock', function () {
    Product::where('id', 5)->first();

    DB::transaction(function () {
        Product::where('id', 5)->first();
    });
});

// Response time: 20s~ (Blocked)
Route::get('/test-with-lock', function () {
    DB::transaction(function () {
        Product::where('id', 5)->lockForUpdate()->first();
    });
});

Answer 1

Little point checking quantities before entering the transaction, it will be subject to race conditions, the only check that has meaning is the one being done in the transaction under the forUpdate lock.

You could put the decrement in the same loop as the check.

DB::transaction handles the start of transaction, commit on exit and rollback on exception.

There should be code that saves an order in a table too right? This should be in the same transaction.

Optionally, for user experience, calculate if all the items have the right quantities and roll them up in the same error message if multiple products have insufficient amounts.

Handling paypay outside the loop means that order cancelation is a separate process.