Advanced Laravel

Advanced Database: Transactions & Locking

18 min Lesson 12 of 40

Advanced Database: Transactions & Locking

Database transactions and locking mechanisms are critical for maintaining data integrity in multi-user applications. Laravel provides elegant APIs for managing transactions, implementing pessimistic and optimistic locking, and preventing deadlocks. Let's explore these advanced database concepts.

Database Transactions Fundamentals

Transactions ensure that a series of database operations either all succeed or all fail, maintaining database consistency.

use Illuminate\Support\Facades\DB;

// Basic transaction
DB::transaction(function () {
    $user = User::create(['name' => 'John Doe', 'email' => 'john@example.com']);

    Profile::create([
        'user_id' => $user->id,
        'bio' => 'Developer',
    ]);

    Subscription::create([
        'user_id' => $user->id,
        'plan' => 'premium',
    ]);
});

// Transaction with return value
$order = DB::transaction(function () use ($cartItems) {
    $order = Order::create([
        'user_id' => auth()->id(),
        'total' => $this->calculateTotal($cartItems),
    ]);

    foreach ($cartItems as $item) {
        OrderItem::create([
            'order_id' => $order->id,
            'product_id' => $item['product_id'],
            'quantity' => $item['quantity'],
            'price' => $item['price'],
        ]);

        // Decrease inventory
        Product::find($item['product_id'])->decrement('stock', $item['quantity']);
    }

    return $order;
});

// Manual transaction control
DB::beginTransaction();

try {
    $account = Account::find($accountId);
    $account->balance -= $amount;
    $account->save();

    Transaction::create([
        'account_id' => $accountId,
        'amount' => -$amount,
        'type' => 'withdrawal',
    ]);

    DB::commit();
} catch (\Exception $e) {
    DB::rollBack();
    throw $e;
}

Note: Laravel automatically rolls back transactions if an exception is thrown within the transaction closure. Manual transaction control gives you more flexibility for complex scenarios.

Nested Transactions and Savepoints

Laravel supports nested transactions using savepoints, allowing partial rollbacks within a larger transaction.

DB::transaction(function () {
    // Outer transaction
    $order = Order::create(['user_id' => 1, 'total' => 0]);

    $total = 0;

    foreach ($items as $item) {
        DB::transaction(function () use ($order, $item, &$total) {
            // Inner transaction (savepoint)
            try {
                $orderItem = OrderItem::create([
                    'order_id' => $order->id,
                    'product_id' => $item['id'],
                    'quantity' => $item['qty'],
                ]);

                $product = Product::lockForUpdate()->find($item['id']);

                if ($product->stock < $item['qty']) {
                    throw new \Exception('Insufficient stock');
                }

                $product->decrement('stock', $item['qty']);
                $total += $product->price * $item['qty'];
            } catch (\Exception $e) {
                // This inner transaction rolls back, but outer continues
                Log::warning("Failed to add item {$item['id']}: " . $e->getMessage());
            }
        });
    }

    $order->update(['total' => $total]);
});

// Transaction with custom attempts
DB::transaction(function () {
    // Critical operations
}, 5); // Retry up to 5 times on deadlock

Pessimistic Locking

Pessimistic locking locks database rows for the duration of a transaction, preventing concurrent modifications.

// Shared lock (read lock) - allows other reads but prevents writes
$user = User::where('id', 1)->sharedLock()->first();

// Exclusive lock (write lock) - prevents all other access
$product = Product::where('id', 5)->lockForUpdate()->first();

// Transfer money between accounts with locking
DB::transaction(function () use ($fromAccountId, $toAccountId, $amount) {
    // Lock both accounts in consistent order to prevent deadlock
    $accounts = Account::whereIn('id', [$fromAccountId, $toAccountId])
        ->orderBy('id')
        ->lockForUpdate()
        ->get()
        ->keyBy('id');

    $fromAccount = $accounts[$fromAccountId];
    $toAccount = $accounts[$toAccountId];

    if ($fromAccount->balance < $amount) {
        throw new \Exception('Insufficient funds');
    }

    $fromAccount->decrement('balance', $amount);
    $toAccount->increment('balance', $amount);

    TransactionLog::create([
        'from_account_id' => $fromAccountId,
        'to_account_id' => $toAccountId,
        'amount' => $amount,
    ]);
});

// Inventory management with pessimistic locking
public function decreaseStock($productId, $quantity)
{
    return DB::transaction(function () use ($productId, $quantity) {
        $product = Product::lockForUpdate()->findOrFail($productId);

        if ($product->stock < $quantity) {
            throw new InsufficientStockException();
        }

        $product->decrement('stock', $quantity);

        StockLog::create([
            'product_id' => $productId,
            'quantity' => -$quantity,
            'type' => 'sale',
        ]);

        return $product;
    });
}

Warning: Always lock records in a consistent order (e.g., by ID) to prevent deadlocks. If two transactions try to lock the same records in different orders, a deadlock can occur.

Optimistic Locking

Optimistic locking uses a version column to detect conflicts, allowing better concurrency than pessimistic locking.

// Add version column to migration
Schema::table('products', function (Blueprint $table) {
    $table->integer('version')->default(0);
});

// Model with optimistic locking
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    protected $fillable = ['name', 'price', 'stock', 'version'];

    public function decreaseStock($quantity)
    {
        $currentVersion = $this->version;

        $affected = static::where('id', $this->id)
            ->where('version', $currentVersion)
            ->where('stock', '>=', $quantity)
            ->update([
                'stock' => DB::raw("stock - {$quantity}"),
                'version' => DB::raw('version + 1'),
            ]);

        if ($affected === 0) {
            throw new OptimisticLockException('Product was modified by another transaction');
        }

        $this->refresh();
        return $this;
    }

    public function updateWithVersion(array $attributes)
    {
        $currentVersion = $this->version;

        $affected = static::where('id', $this->id)
            ->where('version', $currentVersion)
            ->update(array_merge($attributes, [
                'version' => $currentVersion + 1,
            ]));

        if ($affected === 0) {
            throw new OptimisticLockException('Record was modified by another user');
        }

        $this->refresh();
        return $this;
    }
}

// Usage in controller
public function purchase(Request $request, $productId)
{
    $maxRetries = 3;
    $attempt = 0;

    while ($attempt < $maxRetries) {
        try {
            $product = Product::findOrFail($productId);
            $product->decreaseStock($request->quantity);

            return response()->json(['message' => 'Purchase successful']);
        } catch (OptimisticLockException $e) {
            $attempt++;

            if ($attempt >= $maxRetries) {
                return response()->json([
                    'error' => 'Unable to complete purchase. Please try again.',
                ], 409);
            }

            // Small delay before retry
            usleep(100000); // 100ms
        }
    }
}

Tip: Use optimistic locking for scenarios with low conflict probability (e.g., product updates). Use pessimistic locking for high-conflict scenarios (e.g., inventory deduction, financial transactions).

Advisory Locks

Advisory locks are application-level locks that don't lock database rows but prevent concurrent execution of specific operations.

// MySQL advisory locks
use Illuminate\Support\Facades\DB;

class AdvisoryLock
{
    public static function acquire($name, $timeout = 10)
    {
        $result = DB::select(
            "SELECT GET_LOCK(?, ?) as locked",
            [$name, $timeout]
        );

        return $result[0]->locked === 1;
    }

    public static function release($name)
    {
        $result = DB::select(
            "SELECT RELEASE_LOCK(?) as released",
            [$name]
        );

        return $result[0]->released === 1;
    }

    public static function execute($name, callable $callback, $timeout = 10)
    {
        if (!static::acquire($name, $timeout)) {
            throw new \Exception("Could not acquire lock: {$name}");
        }

        try {
            return $callback();
        } finally {
            static::release($name);
        }
    }
}

// Usage example - ensure only one report generation at a time
public function generateReport($userId)
{
    return AdvisoryLock::execute(
        "generate_report_{$userId}",
        function () use ($userId) {
            // Generate report logic
            $data = $this->fetchReportData($userId);
            $pdf = $this->createPdf($data);

            return $pdf;
        },
        30 // 30 second timeout
    );
}

// PostgreSQL advisory locks
class PostgresAdvisoryLock
{
    public static function acquire($key)
    {
        // Returns true if lock acquired, false if already locked
        $result = DB::select('SELECT pg_try_advisory_lock(?) as locked', [$key]);
        return $result[0]->locked;
    }

    public static function release($key)
    {
        DB::select('SELECT pg_advisory_unlock(?)', [$key]);
    }
}

Deadlock Prevention Strategies

Implement strategies to prevent and handle deadlocks in your application.

// Strategy 1: Lock resources in consistent order
public function transferFunds($fromId, $toId, $amount)
{
    DB::transaction(function () use ($fromId, $toId, $amount) {
        // Always lock accounts in ascending ID order
        $ids = [$fromId, $toId];
        sort($ids);

        $accounts = Account::whereIn('id', $ids)
            ->orderBy('id')
            ->lockForUpdate()
            ->get()
            ->keyBy('id');

        $fromAccount = $accounts[$fromId];
        $toAccount = $accounts[$toId];

        // Perform transfer
        $fromAccount->decrement('balance', $amount);
        $toAccount->increment('balance', $amount);
    });
}

// Strategy 2: Retry on deadlock
use Illuminate\Database\QueryException;

public function processOrder($orderId)
{
    $maxAttempts = 3;
    $attempt = 0;

    while ($attempt < $maxAttempts) {
        try {
            return DB::transaction(function () use ($orderId) {
                $order = Order::lockForUpdate()->findOrFail($orderId);

                foreach ($order->items as $item) {
                    $product = Product::lockForUpdate()->find($item->product_id);
                    $product->decrement('stock', $item->quantity);
                }

                $order->update(['status' => 'processed']);
                return $order;
            });
        } catch (QueryException $e) {
            // Check if error is a deadlock (MySQL error code 1213)
            if ($e->getCode() == '40001' || strpos($e->getMessage(), 'Deadlock') !== false) {
                $attempt++;

                if ($attempt >= $maxAttempts) {
                    throw $e;
                }

                // Exponential backoff
                usleep(pow(2, $attempt) * 100000); // 200ms, 400ms, 800ms
                continue;
            }

            throw $e;
        }
    }
}

// Strategy 3: Keep transactions short
// BAD - long transaction
DB::transaction(function () {
    $order = Order::lockForUpdate()->find(1);

    // External API call in transaction!
    $payment = PaymentGateway::charge($order->total);

    $order->update(['payment_id' => $payment->id]);
});

// GOOD - short transaction
$order = Order::find(1);
$payment = PaymentGateway::charge($order->total);

DB::transaction(function () use ($order, $payment) {
    $order = Order::lockForUpdate()->find($order->id);
    $order->update(['payment_id' => $payment->id]);
});

Exercise 1: Create a seat reservation system for an event. Use pessimistic locking to ensure only one user can book a specific seat at a time. Implement proper deadlock prevention by locking seats in a consistent order.

Exercise 2: Build a banking system with account transfers. Implement both pessimistic and optimistic locking approaches. Compare their performance under concurrent load using a database seeder that simulates multiple simultaneous transfers.

Exercise 3: Create a report generation feature that uses advisory locks to ensure only one report per user can be generated at a time. If a second request comes in while a report is being generated, queue it or return an appropriate error message.