Transaction handling in the connection class

Intermediate5 min read·fw-06-004

sql

Concept

Transaction handling ensures that a group of database operations either ALL succeed or ALL fail together. Without transactions, a crash between related operations leaves the database in an inconsistent state.

ACID: Transactions provide Atomicity (all or nothing), Consistency (DB constraints enforced), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes).

PDO transaction API:

beginTransaction(): Start a transaction.
commit(): Permanently write changes.
rollBack(): Undo all changes since beginTransaction().
inTransaction(): Returns true if a transaction is active.

Wrapping in try/catch: Always wrap commit() in a try block. If any operation throws, catch it, call rollBack(), then re-throw the exception.

Nested transactions (savepoints): PDO doesn't support real nested transactions. Instead, use SQL savepoints: SAVEPOINT sp1, ROLLBACK TO SAVEPOINT sp1, RELEASE SAVEPOINT sp1. Track nesting depth manually.

Connection::transaction(callable $callback) helper: The framework's convenience method — opens transaction, runs callback, commits or rolls back automatically. The user never calls beginTransaction/commit manually.

Deadlocks: When two transactions each hold a lock the other needs. The DB detects and kills one (throws error). Retry on deadlock: catch PDOException with error code 1213 (MySQL) or 40P01 (PostgreSQL) and retry.

After-commit callbacks: Queue jobs or fire events only AFTER the transaction commits. Registered via DB::afterCommit() in Laravel. Prevents dispatching a job for a record that might still roll back.

Code Example

php

<?php
namespace Framework\Database;

class Connection
{
    private int $transactionDepth = 0;

    // ... constructor, getPdo(), etc.

    public function transaction(callable $callback): mixed
    {
        $this->beginTransaction();
        try {
            $result = $callback($this);
            $this->commit();
            return $result;
        } catch (\Throwable $e) {
            $this->rollBack();
            throw $e;
        }
    }

    public function beginTransaction(): void
    {
        if ($this->transactionDepth === 0) {
            $this->getPdo()->beginTransaction();
        } else {
            // Nested: use savepoint
            $this->getPdo()->exec("SAVEPOINT trans_{$this->transactionDepth}");
        }
        $this->transactionDepth++;
    }

    public function commit(): void
    {
        $this->transactionDepth--;
        if ($this->transactionDepth === 0) {
            $this->getPdo()->commit();
        } else {
            // Nested: release savepoint
            $this->getPdo()->exec("RELEASE SAVEPOINT trans_{$this->transactionDepth}");
        }
    }

    public function rollBack(): void
    {
        $this->transactionDepth--;
        if ($this->transactionDepth === 0) {
            $this->getPdo()->rollBack();
        } else {
            // Nested: rollback to savepoint
            $this->getPdo()->exec("ROLLBACK TO SAVEPOINT trans_{$this->transactionDepth}");
        }
    }

    public function inTransaction(): bool
    {
        return $this->transactionDepth > 0;
    }
}

// Usage — transfer money example
$db->transaction(function(Connection $db) use ($fromId, $toId, $amount) {
    $from = $db->selectOne('SELECT * FROM accounts WHERE id = ? FOR UPDATE', [$fromId]);
    $to   = $db->selectOne('SELECT * FROM accounts WHERE id = ? FOR UPDATE', [$toId]);

    if ($from['balance'] < $amount) {
        throw new \DomainException('Insufficient funds');
    }

    $db->update('UPDATE accounts SET balance = balance - ? WHERE id = ?', [$amount, $fromId]);
    $db->update('UPDATE accounts SET balance = balance + ? WHERE id = ?', [$amount, $toId]);
    $db->insert('INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)', [$fromId, $toId, $amount]);
    // All succeed or all roll back
});

PreviousPrepared statements and parameter binding — preventing SQL injectionDatabase Connection & PDO Wrappers NextMultiple connection support — read/write splittingDatabase Connection & PDO Wrappers