Transactions — DB::transaction(), beginTransaction(), commit(), rollback()

Intermediate5 min read·lv-13-013

sqlinterview

Concept

Database transactions ensure that a group of operations either all succeed or all fail atomically. If any operation fails, the entire transaction is rolled back, leaving the database in its prior state. Transactions enforce the A (Atomicity) and I (Isolation) properties of ACID.

DB::transaction(Closure $callback, int $attempts = 1): Wraps a closure in a transaction. If an exception is thrown inside, the transaction is automatically rolled back and the exception re-thrown. If the closure returns normally, the transaction commits. $attempts enables automatic retry on deadlocks.

Manual transaction control:

DB::beginTransaction(): Start a transaction.
DB::commit(): Commit all changes.
DB::rollBack(): Undo all changes since beginTransaction.

Nested transactions (savepoints): Laravel tracks nesting level. beginTransaction inside an existing transaction creates a savepoint. rollBack() within a nested transaction rolls back to the savepoint, not the outer transaction. commit() of a nested transaction releases the savepoint.

Deadlocks and $attempts: When two transactions lock each other's rows, the database kills one (a deadlock). The $attempts parameter in DB::transaction($cb, 3) automatically retries up to 3 times on deadlock. Ensure the closure is idempotent (safe to run multiple times) when using retries.

Events in transactions: Model events fire when methods are called. If the transaction rolls back, events have already been dispatched. Consider deferring side effects (email, queue job dispatch) until after the transaction commits — use DB::afterCommit().

Code Example

php

<?php
use Illuminate\Support\Facades\DB;

// Automatic transaction — commits on success, rolls back on exception
DB::transaction(function() {
    $order = Order::create(['user_id' => 1, 'total' => 99.99]);
    $order->items()->createMany([
        ['product_id' => 1, 'quantity' => 2, 'price' => 29.99],
        ['product_id' => 2, 'quantity' => 1, 'price' => 39.99],
    ]);
    DB::table('products')->whereIn('id', [1, 2])->decrement('stock');
    // If any of the above throws, everything is rolled back
});

// With retry on deadlock
DB::transaction(function() {
    // ... transfer funds between accounts ...
    DB::table('accounts')->where('id', $fromId)->decrement('balance', $amount);
    DB::table('accounts')->where('id', $toId)->increment('balance', $amount);
}, 3); // retry up to 3 times on deadlock

// Manual control
DB::beginTransaction();
try {
    $invoice = Invoice::create($data);
    $payment = $invoice->processPayment();
    if (!$payment->isSuccessful()) {
        DB::rollBack();
        return back()->withErrors('Payment failed');
    }
    DB::commit();
    return redirect()->route('invoices.show', $invoice);
} catch (\Exception $e) {
    DB::rollBack();
    throw $e;
}

// afterCommit — defer side effects until AFTER the transaction succeeds
DB::transaction(function() use ($order) {
    $order->update(['status' => 'confirmed']);

    // This runs ONLY if the transaction commits — safe!
    DB::afterCommit(function() use ($order) {
        SendOrderConfirmation::dispatch($order->id);
    });
});
// Without afterCommit, if the transaction rolls back, the job is already dispatched

// Check current transaction level
DB::transactionLevel(); // 0 = no transaction, 1+ = inside transaction(s)

Previousupdate(), updateOrInsert(), increment(), decrement()Query Builder NextDatabase locking — sharedLock(), lockForUpdate()Query Builder