Do not read the stock quantity and then update it in a separate statement. Two requests can both pass that check. Let one conditional UPDATE test and decrement the row atomically, then verify whether it changed a row.
Last updated: November 4, 2026.
<?php
$productId = 42;
$quantity = 5;
$pdo->beginTransaction();
try {
$statement = $pdo->prepare(
'UPDATE products
SET stock = stock - :quantity
WHERE id = :id AND stock >= :quantity'
);
$statement->execute(['quantity' => $quantity, 'id' => $productId]);
if ($statement->rowCount() !== 1) {
throw new RuntimeException('Insufficient stock');
}
createOrderLine($pdo, $productId, $quantity);
$pdo->commit();
} catch (Throwable $error) {
if ($pdo->inTransaction()) $pdo->rollBack();
throw $error;
}Validate that $quantity is a positive bounded integer before starting. The order line and inventory decrement commit together, or both are rolled back.
Why the conditional update is safe
MySQL documents that each statement is atomic when autocommit is enabled, and an explicit transaction groups related statements. The UPDATE statement changes only rows matching its WHERE condition. InnoDB locks the matching inventory row while applying the decrement, so a concurrent request evaluates the condition against the latest committed quantity after it obtains the lock.
MySQL’s affected-row documentation explains that an update reports the number of rows actually changed. A result of zero means the product does not exist or no longer has enough stock. Return a conflict or validation response; do not retry the same quantity blindly.
Use row locking only for more complex rules
If pricing, reservations, bundles, or several inventory rows must be inspected before the update, use SELECT ... FOR UPDATE inside the same transaction. MySQL’s locking-read guide says those locks remain until commit or rollback. Lock multiple products in a consistent order to reduce deadlocks.
Add a CHECK (stock >= 0) constraint as defense in depth, but keep the conditional update because the constraint alone turns overselling into an exception without giving the application a clean availability decision. Test with two real database connections attempting overlapping purchases. Continue with reading PDO database errors, recovering after deadlocks, and connecting PHP to MySQL.
