I want to show you a snippet of code that looks perfectly reasonable at first glance. Imagine you're building a simple internal wallet system for a site where users can send credits to each other. You've got your PDO connection ready, and you write this logic to handle a transfer:

$amount = 50.00;
$fromUser = 12;
$toUser = 45;

// Subtract from sender
$pdo->exec("UPDATE users SET balance = balance - $amount WHERE id = $fromUser");

// Add to receiver
$pdo->exec("UPDATE users SET balance = balance + $amount WHERE id = $toUser");

echo "Transfer successful!";

If you run this during a happy-path test, it works. But here is the nightmare scenario: what happens if the server loses power, the database connection drops, or a constraint is triggered exactly between those two lines of code? The first user loses 50 credits, but the second user never receives them. The money effectively vanishes into the void. In the industry, we call this a lack of atomicity.

The Danger of Partial Execution

The problem here is that by default, PDO (and most databases) operates in "autocommit" mode. Every single exec() or execute() call is treated as a complete, permanent transaction. In a financial operation—or any operation where two or more tables must stay in sync—this is a recipe for corrupted data.

You can't just "hope" the second query works. You need a way to tell the database: "Hold onto these changes in a temporary state. If everything goes perfectly, make them permanent. If even one thing trips up, pretend none of this ever happened."

Guaranteeing Consistency with beginTransaction

To fix this, we wrap the operations in a transaction. I always recommend using a try-catch block here because transactions are useless if you don't have a plan for when things go wrong.

try {
    // 1. Turn off autocommit
    $pdo->beginTransaction();

    $pdo->exec("UPDATE users SET balance = balance - $amount WHERE id = $fromUser");
    $pdo->exec("UPDATE users SET balance = balance + $amount WHERE id = $toUser");

    // 2. If we reached here, no exceptions were thrown. Make it permanent.
    $pdo->commit();
    echo "Transfer successful!";
} catch (Exception $e) {
    // 3. Something went wrong. Undo everything since beginTransaction().
    $pdo->rollBack();
    echo "Transfer failed: " . $e->getMessage();
}

Now, if that second update fails, the rollBack() method tells the database to discard the first update. The sender gets their money back instantly. It's all or nothing.

A Quick Note on Storage Engines

I've seen developers pull their hair out because beginTransaction() didn't seem to be doing anything. If you're using MySQL, check your table engine. Transactions only work with InnoDB. If your tables are using the old MyISAM engine, MySQL will silently ignore your transaction commands and keep autocommitting. It's a legacy quirk, but it's a common trap.

When to Use This Pattern

You don't need transactions for every single query—that would add unnecessary overhead. Use them when you have "dependent writes." For example:

• Creating an order record and simultaneously decrementing product_stock.
• Creating a user account and immediately inserting a default user_profile record.
• Moving a file record from a pending table to an archived table.

If the second action depends on the first being successful to maintain the integrity of your data, wrap it in a transaction. Period.

---

📋 Practical Task

Exercise: Building a Fail-Safe Inventory Deduction System

You are tasked with creating a checkout script for an e-commerce store. You have two tables: orders and products.

Write a PHP script using PDO that does the following inside a single transaction:

1. Inserts a new row into the orders table (columns: user_id, total_price).
2. Updates the products table to subtract the quantity purchased from the stock_count for a specific product.
3. The Twist: Before committing, check if the stock_count has dropped below zero. If it has, throw a custom Exception to trigger the rollback, ensuring that an order is never created for an item that is out of stock.

Your code must include the beginTransaction(), commit(), and rollBack() methods within a try-catch block.