InnoDB Stock Reservations & ACID Checkout | WinCart

Flash sales and limited-inventory product drops expose every shortcut inside a shopping cart's database layer. Decrement stock too late and two concurrent buyers pay for the final unit; decrement stock when someone clicks Add to Cart and a single scraper bot can starve legitimate shoppers of your entire catalog in seconds. WinCart solves both failure modes directly inside MariaDB and MySQL InnoDB without requiring external lock servers.

Universal Variants and Integer Minor-Unit Arithmetic

Before checkout locks even run, WinCart eliminates two perennial sources of commerce bugs in Modules\Catalog and Modules\Cart:

• Universal Default Variants: Every row in products owns at least one purchasable SKU row in product_variants. Cart persistence, stock reservation queries, and order snapshot builders always join against variant_id, avoiding split code paths between simple and configurable products.
• BIGINT Minor-Unit Ledger: Unit prices, compare-at prices, coupon caps, shipping fees, and tax lines are stored as integer minor units (price_minor BIGINT in cents). Avoiding IEEE-754 floating-point arithmetic ensures subtotal, discount, and partial refund calculations never drift by a penny.

Row-Level Checkout Holds With SELECT ... FOR UPDATE

In WinCart, adding an item to cart_items (keyed by an HttpOnly UUIDv4 cart_token cookie) records buyer intent without touching reservable stock. Inventory is only held when the buyer initiates checkout, opening a bounded 15-minute payment window inside an explicit InnoDB transaction:

$this->db->transException(true)->transStart();

$variant = $this->db->query(
    'SELECT id, stock_qty, track_inventory FROM product_variants WHERE id = ? FOR UPDATE',
    [$variantId]
)->getRowArray();

$activeHolds = $this->sumUnexpiredReservations($variantId, $cartId, $nowUtc);
if ((int) $variant['track_inventory'] === 1 && ((int) $variant['stock_qty'] - $activeHolds) < $requestedQty) {
    throw new InsufficientStockException('Requested quantity exceeds available stock.');
}

$this->upsertReservation($cartId, $variantId, $requestedQty, expiresInSeconds: 900);
$this->db->transComplete();

Because SELECT ... FOR UPDATE serializes concurrent checkout entries on the exact product_variants row being purchased, two buyers racing for the last item in stock never read a stale stock_qty count. If the first buyer abandons their Stripe or PayPal tab, the expires_at timestamp on stock_reservations lapses automatically after 900 seconds without needing a cron job to restore stock_qty.

Immutable Order Snapshots and Webhook Idempotency

Payment gateways operate at-least-once: network timeouts and webhook retries routinely deliver the same payment confirmation event two or three times. WinCart protects order fulfillment across three coordinated InnoDB invariants:

Composite Unique Key on Gateway References

The payment_transactions table enforces a strict unique index across (gateway, gateway_reference_id). When a Stripe or PayPal webhook arrives, Modules\Orders checks or inserts the gateway reference inside the settlement transaction—turning duplicate deliveries into safe, idempotent acknowledgments.

Point-in-Time Catalog and Address Snapshots

Merchants rename products, adjust prices, and retire SKUs every week. Rather than relying on mutable foreign-key joins for historical accounting, order_items snapshots product_title, variant_title, sku, unit_price_minor, tax_minor, and discount_minor alongside serialized billing_address_json and shipping_address_json on orders.

Transactional Reservation Consumption

Inside the same ACID commit that writes the paid order ledger and decrements product_variants.stock_qty, WinCart deletes the cart's stock_reservations rows and dispatches Events::trigger('order.paid', $order). Review the complete table layout on the InnoDB Schema Reference or spin up a local store using the Spark CLI Quickstart.