PHP Atomic Stock Updates

How it works

This lab uses one conditional UPDATE to subtract only when enough units remain. The affected-row result identifies whether the operation matched. A full loan workflow normally groups this mutation with the new loan record in one transaction and applies the appropriate authorisation and idempotency rules. The example checks the SQL contract in one connection; concurrent production behaviour also depends on engine isolation and locking.

Run it locally

Run this complete source locally with PHP 8.2 or newer. Save it using the filename shown and run the command below. PHP is not currently available in Bhylu’s browser playground. Apache and MySQL are not needed for an ordinary CLI example.

php -l conditional-stock.php
php conditional-stock.php

Download this source as a local PHP lab file. Keep lab files separate from the Bhylu project and production data.

Example

<?php
declare(strict_types=1);

$db = new PDO("sqlite::memory:",null,null,[PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION]);
$db->exec("CREATE TABLE stock (id INTEGER PRIMARY KEY, available INTEGER NOT NULL CHECK(available >= 0))");
$db->exec("INSERT INTO stock VALUES (1,5)");
$reserve = $db->prepare("UPDATE stock SET available = available - :take WHERE id = :id AND available >= :needed");
foreach ([3,3] as $count) {
    $reserve->execute(["take"=>$count,"id"=>1,"needed"=>$count]);
    echo $reserve->rowCount() === 1 ? "Reserved\n" : "Insufficient stock\n";
}
echo $db->query("SELECT available FROM stock WHERE id=1")->fetchColumn(), " left\n";

Expected result

Reserved
Insufficient stock
2 left

The second request no longer satisfies the availability predicate, so it cannot produce a negative count. Separate placeholders avoid relying on repeated named-parameter behaviour across drivers. Validate a positive requested count before this statement; otherwise subtracting a negative value would violate the intended business operation even if the database constraint still held.