PDO transactions group several database writes into one all-or-nothing unit. You start one with beginTransaction(), run your statements, then call commit() to keep them. If anything goes wrong, rollBack() undoes every write since the transaction began. This tutorial builds the classic example for PDO transactions: moving money between two accounts. The debit and the credit must both succeed, or neither should. So it wraps them in a transaction and rolls back the moment a check fails, which keeps the data consistent.
Requirements for PDO transactions:
- PHP 7.0 or newer (tested on PHP 8.5.7) with the
pdo_mysqldriver, which ships enabled by default. - MySQL 8.0 or newer (tested on MySQL 8.4) using the InnoDB engine — the default. Transactions need a transactional engine; MyISAM silently ignores them.
- A command line to run the script.
How To Use PDO Transactions.
The objective is to transfer money between two accounts safely. Either both balances change, or neither does. A rollback proves that a failed transfer leaves the data untouched.
Step 1.
First, create the database and table, then seed two accounts. Save this as setup.sql and load it with mysql -u root < setup.sql. The balance column is DECIMAL, not a float, because money needs exact values.
DROP DATABASE IF EXISTS bank;
CREATE DATABASE bank CHARACTER SET utf8mb4;
USE bank;
CREATE TABLE accounts (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
balance DECIMAL(10,2) NOT NULL DEFAULT 0
);
INSERT INTO accounts (name, balance) VALUES
('Alice', 100.00),
('Bob', 50.00);Step 2.
Next, create the script, name it transfer.php, and copy in the following. The connection sets ERRMODE_EXCEPTION, so any failed query throws a PDOException. That exception is what triggers the rollback. Inside transfer(), three things happen between beginTransaction() and commit(): the sender is debited, the new balance is checked, and the recipient is credited. If the balance check throws, control jumps to the catch block, which calls rollBack() and undoes the debit.
<?php
$pdo = new PDO(
'mysql:host=localhost;dbname=bank;charset=utf8mb4',
'root',
'',
[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);
// Move money between two accounts as one all-or-nothing unit.
function transfer(PDO $pdo, int $from, int $to, string $amount): void
{
$pdo->beginTransaction();
try {
// 1. Debit the sender.
$pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')
->execute([$amount, $from]);
// 2. Reject the transfer if the sender cannot cover it.
$balance = $pdo->query("SELECT balance FROM accounts WHERE id = $from")
->fetchColumn();
if ($balance < 0) {
throw new RuntimeException("account #$from has insufficient funds");
}
// 3. Credit the recipient.
$pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')
->execute([$amount, $to]);
// Both writes succeeded, so make them permanent.
$pdo->commit();
echo "Transferred $amount from #$from to #$to.\n";
} catch (Exception $e) {
// Anything threw, so undo every write since beginTransaction().
$pdo->rollBack();
echo "Transfer failed: {$e->getMessage()} (rolled back).\n";
}
}
function show(PDO $pdo, string $label): void
{
$rows = $pdo->query('SELECT name, balance FROM accounts ORDER BY id')
->fetchAll(PDO::FETCH_KEY_PAIR);
$parts = [];
foreach ($rows as $name => $balance) {
$parts[] = sprintf('%s = %s', $name, number_format((float) $balance, 2));
}
printf("%-9s %s\n", $label, implode(' ', $parts));
}
show($pdo, 'Before:');
transfer($pdo, 1, 2, '30.00'); // Alice can cover this: it commits.
transfer($pdo, 1, 2, '500.00'); // Alice cannot: it rolls back.
show($pdo, 'After:');A few things to note here. The two UPDATE statements use placeholders, so the values are bound safely. The balance check is a plain business rule in PHP, and throwing from it is enough to abandon the whole transaction. Because rollBack() reverses the debit, the first transfer commits while the second changes nothing at all.
Step 3.
Finally, run the script from the command line.
php transfer.phpResult of the PDO transactions.
The first transfer of 30.00 commits, so Alice drops to 70.00 and Bob rises to 80.00. The second transfer of 500.00 would push Alice negative, so the balance check throws and the transaction rolls back. As a result, the final balances still read 70.00 and 80.00, which proves the failed transfer left no partial change behind:
Before: Alice = 100.00 Bob = 50.00
Transferred 30.00 from #1 to #2.
Transfer failed: account #1 has insufficient funds (rolled back).
After: Alice = 70.00 Bob = 80.00
Notes on PDO transactions:
- Exception mode is what makes this work. Without
PDO::ERRMODE_EXCEPTION, a failing query returnsfalseinstead of throwing, so thecatchnever runs and the rollback never happens. Set it on every connection. - One connection, one transaction.
beginTransaction()works on a singlePDOobject. Nesting a second transaction on the same connection throws, so finish or roll back one before starting another. - Use a real constraint as a backstop. A
CHECK (balance >= 0)on the column, or a foreign key, makes the database reject bad writes even if the PHP check is skipped. The transaction still rolls back cleanly, because the constraint violation throws aPDOException. - Store money as
DECIMAL, never a float. Floats cannot represent values like 0.10 exactly, so sums drift by fractions of a cent. Keep amounts as strings in PHP and let MySQL do the math. - This transaction reuses the same connection pattern as the rest of the PDO series. If you have not built the write side yet, start by learning to insert MySQL data with PHP PDO, then wrap those inserts in a transaction as shown above.
