PHP Transactions

In this page:

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

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
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE accounts (id INTEGER, balance INTEGER)");
$db->exec("INSERT INTO accounts VALUES (1, 100), (2, 50)");
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
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE accounts (id INTEGER, balance INTEGER)");
$db->exec('BEGIN');
$db->exec("INSERT INTO accounts VALUES (1, 100)");
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
$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');
$result = $db->query("SELECT COUNT(*) as c FROM accounts");
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
$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');
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
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE accounts (id INTEGER, balance INTEGER)");
try {
    $db->exec('BEGIN');
    $db->exec("UPDATE accounts SET balance = balance - 50 WHERE id = 1");
    $db->exec('COMMIT');
} catch (Exception $e) {
    $db->exec('ROLLBACK');
    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