Transactions

Transactions

The Database exposes one transaction entry point:

$db->transaction(Closure $closure, int $attempt = 1, bool $testMode = false): bool;

The closure receives the Database instance as its only argument. The transaction is committed when the closure returns and rolled back when it throws.

Commit on success

$db->transaction(function ($db) {
    $db->create('orders', ['user_id' => 5, 'total' => 199.90]);
    $orderId = $db->insertId();

    $db->create('order_items', [
        'order_id' => $orderId,
        'sku'      => 'X-1',
        'qty'      => 1,
    ]);
});

If both inserts succeed, the transaction commits and the helper returns true.

Rollback on exception

When the closure throws, the current transaction is rolled back and the original throwable is wrapped in DatabaseException:

use InitORM\Database\Exceptions\DatabaseException;

try {
    $db->transaction(function ($db) {
        $db->create('orders', ['user_id' => 5, 'total' => -1]);
        throw new \RuntimeException('payment declined');
    });
} catch (DatabaseException $e) {
    $message  = $e->getMessage();
    // "Transaction failed after 1 attempt(s): payment declined"

    $original = $e->getPrevious();
    // \RuntimeException 'payment declined'
}

📝 This was a regression in earlier versions: failures used to be swallowed and the caller got back a bare false. In v3 the original error is always reachable via getPrevious().

Retry transient failures

Set $attempt > 1 to retry the closure on failure. Each attempt opens a fresh transaction:

$db->transaction(function ($db) {
    // Some operation that might deadlock under contention.
    $db->update('counters', ['value' => DB::raw('value + 1')], ['id' => 1]);
}, attempt: 5);

• Any attempt that succeeds → helper returns true and the rest are skipped.
• All attempts fail → the last error is wrapped and re-thrown.
• $attempt < 1 throws DatabaseInvalidArgumentException (silent no-op is worse than a loud failure).

$db->transaction(fn () => null, attempt: 0);
// DatabaseInvalidArgumentException:
// "The number of transaction attempts cannot be less than 1."

testMode — roll back even on success

Pass testMode: true to always roll back. Useful for integration tests that want to exercise the closure without persisting changes:

$db->transaction(function ($db) {
    $db->create('users', ['name' => 'TEMP', 'email' => 't@example.com']);
}, testMode: true);

// The INSERT was rolled back — table is unchanged.
self::assertCount(0, $db->read('users')->asAssoc()->rows());

Nested transactions

PDO does not natively support nested transactions. Starting one inside another throws:

$db->transaction(function ($db) {
    $db->transaction(function ($db) {
        // throws — wrapped in DatabaseException:
        // "Cannot start a transaction while another is already in progress."
    });
});

If you need savepoints (per-section partial rollback), drop down to raw SQL:

$db->transaction(function ($db) {
    $db->query('SAVEPOINT sp_audit');
    try {
        $db->create('audit', [...]);
    } catch (\Throwable $e) {
        $db->query('ROLLBACK TO sp_audit');
        // continue with the rest of the transaction
    }
    $db->query('RELEASE SAVEPOINT sp_audit');
});

Caveats

DDL implicit commits

Most databases (MySQL, MariaDB, Oracle) auto-commit when they encounter a DDL statement like CREATE TABLE or ALTER TABLE. After that, rollBack() has nothing to roll back.

The helper guards rollBack() with inTransaction() so it doesn't error out, but the rolled-back transaction is no longer atomic — anything before the DDL is already on disk. Avoid mixing DDL into a retried transaction.

Long-running closures

The closure runs synchronously inside the transaction. If it makes external API calls or sleeps, every other client waiting on the same rows is blocked for the duration. Move slow work out of the transaction:

// ❌ payment provider blocks the lock for seconds
$db->transaction(function ($db) use ($paymentGateway) {
    $db->update('orders', ['status' => 'paying'], ['id' => 1]);
    $result = $paymentGateway->charge(...);  // slow HTTP call
    $db->update('orders', ['status' => $result], ['id' => 1]);
});

// ✅ flip status outside, write result inside
$db->update('orders', ['status' => 'paying'], ['id' => 1]);
$result = $paymentGateway->charge(...);
$db->transaction(fn ($db) => $db->update('orders', ['status' => $result], ['id' => 1]));

Connection drops

If the underlying connection dies mid-transaction (network blip, DB restart), rollBack() itself can throw. The helper catches this and surfaces the rollback failure alongside the original error:

Rollback failed after a failed transaction attempt: <rollback msg> (original: <original msg>)

The original throwable remains accessible via getPrevious().

The closure receives $this

The Database passed into the closure is the same instance you called transaction() on. Builder state from before the transaction is preserved; CRUD calls inside reset state as usual.

If you want a clean builder for the closure, create one explicitly:

$db->transaction(function ($db) {
    $tx = $db->withFreshBuilder();
    $tx->where('id', '=', 5)->update('users', [...]);
});

See also

• CRUD Operations — the methods you'll typically call inside the closure
• Recipes — common multi-step transaction patterns (atomic counter, queued event publishing)
• FAQ — "why does my DDL not roll back?", "can I nest transactions?", "what is the isolation level?"