Transactions

Transactions

Transactions, Savepoints and Deadlocks

beginTransaction() suspends autocommit until commit() or rollBack() (Transactions and Locking). In a checkout, the order row, its lines and every stock decrement happen together or not at all. The guard AND stock >= ? makes check and change one atomic statement; a rowCount() of 0 means empty.

A checkout that decrements stock inside one transactionPHP

<?php
$pdo = require 'db.php';
function checkout(PDO $pdo, int $customerId, array $cart): int {   // [product id => quantity]
  $pdo->beginTransaction();
  try {
    $pdo->prepare('INSERT INTO orders (customer_id, ordered_at) VALUES (?, NOW())')
        ->execute([$customerId]);
    $orderId = (int) $pdo->lastInsertId();
    $take = $pdo->prepare('UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?');
    $line = $pdo->prepare('INSERT INTO order_items SELECT ?, id, ?, price FROM products
                           WHERE id = ?');
    foreach ($cart as $id => $qty) {
      $take->execute([$qty, $id, $qty]);
      if ($take->rowCount() === 0) throw new RangeException("product $id is out of stock");
      $line->execute([$orderId, $qty, $id]);
    }
    return $pdo->commit() ? $orderId : 0;
  } catch (Throwable $e) {
    $pdo->rollBack();                        // undoes the order row and every decrement
    throw $e;
  }
}
$stock = fn() => implode('/', $pdo->query('SELECT stock FROM products WHERE id IN (1, 3)')
                                   ->fetchAll(PDO::FETCH_COLUMN));
echo 'order ', checkout($pdo, 2, [1 => 2]), ' placed, stock ', $stock(), "\n";
try { checkout($pdo, 2, [1 => 1, 3 => 1]); }
catch (RangeException $e) { echo "rolled back: {$e->getMessage()}, stock ", $stock(), "\n"; }

Output

order 10 placed, stock 23/0
rolled back: product 3 is out of stock, stock 23/0

Products 1 and 3 started with 25 and 0 copies. The second cart had taken one copy of product 1 before product 3 failed; the rollback returned it. For savepoints, send the SQL: $pdo->exec('SAVEPOINT line'), then ROLLBACK TO SAVEPOINT line. Keep DDL out: CREATE TABLE commits implicitly, inTransaction() turns false, and commit() then throws "There is no active transaction". When two checkouts lock rows in opposite order, InnoDB kills one with error 1213, SQLSTATE 40001 (Subsection 3.14.8). Retry checkout() two or three times on 1213 and on lock-wait timeout 1205, and sort the cart by product id so locks are always taken in the same order.

MENU