PHP PDO::rollBack Method

last modified April 19, 2025

The PDO::rollBack method is used to undo changes made during a transaction. It reverts the database to its state before the transaction began.

Basic Definition

PDO::rollBack rolls back the current transaction. It only works if the database supports transactions and autocommit is turned off.

Syntax: public PDO::rollBack(): bool. Returns true on success or false on failure. Throws PDOException if no active transaction exists.

Basic Transaction Rollback Example

This shows the simplest usage of rollBack when an error occurs.

basic_rollback.php

<?php

declare(strict_types=1);

try { $pdo = new PDO(‘mysql:host=localhost;dbname=testdb’, ‘user’, ‘password’); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->beginTransaction();

$pdo->exec("INSERT INTO users (name, email) VALUES ('John', 'john@example.com')");

// Simulate an error
throw new Exception("Something went wrong");

$pdo->commit();

} catch (Exception $e) { $pdo->rollBack(); echo “Transaction rolled back: " . $e->getMessage(); }

This starts a transaction, inserts a record, then simulates an error. The catch block calls rollBack to undo the insert. The database remains unchanged.

Nested Transactions with Rollback

This demonstrates rollBack behavior with nested transactions.

nested_rollback.php

<?php

declare(strict_types=1);

try { $pdo = new PDO(‘mysql:host=localhost;dbname=testdb’, ‘user’, ‘password’); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Outer transaction
$pdo->beginTransaction();
$pdo->exec("INSERT INTO logs (message) VALUES ('Starting process')");

try {
    // Inner transaction
    $pdo->beginTransaction();
    $pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    
    // Simulate error in inner transaction
    throw new Exception("Transfer failed");
    
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack(); // Rolls back only the inner transaction
    echo "Inner transaction failed: " . $e->getMessage();
}

$pdo->commit(); // Commits the outer transaction
echo "Outer transaction completed";

} catch (PDOException $e) { $pdo->rollBack(); // Rolls back everything if outer transaction fails echo “Error: " . $e->getMessage(); }

This shows nested transactions. The inner rollBack only undoes the inner operations. The outer transaction can still commit successfully.

Conditional Rollback Example

This demonstrates using rollBack based on business logic conditions.

conditional_rollback.php

<?php

declare(strict_types=1);

try { $pdo = new PDO(‘mysql:host=localhost;dbname=bank’, ‘user’, ‘password’); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->beginTransaction();

// Withdraw from account 1
$pdo->exec("UPDATE accounts SET balance = balance - 200 WHERE id = 1");

// Check if balance went negative
$stmt = $pdo->query("SELECT balance FROM accounts WHERE id = 1");
$balance = $stmt->fetchColumn();

if ($balance < 0) {
    $pdo->rollBack();
    echo "Transaction rolled back due to insufficient funds";
} else {
    // Deposit to account 2
    $pdo->exec("UPDATE accounts SET balance = balance + 200 WHERE id = 2");
    $pdo->commit();
    echo "Transaction completed successfully";
}

} catch (PDOException $e) { $pdo->rollBack(); echo “Error: " . $e->getMessage(); }

This performs a conditional rollback if an account balance goes negative. The business logic determines whether to commit or rollback the transaction.

Rollback with Savepoints

This shows using savepoints for partial transaction rollbacks.

savepoint_rollback.php

<?php

declare(strict_types=1);

try { $pdo = new PDO(‘mysql:host=localhost;dbname=testdb’, ‘user’, ‘password’); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->beginTransaction();

// First operation
$pdo->exec("INSERT INTO orders (product, quantity) VALUES ('Laptop', 1)");
$pdo->exec("SAVEPOINT point1");

// Second operation
$pdo->exec("UPDATE inventory SET stock = stock - 1 WHERE product = 'Laptop'");

// Check stock level
$stmt = $pdo->query("SELECT stock FROM inventory WHERE product = 'Laptop'");
$stock = $stmt->fetchColumn();

if ($stock < 0) {
    $pdo->exec("ROLLBACK TO SAVEPOINT point1");
    echo "Partial rollback performed, order kept but inventory not updated";
}

$pdo->commit();

} catch (PDOException $e) { $pdo->rollBack(); echo “Error: " . $e->getMessage(); }

This creates a savepoint after the first operation. If the second operation fails a check, it rolls back to the savepoint rather than the full transaction.

Rollback in Batch Processing

This demonstrates using rollBack when processing batches of records.

batch_rollback.php

<?php

declare(strict_types=1);

try { $pdo = new PDO(‘mysql:host=localhost;dbname=testdb’, ‘user’, ‘password’); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$records = [
    ['name' => 'Alice', 'email' => 'alice@example.com'],
    ['name' => 'Bob', 'email' => 'bob@example.com'],
    ['name' => '', 'email' => 'invalid'], // Invalid record
    ['name' => 'Charlie', 'email' => 'charlie@example.com']
];

$pdo->beginTransaction();
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");

foreach ($records as $record) {
    if (empty($record['name']) || empty($record['email'])) {
        $pdo->rollBack();
        throw new Exception("Invalid record found, rolling back entire batch");
    }
    
    $stmt->execute([$record['name'], $record['email']]);
}

$pdo->commit();
echo "Batch processed successfully";

} catch (Exception $e) { echo “Error: " . $e->getMessage(); }

This processes multiple records in a transaction. If any record is invalid, it rolls back the entire batch. This ensures data consistency.

Rollback with Multiple Databases

This shows handling rollback across multiple database connections.

multi_db_rollback.php

<?php

declare(strict_types=1);

try { // First database connection $pdo1 = new PDO(‘mysql:host=localhost;dbname=db1’, ‘user’, ‘password’); $pdo1->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Second database connection
$pdo2 = new PDO('mysql:host=localhost;dbname=db2', 'user', 'password');
$pdo2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo1->beginTransaction();
$pdo2->beginTransaction();

try {
    $pdo1->exec("INSERT INTO orders (product) VALUES ('Phone')");
    $orderId = $pdo1->lastInsertId();
    
    $pdo2->exec("INSERT INTO shipments (order_id) VALUES ($orderId)");
    
    // Simulate error
    throw new Exception("Shipping service unavailable");
    
    $pdo1->commit();
    $pdo2->commit();
} catch (Exception $e) {
    $pdo1->rollBack();
    $pdo2->rollBack();
    echo "Distributed transaction rolled back: " . $e->getMessage();
}

} catch (PDOException $e) { echo “Connection error: " . $e->getMessage(); }

This coordinates transactions across two databases. If any operation fails, both transactions are rolled back. This maintains consistency across systems.

Rollback with Error Logging

This demonstrates logging errors when rolling back a transaction.

logging_rollback.php

<?php

declare(strict_types=1);

try { $pdo = new PDO(‘mysql:host=localhost;dbname=testdb’, ‘user’, ‘password’); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->beginTransaction();

$pdo->exec("UPDATE products SET stock = stock - 5 WHERE id = 101");
$pdo->exec("INSERT INTO order_items (product_id, quantity) VALUES (101, 5)");

// Check if product exists
$stmt = $pdo->query("SELECT COUNT(*) FROM products WHERE id = 101");
if ($stmt->fetchColumn() == 0) {
    $pdo->rollBack();
    
    // Log the error
    $errorMsg = "Attempted to order non-existent product 101";
    $pdo->exec("INSERT INTO error_log (message) VALUES ('$errorMsg')");
    
    throw new Exception($errorMsg);
}

$pdo->commit();
echo "Order processed successfully";

} catch (Exception $e) { echo “Error: " . $e->getMessage(); }

This rolls back the main transaction but logs the error to a separate table. The error logging happens after rollback to ensure it’s always recorded.

Best Practices for rollBack

• Error Handling: Always use try-catch with transactions.

• Transaction Scope: Keep transactions as short as possible.

• Validation: Validate data before starting transactions.

• Nested Transactions: Understand database-specific behavior.

• Testing: Test rollback scenarios thoroughly.

Source

PHP PDO::rollBack Documentation

This tutorial covered the PDO::rollBack method with practical examples showing different scenarios where transaction rollback is necessary.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all PHP PDO Functions.