Recommended Free Tools
Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
Reduce inventory with a conditional SQL UPDATE, not by reading the quantity into PHP and writing back a calculated value:
UPDATE products
SET stock_quantity = stock_quantity - :amount
WHERE id = :id
AND stock_quantity >= :amountIf the statement affects one row, the requested quantity was deducted. If it affects no rows, the product may not exist or may not have enough stock. The condition and subtraction happen together, which avoids the lost-update race that can occur when two customers buy the last units simultaneously.
Basic PDO implementation
Use a prepared statement and validate the requested amount before sending it to the database. This example assumes whole-unit inventory stored in a MySQL or MariaDB table using a transactional engine such as InnoDB.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsCREATE TABLE products (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
sku VARCHAR(64) NOT NULL,
name VARCHAR(255) NOT NULL,
stock_quantity INT NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE KEY uq_products_sku (sku)
) ENGINE = InnoDB;<?php
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$productId = 42;
$amount = 2;
if ($productId < 1 || $amount < 1) {
throw new InvalidArgumentException('Product ID and quantity must be positive.');
}
$stmt = $pdo->prepare('
UPDATE products
SET stock_quantity = stock_quantity - :amount
WHERE id = :product_id
AND stock_quantity >= :amount
');
$stmt->execute([
':amount' => $amount,
':product_id' => $productId,
]);
if ($stmt->rowCount() !== 1) {
throw new RuntimeException('Product not found or insufficient stock.');
}For a one-unit deduction, use stock_quantity - 1 and stock_quantity > 0. For arbitrary quantities, bind the requested amount as shown above. Never concatenate unvalidated request data into SQL.
#1 Best Overall
Validate quantities at the application boundary
Reject zero, negative, decimal, missing, and implausibly large quantities before attempting the update.
$amount = filter_input(
INPUT_POST,
'quantity',
FILTER_VALIDATE_INT,
['options' => ['min_range' => 1]]
);
if ($amount === false || $amount === null) {
throw new InvalidArgumentException('Quantity must be a positive integer.');
}The SQL condition is still required. PHP validation protects the input; the database condition protects the inventory when concurrent requests arrive.
Why subtraction in PHP is unsafe
A read-then-write sequence can lose an update:
$current = getStock($productId);
if ($current >= $amount) {
$newStock = $current - $amount;
setStock($productId, $newStock);
}Suppose two requests both read a quantity of 1. Both pass the check and both calculate 0. The second write can overwrite the first, even though two purchases were accepted. A conditional update makes the availability test and arithmetic one database operation. It prevents the specific negative-stock race as long as every stock-consuming code path follows the same rule.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Use a transaction for orders and related writes
A stock decrement alone may not be enough. If it accompanies an order, order item, reservation, warehouse balance, or inventory movement, commit all related database changes together.
Rank #2
<?php
try {
$pdo->beginTransaction();
$stock = $pdo->prepare('
UPDATE products
SET stock_quantity = stock_quantity - :quantity
WHERE id = :product_id
AND stock_quantity >= :quantity
');
$stock->execute([
':quantity' => $amount,
':product_id' => $productId,
]);
if ($stock->rowCount() !== 1) {
throw new RuntimeException('Insufficient stock or product not found.');
}
$item = $pdo->prepare('
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (:order_id, :product_id, :quantity)
');
$item->execute([
':order_id' => $orderId,
':product_id' => $productId,
':quantity' => $amount,
]);
$pdo->commit();
} catch (Throwable $e) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
throw $e;
}If inserting the order item fails, the rollback restores the deducted stock. PDO transactions provide this all-or-nothing behavior only when the driver and database tables support transactions. For MySQL and MariaDB, use a transactional engine such as InnoDB. See PDO transaction documentation and PDO::beginTransaction().
Distinguish failure cases when necessary
Affected-row checking is a practical success test for this example, but zero affected rows can mean either that the product ID is missing or that available stock is too low. If the user interface needs different messages, perform a follow-up read after the failed update:
SELECT stock_quantity
FROM products
WHERE id = :product_idDo not treat that follow-up read as the authoritative stock check. The conditional UPDATE remains the operation that decides whether the deduction succeeds. Also test the affected-row behavior of the PDO driver deployed by your application.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →When to use SELECT ... FOR UPDATE
A conditional update is usually best when the complete rule is simply “deduct this amount if enough exists.” Use a locking read when you need the current row for additional decisions, such as checking a price, warehouse, product state, or bundle components.
try {
$pdo->beginTransaction();
$select = $pdo->prepare('
SELECT id, stock_quantity, price
FROM products
WHERE id = :id
FOR UPDATE
');
$select->execute([':id' => $productId]);
$product = $select->fetch(PDO::FETCH_ASSOC);
if (!$product) {
throw new RuntimeException('Product not found.');
}
if ((int) $product['stock_quantity'] < $amount) {
throw new RuntimeException('Insufficient stock.');
}
$update = $pdo->prepare('
UPDATE products
SET stock_quantity = stock_quantity - :amount
WHERE id = :id
');
$update->execute([
':amount' => $amount,
':id' => $productId,
]);
$pdo->commit();
} catch (Throwable $e) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
throw $e;
}FOR UPDATE must be used inside a transaction. Its exact locking behavior depends on the database engine, indexes, isolation level, and query plan. MySQL documents locking reads for InnoDB at SELECT … FOR UPDATE.
Reducing stock for several order lines
Process every line inside one transaction. If any product lacks stock, roll back every earlier decrement.
try {
$pdo->beginTransaction();
// Sort cart items by product ID before this loop in high-concurrency systems.
$stmt = $pdo->prepare('
UPDATE products
SET stock_quantity = stock_quantity - :quantity
WHERE id = :product_id
AND stock_quantity >= :quantity
');
foreach ($cartItems as $item) {
if ((int) $item['quantity'] < 1) {
throw new InvalidArgumentException('Invalid line quantity.');
}
$stmt->execute([
':quantity' => (int) $item['quantity'],
':product_id' => (int) $item['product_id'],
]);
if ($stmt->rowCount() !== 1) {
throw new RuntimeException(
'Insufficient stock for product ' . (int) $item['product_id']
);
}
}
// Insert the order and its order-item records here.
$pdo->commit();
} catch (Throwable $e) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
throw $e;
}Update product IDs in a consistent order to reduce deadlocks when two orders contain overlapping products. Keep the transaction short, avoid network calls inside it, and retry the complete transaction when the database reports a deadlock. Never retry only the last statement.
Laravel equivalent
In Laravel, validate and cast the amount before using it in a raw arithmetic expression:
Rank #4
use IlluminateSupportFacadesDB;
$amount = (int) $amount;
if ($amount < 1) {
throw new InvalidArgumentException('Quantity must be positive.');
}
$updated = DB::table('products')
->where('id', $productId)
->where('stock_quantity', '>=', $amount)
->update([
'stock_quantity' => DB::raw(
'stock_quantity - ' . $amount
),
]);
if ($updated !== 1) {
throw new RuntimeException('Product not found or insufficient stock.');
}Do not place arbitrary request text inside DB::raw(). For related writes, use DB::transaction():
DB::transaction(function () use ($productId, $amount, $orderId) {
$updated = DB::table('products')
->where('id', $productId)
->where('stock_quantity', '>=', $amount)
->update([
'stock_quantity' => DB::raw(
'stock_quantity - ' . (int) $amount
),
]);
if ($updated !== 1) {
throw new RuntimeException('Insufficient stock.');
}
DB::table('order_items')->insert([
'order_id' => $orderId,
'product_id' => $productId,
'quantity' => $amount,
]);
});Laravel documents automatic commit and rollback for DB::transaction(), as well as retry handling for deadlocks. See the Laravel database documentation.
Reservations, cancellations, and returns
“Reduce stock” can describe several different business operations:
- Permanent deduction: inventory is consumed when an order is confirmed or shipped.
- Reservation: available inventory decreases while on-hand inventory remains distinguishable from reserved units.
- Cart hold: a temporary reservation expires and is released.
- Cancellation or return: inventory is restored, subject to the actual fulfillment state.
- Adjustment: an administrator records a controlled stock correction.
- Component consumption: one sale reduces several component SKUs.
- Warehouse deduction: a specific location, rather than the product globally, supplies the item.
For reservations, separate values such as on_hand_quantity, reserved_quantity, and available quantity. For multiple warehouses, store a row keyed by (product_id, warehouse_id) and update the chosen location. A simple product-level column is suitable only for simple, single-location inventory.
Do not blindly add stock during cancellation. A duplicate webhook, retry, or repeated cancellation can restore the same units twice. Record a unique cancellation or inventory-movement identifier and make restoration idempotent.
Payment and duplicate requests
A database transaction cannot roll back a payment that has already succeeded at an external provider. Payment and inventory are separate systems. A practical workflow is to create a pending order, reserve or deduct stock according to your policy, use an idempotency key for payment, and release or restore stock when an order expires or is cancelled.
Also protect checkout against browser double-clicks, network retries, repeated webhooks, and client timeouts. The conditional update prevents an invalid negative quantity, but it cannot know whether two identical requests represent one intended order or two legitimate orders. Use a unique order or reservation identifier and idempotent processing.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Schema and database safeguards
An unsigned quantity column or a supported CHECK (stock_quantity >= 0) constraint can provide an additional invariant. However, neither replaces the conditional update. The conditional statement lets the application report “insufficient stock” instead of relying on a database error. Constraint enforcement can vary across MySQL-compatible versions and deployments, so verify the behavior of your target database.
Confirm that all participating writes use the same database connection, that the tables are transactional, and that autocommit and read/write routing are configured as expected. Some DDL statements can cause implicit commits, and nontransactional tables cannot participate in rollback in the same way as InnoDB tables. See MySQL’s InnoDB transaction model.
Quick Recap
Testing checklist
- Deduct one unit from a product with stock.
- Deduct exactly the available quantity.
- Request more than available.
- Use a missing product ID.
- Submit zero, negative, decimal, missing, and very large quantities.
- Run two concurrent requests competing for the final unit; only one should succeed.
- Force order-item insertion to fail and verify that stock is restored.
- Submit the same checkout or webhook twice and verify idempotency.
- Cancel or restore the same order twice and verify that stock is not doubled.
- Test deadlock handling for multi-product orders.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
