An inventory operation often needs more than one SQL statement. Reducing a quantity without recording the matching reservation leaves an incomplete result. A transaction lets those database changes succeed together or be rolled back together.
This runnable PHP PDO example pairs a conditional stock update with an audit insert. The second operation deliberately encounters a duplicate reference after the stock update succeeds, so you can inspect whether the earlier change was undone. The complete project ZIP contains both source files below, twelve tests, a README and expected output.
Prerequisites and the fictional fixture
Use PHP 8.1 or later from a terminal, with PDO and pdo_sqlite. No Composer package, database server, password or web server is needed. The checks ran on PHP 8.2.12 on Windows, not on every supported PHP version. This records the available test environment; it does not describe that patch version as current.
php --version
php -r "print_r(PDO::getAvailableDrivers());"The driver list must include sqlite. Extract the ZIP and open a terminal inside php-pdo-transactions. Each run creates a fresh in-memory database with one product and ten fictional units. It never opens the website database or a user-supplied file. Because storage is in memory, committed data remains available on that connection for the exercise and disappears when the connection closes.
Save inventory.php
<?php
declare(strict_types=1);
function createDemoDb(): PDO
{
$db = new PDO('sqlite::memory:', null, null, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
// Fixed schema and fictional data, created outside the transaction under test.
$db->exec('CREATE TABLE stock (
id INTEGER PRIMARY KEY,
quantity INTEGER NOT NULL CHECK (quantity >= 0)
)');
$db->exec('CREATE TABLE reservations (
reference TEXT PRIMARY KEY NOT NULL,
product_id INTEGER NOT NULL,
units INTEGER NOT NULL CHECK (units > 0)
)');
$db->exec('INSERT INTO stock (id, quantity) VALUES (1, 10)');
return $db;
}
function reserveStock(PDO $db, int $productId, int $units, string $reference): void
{
if ($productId < 1 || $units < 1 || $units > 1000
|| !preg_match('/\A[A-Za-z0-9-]{1,40}\z/', $reference)) {
throw new InvalidArgumentException('Invalid reservation input.');
}
// This function owns its transaction; never commit or undo a caller's work.
if ($db->inTransaction()) {
throw new LogicException('An existing transaction is not supported.');
}
$db->beginTransaction();
try {
$update = $db->prepare('UPDATE stock SET quantity = quantity - :units
WHERE id = :id AND quantity >= :needed');
$update->bindValue(':units', $units, PDO::PARAM_INT);
$update->bindValue(':id', $productId, PDO::PARAM_INT);
$update->bindValue(':needed', $units, PDO::PARAM_INT);
$update->execute();
if ($update->rowCount() !== 1) {
throw new DomainException('Unknown product or insufficient stock.');
}
$audit = $db->prepare('INSERT INTO reservations (reference, product_id, units)
VALUES (:reference, :product, :units)');
$audit->bindValue(':reference', $reference, PDO::PARAM_STR);
$audit->bindValue(':product', $productId, PDO::PARAM_INT);
$audit->bindValue(':units', $units, PDO::PARAM_INT);
$audit->execute();
$db->commit();
} catch (Throwable $error) {
if ($db->inTransaction()) {
$db->rollBack();
}
throw $error;
}
}
function snapshot(PDO $db): array
{
return [
'stock' => $db->query('SELECT id, quantity FROM stock ORDER BY id')->fetchAll(),
'reservations' => $db->query('SELECT reference, product_id, units
FROM reservations ORDER BY reference')->fetchAll(),
];
}
The UPDATE subtracts units only when the product exists and enough quantity remains. We check that one row changed before inserting the reservation. Every variable value is bound to a fixed SQL statement. The reference accepts one to forty ASCII letters, digits or hyphens; units must be between one and one thousand, and the product ID must be positive.
The function rejects an existing transaction before starting its own. That ownership rule prevents a helper from committing or rolling back unrelated caller work. It is deliberately simple: this version does not compose nested transactions or implement savepoints.
Save demo.php and run both paths
<?php
declare(strict_types=1);
require __DIR__ . '/inventory.php';
function showState(PDO $db, string $label): void
{
$state = snapshot($db);
printf("%s: stock=%d, reservations=%d\n", $label,
$state['stock'][0]['quantity'], count($state['reservations']));
}
$db = createDemoDb();
showState($db, 'Initial');
reserveStock($db, 1, 3, 'DEMO-1');
showState($db, 'Committed');
try {
// The stock UPDATE succeeds, then the duplicate reference INSERT fails.
reserveStock($db, 1, 2, 'DEMO-1');
throw new RuntimeException('Expected a duplicate-reference failure.');
} catch (PDOException $error) {
echo "Duplicate reference rejected; transaction rolled back.\n";
}
showState($db, 'After duplicate');
try {
reserveStock($db, 1, 99, 'DEMO-2');
throw new RuntimeException('Expected an insufficient-stock failure.');
} catch (DomainException $error) {
echo "Insufficient stock rejected; transaction rolled back.\n";
}
showState($db, 'After shortage');
php demo.phpThe expected application output is:
Initial: stock=10, reservations=0
Committed: stock=7, reservations=1
Duplicate reference rejected; transaction rolled back.
After duplicate: stock=7, reservations=1
Insufficient stock rejected; transaction rolled back.
After shortage: stock=7, reservations=1
The first reservation commits, reducing stock from ten to seven and inserting one audit row. The second call tries the same reference with two more units. Its UPDATE can run, but its INSERT violates the unique reference constraint. The catch block rolls the transaction back, restoring seven units and retaining the original single reservation. The final call asks for too many units and also leaves the state unchanged.
The demo catches the two expected failure types so the demonstration completes successfully. An unexpected failure remains an error. The example rejects duplicate references; it does not implement an idempotent retry API that returns an earlier successful response.
Understand the transaction boundary
The official beginTransaction reference describes starting a transaction and suspending autocommit until it ends. The commit reference describes committing the grouped work and returning to autocommit. In this example, commit happens only after both statements complete.
The rollBack reference explains undoing the current transaction and notes that calling it without an active transaction raises an exception. The handler checks inTransaction before rollback, then rethrows the original operation error. A failed rollback can itself raise an exception; a production application's error reporting should preserve both failure contexts when that happens.
Schema creation is outside the operation under test. PHP's documentation warns that some databases implicitly commit schema-changing statements, so moving CREATE TABLE into a transaction and expecting portable rollback would be a separate mistake. This example uses SQLite; changing the DSN is not a substitute for checking another engine's transaction behavior.
Run the twelve transaction cases
php test.phpAll twelve cases passed on the recorded runtime. They verify the exact committed stock and audit row, rollback after a duplicate audit insert, insufficient stock, an unknown product, zero/negative/excessive units, invalid/overlong references, preserving an existing caller transaction, reusing the connection after rollback, and exact depletion followed by shortage. Rejected operations compare complete before-and-after snapshots rather than merely checking that an exception happened.
The tests use explicit failures instead of PHP assert, so they work independently of assertion configuration. All three PHP files passed syntax checks. The delivered ZIP was extracted separately, and its twelve cases and exact demo output passed again. The local CLI emitted a pre-existing duplicate OpenSSL module startup warning; checks recorded it separately from application output using display_errors=stderr. No extension configuration was changed. A clean PHP installation should not show that warning.
Limits before using this pattern in an application
The SQLite transaction documentation explains that several connections can read, while only one write transaction can run at a time. This exercise uses one connection sequentially. It demonstrates grouped state changes, not concurrency throughput, lock retries, crash durability or a production stock-reservation service.
Transactions cover the database work within their boundary. They do not undo an email, remote API request or file change made elsewhere. Keep those effects out of this small exercise; a real workflow needs an explicit coordination design. There is also no HTTP authentication, authorization, persistence or retry policy here.
The rowCount check refers to this SQLite UPDATE that changes a positive quantity. Do not repurpose it as a portable count of SELECT results. If prepared statements are new to you, the earlier PDO parameter-binding example covers the distinct input-versus-SQL boundary. Both lessons matter: binding controls how values enter a statement, while a transaction controls which database changes belong together.
Comments (0)
Join the discussion