PHP and Databases

Using Database Transactions in PHP with PDO

Learn how PDO transactions keep related database changes consistent with commit and rollback.

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.