PHP Transactions

A transaction is like an all-or-nothing deal: either every step works, or none of them count. If moving money from one account to another fails halfway, everything is put back.

In this page:

What is a Transaction?
Committing Changes
Rolling Back Changes
Transactions with Savepoints
Transactions in MySQLi

Syntax

php

try {
    $pdo->beginTransaction();
    // several queries
    $pdo->commit();       // save all changes
} catch (Exception $e) {
    $pdo->rollBack();     // undo all changes
}

What is a Transaction?

A database transaction groups multiple SQL statements into a single all-or-nothing unit — either every statement in it succeeds and is saved, or if any one fails, all of them are rolled back as if none had run.

Example: What is a Transaction?

php

<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE accounts (id INTEGER, balance INTEGER)");
$db->exec("INSERT INTO accounts VALUES (1, 100), (2, 50)");
// Print "All statements in a transaction succeed together, or none do" to the output
echo "All statements in a transaction succeed together, or none do";
?>

Committing Changes

beginTransaction() (PDO) or mysqli's begin_transaction() marks the start of a transaction, after which subsequent queries are held in a pending state rather than being permanently committed right away.

Example: Committing Changes

php

<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE accounts (id INTEGER, balance INTEGER)");
$db->exec('BEGIN');
$db->exec("INSERT INTO accounts VALUES (1, 100)");
// Print "Insert is pending, not yet committed" to the output
echo "Insert is pending, not yet committed";
?>

Rolling Back Changes

commit() permanently saves every change made since the transaction began, while rollback() discards them all — calling rollback() inside a catch block is the standard pattern for reacting to a failed step partway through.

Example: Rolling Back Changes

php

<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE accounts (id INTEGER, balance INTEGER)");
$db->exec('BEGIN');
$db->exec("INSERT INTO accounts VALUES (1, 100)");
$db->exec('ROLLBACK');
// Declare `$result`, set to `$db->query("SELECT COUNT(*) as c FROM accounts")`
$result = $db->query("SELECT COUNT(*) as c FROM accounts");
// Print a human-readable dump of `$result->fetchArray()`
print_r($result->fetchArray());
?>

Transactions with Savepoints

Transactions matter most when several related writes must succeed or fail together, like transferring money between two accounts — you never want the debit to succeed while the matching credit silently fails.

Example: Transactions with Savepoints

php

<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE accounts (id INTEGER, balance INTEGER)");
$db->exec('BEGIN');
$db->exec("UPDATE accounts SET balance = balance - 50 WHERE id = 1");
$db->exec("UPDATE accounts SET balance = balance + 50 WHERE id = 2");
$db->exec('COMMIT');
// Print "Debit and credit committed together" to the output
echo "Debit and credit committed together";
?>

Transactions in MySQLi

Wrapping database operations in a try/catch with a transaction ensures that an unexpected exception partway through a multi-step operation leaves the database in its original, consistent state rather than a half-finished one.

Example: Transactions in MySQLi

php

<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE accounts (id INTEGER, balance INTEGER)");
// Try running this block; jump to `catch` if it throws
try {
    $db->exec('BEGIN');
    $db->exec("UPDATE accounts SET balance = balance - 50 WHERE id = 1");
    $db->exec('COMMIT');
// Catch Exception $e
} catch (Exception $e) {
    $db->exec('ROLLBACK');
    // Print "Rolled back on failure" to the output
    echo "Rolled back on failure";
}
?>

Chapter Quiz — Complete all 21 topics to unlock

0/21 topics done

Complete these topics first:

← Previous Lesson PHP Stored Procedures Next Lesson → PHP Error Handling in DB