How to Use PDO Transactions in PHP

Database transactions are critical for maintaining data integrity by ensuring a group of SQL queries execute as a single, indivisible unit. This article explains how database transactions work in PHP using the PHP Data Objects (PDO) extension. You will learn the core concepts of transactions, the essential PDO methods required to control them, and how to implement robust error handling using try-catch blocks to prevent data corruption.

Understanding Database Transactions

A database transaction operates on the principle of Atomicity (the “A” in ACID). This means that when you have multiple database operations dependent on one another, they must either all succeed together, or all fail together. If any single query in the sequence fails, the entire transaction is rolled back, leaving the database in its original state.

A classic example is a bank transfer: money must be debited from Account A and credited to Account B. If the credit operation fails, the debit operation must be undone.

The Three Key PDO Methods

PDO provides three simple methods to manage transactions:

  1. beginTransaction(): Disables auto-commit mode. From this point onward, any changes made to the database are temporary and visible only to the current database connection.
  2. commit(): Saves all changes made during the transaction permanently to the database and restores auto-commit mode.
  3. rollBack(): Discards all changes made during the transaction, reverting the database to the state it was in before beginTransaction() was called, and restores auto-commit mode.

Step-by-Step Implementation in PHP

To use transactions safely, you must configure PDO to throw exceptions when errors occur. This allows you to catch errors in a try-catch block and trigger a rollback.

Here is a practical example of a bank transfer transaction:

<?php
$dsn = 'mysql:host=localhost;dbname=bank_db;charset=utf8mb4';
$username = 'root';
$password = '';

try {
    // 1. Establish connection and enable exception-based error handling
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);

    // 2. Start the transaction
    $pdo->beginTransaction();

    // 3. Perform Operation 1: Deduct money from Account A
    $stmt1 = $pdo->prepare("UPDATE accounts SET balance = balance - :amount WHERE id = :from_id");
    $stmt1->execute(['amount' => 500, 'from_id' => 1]);

    // 4. Perform Operation 2: Add money to Account B
    $stmt2 = $pdo->prepare("UPDATE accounts SET balance = balance + :amount WHERE id = :to_id");
    $stmt2->execute(['amount' => 500, 'to_id' => 2]);

    // 5. If both queries succeeded, commit the transaction
    $pdo->commit();
    echo "Transaction successful. Funds transferred.";

} catch (Exception $e) {
    // 6. If any error occurred, roll back the changes
    if (isset($pdo) && $pdo->inTransaction()) {
        $pdo->rollBack();
    }
    echo "Transaction failed: " . $e->getMessage();
}

Important Considerations