Using Database Transactions in PHP with PDO
A database transaction groups multiple operations into one logical unit. If every operation succeeds, the application commits the changes. If something fails, it can roll them back.
Why Transactions Matter
Imagine transferring money between two accounts. The application must subtract from one account and add to another. If only one update succeeds, the data becomes inconsistent. A transaction ensures that both updates succeed together or neither is applied.
A PDO Transaction Example
try {
$pdo->beginTransaction();
$debit = $pdo->prepare(
"UPDATE accounts SET balance = balance - :amount WHERE id = :id"
);
$debit->execute(["amount" => $amount, "id" => $fromId]);
$credit = $pdo->prepare(
"UPDATE accounts SET balance = balance + :amount WHERE id = :id"
);
$credit->execute(["amount" => $amount, "id" => $toId]);
$pdo->commit();
} catch (Throwable $exception) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
throw $exception;
}
A production transfer also needs validation, sufficient-funds checks, authorization, and concurrency protection. Consider locking the relevant rows inside the transaction.
Commit and Rollback
commit() makes the transaction's changes permanent. rollBack() cancels changes made since the transaction began. PDO's inTransaction() can help determine whether a transaction is active.
Important Considerations
Transactions require a storage engine that supports them, such as InnoDB in MySQL. Keep transactions short and avoid slow external network calls while a transaction is open. Handle exceptions at an appropriate application boundary.
Conclusion
Transactions protect data consistency when multiple database operations belong together. Use them for workflows such as payments, inventory updates, and multi-table record creation.