PDO Transactions

Also Known As

TL;DR

Explanation

beginTransaction() disables auto-commit. commit() writes all changes atomically. rollBack() undoes everything since beginTransaction(). PDO throws a PDOException if beginTransaction() is called when already in a transaction. Savepoints (SAVEPOINT name / ROLLBACK TO name) allow partial rollbacks within a transaction. MySQL requires InnoDB tables — MyISAM does not support transactions.

Diagram

sequenceDiagram
    participant PHP
    participant MySQL
    PHP->>MySQL: beginTransaction()
    PHP->>MySQL: UPDATE accounts SET balance=balance-100
    PHP->>MySQL: UPDATE accounts SET balance=balance+100
    alt success
        PHP->>MySQL: commit()
        MySQL-->>PHP: OK
    else exception
        PHP->>MySQL: rollBack()
        MySQL-->>PHP: reverted
    end

Common Misconception

Why It Matters

Common Mistakes

• Forgetting rollBack() in the catch block — failed transactions hold locks until the connection closes.
• Using MyISAM tables which silently ignore transaction boundaries.
• Opening long-running transactions that hold row locks and cause deadlocks under concurrency.

Avoid When

• Avoid long-running transactions — they hold row locks and cause deadlocks under concurrency.
• Do not open a transaction and then make external HTTP calls before committing — network failures leave transactions open.

When To Use

• Use transactions for any operation that modifies multiple rows or tables that must succeed or fail together.
• Use transactions for financial operations, inventory management, and any multi-step state change.

Code Examples

// No transaction — partial failure leaves DB inconsistent
$pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')
    ->execute([$amount, $fromId]);
// Exception here? $fromId debited, $toId never credited.
$pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')
    ->execute([$amount, $toId]);

$pdo->beginTransaction();
try {
    $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')
        ->execute([$amount, $fromId]);
    $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')
        ->execute([$amount, $toId]);
    $pdo->commit();
} catch (\Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

References

• ↗ https://www.php.net/manual/en/pdo.begintransaction.php

Tags

Related Terms