MySQL Transactions in PHP (Commit and Rollback)

A transaction means: “either everything succeeds, or nothing changes.”

This is critical in real apps:

• placing an order (order + order_items + payment log)
• transferring money (debit + credit)
• updating inventory + creating invoice

The problem without transactions

If you insert the order, then fail inserting order_items, your database becomes messy.

Transaction flow

flowchart TD
  A[Begin Transaction] --> B[Query 1]
  B --> C[Query 2]
  C --> D{All OK?}
  D -->|Yes| E[Commit]
  D -->|No| F[Rollback]

Example: Create order + items

<?php
require_once "config.php";

try {
  $pdo->beginTransaction();

  $stmt = $pdo->prepare("INSERT INTO orders (user_id, total) VALUES (:user_id, :total)");
  $stmt->execute(["user_id" => $userId, "total" => $total]);

  $orderId = (int)$pdo->lastInsertId();

  $itemStmt = $pdo->prepare(
    "INSERT INTO order_items (order_id, product_id, qty, price) VALUES (:order_id, :product_id, :qty, :price)"
  );

  foreach ($items as $it) {
    $itemStmt->execute([
      "order_id" => $orderId,
      "product_id" => $it["product_id"],
      "qty" => $it["qty"],
      "price" => $it["price"],
    ]);
  }

  $pdo->commit();
  echo "Order created successfully";
} catch (Throwable $e) {
  $pdo->rollBack();
  echo "Order failed safely (no partial data saved)";
}
?>

Pro tip

Use transactions whenever multiple queries must stay consistent.

Next: Database indexing, why some queries become slow and how to fix them.