A transaction makes several statements succeed or fail as one unit (Transactions, ACID). PHP gives you three methods: beginTransaction(), commit() and rollBack().
The pattern
index.php
PHP
<?php
$pdo = require __DIR__ . '/db.php';
function placeOrder(PDO $pdo, int $customerId, int $productId, int $qty): int
{
$pdo->beginTransaction();
try {
$stmt = $pdo->prepare('SELECT price, stock FROM products WHERE id = ? FOR UPDATE'); // lock the row
$stmt->execute([$productId]);
$product = $stmt->fetch();
if (!$product || $product['stock'] < $qty) {
throw new RuntimeException('Not enough stock');
}
$pdo->prepare('UPDATE products SET stock = stock - ? WHERE id = ?')->execute([$qty, $productId]);
$total = bcmul($product['price'], (string) $qty, 2);
$pdo->prepare('INSERT INTO orders (customer_id, total) VALUES (?, ?)')->execute([$customerId, $total]);
$orderId = (int) $pdo->lastInsertId();
$pdo->commit();
return $orderId;
} catch (Throwable $e) {
$pdo->rollBack();
throw $e;
}
}
echo 'order ', placeOrder($pdo, 1, 1, 2), " placed\n";
echo 'stock now: ', $pdo->query('SELECT stock FROM products WHERE id = 1')->fetchColumn(), "\n";
try {
placeOrder($pdo, 1, 3, 1); // the backpack has no stock
} catch (RuntimeException $e) {
echo 'failed: ', $e->getMessage(), "\n";
}
echo 'orders: ', $pdo->query('SELECT COUNT(*) FROM orders')->fetchColumn(), "\n";
Output
OUTPUT
order 4 placed
stock now: 23
failed: Not enough stock
orders: 4The failed order left nothing behind: the rollback undid everything. Points to notice:
SELECT ... FOR UPDATElocks the product row, so two customers cannot both buy the last item (Locking Reads).- The
catch (Throwable)rolls back and re-throws, so the caller still sees the error. bcmul(thebcmathextension) does exact decimal arithmetic on theDECIMALstrings.
Check before you roll back
If the failure was a connection loss or an implicit commit, there may be no active transaction, and rollBack() itself throws. Guard it:
index.php
PHP
<?php
$pdo = require __DIR__ . '/db.php';
$pdo->beginTransaction();
$pdo->exec('CREATE TABLE tmp_demo (id INT)'); // DDL: MySQL commits implicitly
echo 'in transaction after DDL? ', var_export($pdo->inTransaction(), true), "\n";
try {
$pdo->rollBack();
} catch (PDOException $e) {
echo 'rollBack failed: ', $e->getMessage(), "\n";
}
$pdo->exec('DROP TABLE tmp_demo');
Output
OUTPUT
in transaction after DDL? false
rollBack failed: There is no active transactionCREATE, ALTER, DROP, TRUNCATE and some other statements end the transaction automatically in MySQL (Autocommit and Savepoints). PDO’s inTransaction() reflects that. Keep DDL out of transactions, and use if ($pdo->inTransaction()) { $pdo->rollBack(); } in catch blocks.
A reusable helper
index.php
PHP
<?php
$pdo = require __DIR__ . '/db.php';
function transaction(PDO $pdo, callable $work): mixed
{
$pdo->beginTransaction();
try {
$result = $work($pdo);
$pdo->commit();
return $result;
} catch (Throwable $e) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
throw $e;
}
}
$count = transaction($pdo, function (PDO $pdo) {
$pdo->exec("UPDATE products SET stock = stock + 10 WHERE category = 'writing'");
return $pdo->query("SELECT SUM(stock) FROM products WHERE category = 'writing'")->fetchColumn();
});
echo "writing stock is now $count\n";
Output
OUTPUT
writing stock is now 460Deadlocks and retries
Two transactions that lock the same rows in different order can deadlock. MySQL picks one as the victim, rolls it back and returns error 1213 (SQLSTATE 40001). Another transaction waiting too long for a lock gets error 1205. These errors are normal in busy systems, and the right answer is to retry the whole transaction (Deadlocks):
index.php
PHP
<?php
$pdo = require __DIR__ . '/db.php';
function retryTransaction(PDO $pdo, callable $work, int $attempts = 3): mixed
{
for ($try = 1; ; $try++) {
$pdo->beginTransaction();
try {
$result = $work($pdo);
$pdo->commit();
return $result;
} catch (PDOException $e) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
$retryable = in_array($e->errorInfo[1] ?? 0, [1213, 1205], true);
if (!$retryable || $try >= $attempts) {
throw $e;
}
usleep(random_int(20, 100) * 1000 * $try); // short random pause, then try again
echo "retrying after error {$e->errorInfo[1]} (attempt $try)\n";
}
}
}
$calls = 0;
$result = retryTransaction($pdo, function (PDO $pdo) use (&$calls) {
$calls++;
$pdo->exec("UPDATE products SET stock = stock + 1 WHERE id = 1");
if ($calls === 1) {
$deadlock = new PDOException('Deadlock found when trying to get lock; try restarting transaction'); // simulated, for the demo
$deadlock->errorInfo = ['40001', 1213, 'Deadlock found when trying to get lock; try restarting transaction'];
throw $deadlock;
}
return $pdo->query('SELECT stock FROM products WHERE id = 1')->fetchColumn();
});
echo "stock: $result after $calls attempts\n";
Output
OUTPUT
retrying after error 1213 (attempt 1)
stock: 26 after 2 attempts(In this demo the first attempt throws an error by hand. A real deadlock needs two connections. The tested transcripts of real deadlocks are in Deadlocks.) Rules for retrying:
- Retry only
1213(deadlock) and1205(lock wait timeout), not every error. - The whole transaction is retried from
beginTransaction(), with fresh reads. Never retry only the failed statement. - Keep the work inside the callback free of side effects (sending an email, charging a card), because it may run more than once.
- Limit the attempts, and add a small random delay.
- To make deadlocks rare: lock rows in a consistent order, keep transactions short, and add the indexes your
WHEREclauses need (Locks and Indexes).
What not to do
- Do not hold a transaction open while waiting for the user or calling an external API. Locks stay held, and others queue up.
- Do not rely on
PDO::ATTR_AUTOCOMMITtoggles. UsebeginTransaction(). - Do not mix MyISAM tables into a transaction. They ignore
ROLLBACK(Storage Engines).
lightbulb
One transaction, one purpose
Group the statements that must succeed together (the stock change and the order row), and commit as soon as they are done.