Transaction management
Learn to manage transactions with beginTransaction, commit and rollBack.
What is a transaction?
Running several related queries as a single unit is called a transaction. Either all of them succeed, or none of them are applied.
beginTransaction() starts it; commit() makes the changes permanent; rollBack() reverts everything.
This pattern protects data integrity in all-or-nothing operations like money transfers: if you cannot deposit after withdrawing, the money is not lost.
The try/catch pattern
The typical transaction pattern: in the try block you call beginTransaction(), run the operation queries and then commit(); if anything throws, the catch block calls rollBack().
By default PDO errors are silent; $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION) turns them into exceptions.
This way, even a single failing query reverts all the changes.
Exercise Transfer money inside a transaction
In the skeleton below, use try/beginTransaction/commit to subtract 30 from 'Alex' and add 30 to 'Sam'. In the catch block, call rollBack(). Then fetch Sam's balance with SELECT and print it.
Show example solution
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE accounts (id INTEGER PRIMARY KEY AUTOINCREMENT, owner TEXT, balance INTEGER)");
$pdo->exec("INSERT INTO accounts (owner, balance) VALUES ('Alex', 100)");
$pdo->exec("INSERT INTO accounts (owner, balance) VALUES ('Sam', 50)");
try {
$pdo->beginTransaction();
$pdo->exec("UPDATE accounts SET balance = balance - 30 WHERE owner = 'Alex'");
$pdo->exec("UPDATE accounts SET balance = balance + 30 WHERE owner = 'Sam'");
$pdo->commit();
} catch (Exception $e) {
$pdo->rollBack();
}
$stmt = $pdo->query("SELECT balance FROM accounts WHERE owner = 'Sam'");
echo $stmt->fetchColumn() . "\n";Test yourself
-
Match transaction methods
- beginTransaction()
- Starts the transaction
- commit()
- Makes changes permanent
- rollBack()
- Reverts changes
- try/catch
- Catches errors
-
When should rollBack() be called?
- When an error occurs
- When the operation succeeds
- When the connection opens
- When the statement is prepared
Show answer
When an error occurs — On error, rollBack() reverts all changes.
-
Revert on error
try { $pdo->beginTransaction(); $pdo->commit(); } catch (Exception $e) { $pdo->____(); }Show answer
try { $pdo->beginTransaction(); $pdo->commit(); } catch (Exception $e) { $pdo->rollBack(); }