Database Transactions

Using Transactions to ensure data integrity

sequenceDiagram participant App participant DB App->>DB: Begin Transaction App->>DB: Operation 1 App->>DB: Operation 2 App->>DB: Operation 3 alt All Success App->>DB: Commit DB-->>App: Changes Saved else Any Error App->>DB: Rollback DB-->>App: Changes Reverted end

Database Transactions allow you to perform multiple database operations atomically. This means either all operations succeed or none are executed (all or nothing).

Benefits of Transactions:

Atomicity: All operations execute as one unit
Consistency: Database always remains in consistent state
Isolation: Transactions are isolated from each other
Durability: Committed changes are permanent

ACID Properties:

Atomicity: All operations either succeed or fail
Consistency: Database constraints are always respected
Isolation: Concurrent transactions are isolated from each other
Durability: Committed changes survive system failures

Use Cases:

Multiple related operations that must succeed or fail together
Financial transactions
Data consistency requirements
Complex operations with multiple steps
Error recovery

Laravel Transaction Methods:

<code>DB::transaction()</code>: Automatic transaction with automatic rollback
<code>DB::beginTransaction()</code>: Manual transaction start
<code>DB::commit()</code>: Commit transaction
<code>DB::rollBack()</code>: Rollback transaction

Examples

Using Transaction

<?php

use Illuminate\Support\Facades\DB;

DB::transaction(function () {
    $user = User::create([
        'name' => 'John Doe',
        'email' => 'john@example.com'
    ]);
    
    $user->profile()->create([
        'bio' => 'User bio',
        'avatar' => 'avatar.jpg'
    ]);
    
    // If any error occurs, all changes are rolled back
});

Using DB::transaction() for automatic transaction management.

Manual Transaction

<?php

DB::beginTransaction();

try {
    $user = User::create([...]);
    $user->profile()->create([...]);
    
    DB::commit();
} catch (\Exception $e) {
    DB::rollBack();
    throw $e;
}

Manual transaction management with try-catch.

Transaction with Retry

<?php

DB::transaction(function () {
    $user = User::create([...]);
    $user->profile()->create([...]);
}, 5); // Retry up to 5 times on deadlock

// Or with custom retry logic
$attempts = 0;
while ($attempts < 5) {
    try {
        DB::transaction(function () {
            // Operations
        });
        break;
    } catch (\Illuminate\Database\QueryException $e) {
        if ($e->getCode() == 40001) { // Deadlock
            $attempts++;
            usleep(100000); // Wait 100ms
            continue;
        }
        throw $e;
    }
}

Transaction with retry logic for handling deadlocks.

Nested Transactions

<?php

DB::transaction(function () {
    $user = User::create([...]);
    
    // Nested transaction
    DB::transaction(function () use ($user) {
        $user->profile()->create([...]);
        
        // If this fails, only this nested transaction rolls back
        // Outer transaction continues
    });
    
    $user->posts()->create([...]);
});

Nested transactions where each is independent.

Transaction with Multiple Models

<?php

DB::transaction(function () {
    $order = Order::create([
        'user_id' => auth()->id(),
        'total' => 1000
    ]);
    
    foreach ($items as $item) {
        $order->items()->create([
            'product_id' => $item['product_id'],
            'quantity' => $item['quantity'],
            'price' => $item['price']
        ]);
        
        // Update product stock
        Product::where('id', $item['product_id'])
            ->decrement('stock', $item['quantity']);
    }
    
    // Update user balance
    User::where('id', auth()->id())
        ->decrement('balance', 1000);
});

Transaction with multiple models and operations.

Transaction in Queue Jobs

<?php

class ProcessPayment implements ShouldQueue
{
    public function handle()
    {
        DB::transaction(function () {
            // Process payment
            $payment = Payment::create([...]);
            
            // Update order status
            $order = Order::find($this->orderId);
            $order->update(['status' => 'paid']);
            
            // Send notification
            Mail::to($order->user)->send(new PaymentConfirmation($payment));
        });
    }
}

Using transactions in queue jobs for atomic operations.

Transaction with Database Events

<?php

DB::transaction(function () {
    $user = User::create([...]);
    
    // Model events fire within transaction
    // If event throws exception, transaction rolls back
    
    event(new UserCreated($user));
    
    // If event listener fails, transaction rolls back
});

Transactions with model events that execute in transaction context.

Use Cases

Financial transactions that must be atomic
Multiple related operations that must succeed or fail together
Data consistency requirements
Complex operations with multiple steps
Error recovery and rollback
Concurrent operations with isolation

Common Mistakes

Forgetting to use transaction for multiple operations
Using transaction for single operations which is unnecessary
Forgetting to handle exceptions in manual transactions
Nested transactions without proper understanding
Transaction with long-running operations causing locks
Forgetting to commit in manual transactions

Best Practices

Use transaction for multiple related operations
Use DB::transaction() for automatic management
Use try-catch for manual transactions
Keep transaction scope small
Use retry logic for deadlock handling
Use transactions for critical operations
Monitor transaction timeout

Edge Cases

Nested transactions with different isolation levels
Transaction with long-running operations
Deadlock situations
Transaction with multiple database connections
Transaction rollback with model events
Transaction in testing requiring cleanup

Performance Notes

Transaction overhead is very low
Don't use transaction for single operations
Keep transaction scope small
Use indexes to reduce lock time
Use appropriate isolation levels
Monitor transaction timeout

Security Notes

Ensure transactions are used for sensitive operations
Use transactions for preventing race conditions
Ensure transaction rollback doesn't expose sensitive data
Use proper error handling in transactions

Interview Points

What is Transaction and why is it used?
What are ACID properties?
What is the difference between DB::transaction() and manual transaction?
How can you handle deadlock?
What are nested transactions and how do they work?
When should you use transaction?

Version Notes

Laravel 11.x: Improved performance in transaction execution
Laravel 11.x: Better support for nested transactions
Laravel 10.x: Improved deadlock handling
Laravel 9.x: Improved transaction retry logic