How do PDO transactions and error modes work in PHP?

Two things fix this: turn on exceptions and wrap related writes in a transaction.

1. Error mode

In PHP 8 the default PDO::ATTR_ERRMODE is already ERRMODE_EXCEPTION (changed in PHP 8.0; in PHP 7 it was silent). Set it explicitly anyway so the code is clear and portable:

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

2. Transaction

try {
    $pdo->beginTransaction();

    $pdo->prepare('INSERT INTO orders (user_id, total) VALUES (?, ?)')->execute([$userId, $total]);
    $orderId = (int) $pdo->lastInsertId();

    $stmt = $pdo->prepare('INSERT INTO order_items (order_id, sku, qty) VALUES (?, ?, ?)');
    foreach ($items as $it) {
        $stmt->execute([$orderId, $it['sku'], $it['qty']]);
    }

    $pdo->commit();
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    error_log($e->getMessage());
    throw $e;
}

Either everything is saved or nothing is.

3. Stock/balance updates need locking

$stmt = $pdo->prepare('SELECT stock FROM products WHERE id = ? FOR UPDATE');

SELECT ... FOR UPDATE inside the transaction locks the row so two buyers cannot both take the last item. Or use an atomic update: UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ? and check rowCount().

Pitfalls

• MySQL DDL statements (CREATE TABLE, ALTER) cause an implicit commit; do not mix them in a transaction.
• The table engine must be InnoDB. MyISAM ignores transactions.
• lastInsertId() must be called on the same connection, before commit/other inserts.
• Keep transactions short. Do not call external APIs or send email inside them.
• PDO does not support nested transactions; use savepoints with raw SAVEPOINT statements if you need them.
• Deadlocks (error 1213) can happen; retry the whole transaction a couple of times.