Transactions

Transactions

DBAL does not add a transaction abstraction — PDO's surface is already sufficient and Connection::__call() forwards every transaction method directly:

$db->beginTransaction();
$db->inTransaction();
$db->commit();
$db->rollBack();

This page collects the patterns and the foot-guns.

The basic shape

$db->beginTransaction();
try {
    $db->query(
        'INSERT INTO orders (user_id, total) VALUES (:u, :t)',
        ['u' => $userId, 't' => 99]
    );
    $db->query(
        'INSERT INTO order_items (order_id, sku, qty) VALUES (:o, :s, :q)',
        ['o' => $db->lastInsertId(), 's' => 'X-1', 'q' => 2]
    );
    $db->commit();
} catch (\Throwable $e) {
    if ($db->inTransaction()) {
        $db->rollBack();
    }
    throw $e;
}

Notes:

• Always rethrow after rolling back. Swallowing the original exception turns a database fault into silent data loss.
• The inTransaction() guard is important — rollBack() outside a transaction throws.
• beginTransaction() itself can throw if the driver does not support transactions (e.g. with ATTR_AUTOCOMMIT => true set explicitly on MySQL DDL).

A reusable helper

If you do this in many places, wrap it once:

function transactional(Connection $db, callable $work): mixed
{
    $db->beginTransaction();
    try {
        $result = $work($db);
        $db->commit();
        return $result;
    } catch (\Throwable $e) {
        if ($db->inTransaction()) {
            $db->rollBack();
        }
        throw $e;
    }
}

$orderId = transactional($db, static function (Connection $db): int {
    $db->query('INSERT INTO orders ...');
    return (int) $db->lastInsertId();
});

Nesting and savepoints

PDO does not implement nested transactions portably. If you need them, emit driver-specific SAVEPOINT SQL directly:

$db->getPDO()->exec('SAVEPOINT sp1');
try {
    $db->query('...');
    $db->getPDO()->exec('RELEASE SAVEPOINT sp1');
} catch (\Throwable $e) {
    $db->getPDO()->exec('ROLLBACK TO SAVEPOINT sp1');
    throw $e;
}

MySQL, PostgreSQL, and SQLite all support savepoints with this syntax.

Persistent connections — read this once

If you set PDO::ATTR_PERSISTENT => true, several gotchas appear:

1. Aborted transactions leak. A request that dies mid-transaction leaves the connection in an open-transaction state for the next request that picks it up. PHP's shutdown does not roll back.
2. Prepared-statement caches survive across requests on some drivers, which can confuse migrations and DDL.
3. Connection::disconnect() returns the connection to the pool rather than closing it. There is no portable way to force-close.

DBAL's 2.x default is false for exactly these reasons. If you opt in, make sure:

• Every controller path uses the try/commit + catch/rollBack pattern.
• You have a register_shutdown_function that calls rollBack() when inTransaction() is still true.

register_shutdown_function(static function () use ($db): void {
    if ($db->inTransaction()) {
        $db->rollBack();
    }
});

DDL inside transactions

• MySQL auto-commits before and after CREATE TABLE, ALTER TABLE, etc. Wrapping DDL in beginTransaction() looks like it works, but the rollback won't undo the DDL.
• PostgreSQL is fully transactional, even for DDL.
• SQLite is fully transactional, but VACUUM and a few other statements implicitly commit.

When running migrations, prefer the database's own transactional DDL guarantees over wrapping everything in beginTransaction().

What's next

• Logging for capturing what happened inside the transaction.
• Error Handling for the exception types you may catch.