Bilal Haidar

Posted on Nov 1

Understanding Database Locking and Concurrency in Laravel: A Deep Dive

When building applications that handle concurrent operations, preventing race conditions and ensuring data consistency becomes crucial. Laravel provides powerful tools like lockForUpdate() to handle these scenarios. In this post, we'll explore how to effectively use database locking in Laravel while avoiding common pitfalls like deadlocks.

Understanding Database Locking
Basic Usage of lockForUpdate()
Common Pitfalls
Best Practices
Real-World Examples
Handling Deadlocks
Performance Considerations

Understanding Database Locking

Before diving into the implementation, it's essential to understand what happens when we use lockForUpdate():

It creates a row-level lock in the database
Other transactions must wait to access locked rows until the current transaction completes
Locks are only effective within transactions
Locks are automatically released when the transaction commits or rolls back

Basic Usage

Here's the simplest way to use lockForUpdate():

DB::transaction(function () {
    $user = User::lockForUpdate()->find(1);
    $user->balance -= 100;
    $user->save();
});

However, this basic implementation might not be sufficient for real-world applications. Let's explore more robust approaches.

Common Pitfalls

1. Using Locks Without Transactions

// DON'T DO THIS - Lock won't work!
$user = User::lockForUpdate()->find(1);
$user->balance -= 100;
$user->save();

// DO THIS INSTEAD
DB::transaction(function () {
    $user = User::lockForUpdate()->find(1);
    $user->balance -= 100;
    $user->save();
});

2. Transactions That Are Too Large

// DON'T DO THIS - Transaction holds lock too long
DB::transaction(function () {
    $user = User::lockForUpdate()->find(1);

    // Bad! HTTP calls inside transaction
    $response = Http::get('external-api.com');

    // Bad! Long-running process inside transaction
    $this->processLargeFile();

    $user->balance -= 100;
    $user->save();
});

// DO THIS INSTEAD
$apiResponse = Http::get('external-api.com');
$fileData = $this->processLargeFile();

DB::transaction(function () use ($apiResponse, $fileData) {
    $user = User::lockForUpdate()->find(1);
    $user->balance -= 100;
    $user->save();
});

Best Practices

1. Keep Transactions Small and Focused

class OrderService
{
    public function processOrder(Order $order)
    {
        // First transaction: Lock and update order status
        $order = DB::transaction(function () use ($order) {
            $order = Order::lockForUpdate()->find($order->id);
            $order->status = 'processing';
            $order->save();
            return $order;
        });

        // Second transaction: Process inventory
        DB::transaction(function () use ($order) {
            foreach ($order->items as $item) {
                $inventory = Inventory::lockForUpdate()->find($item->inventory_id);
                $inventory->quantity -= $item->quantity;
                $inventory->save();
            }
        });

        // Non-transactional operations
        $this->sendOrderConfirmation($order);
    }
}

2. Implement Retry Logic for Deadlocks

class TransactionHelper
{
    public static function retryOnDeadlock(callable $callback, $maxAttempts = 3)
    {
        $attempt = 1;

        while (true) {
            try {
                return DB::transaction($callback);
            } catch (\Exception $e) {
                if ($attempt >= $maxAttempts || !static::isDeadlockError($e)) {
                    throw $e;
                }

                $attempt++;
                // Exponential backoff
                $sleepMs = rand(10, 20) * $attempt;
                usleep($sleepMs * 1000);
            }
        }
    }

    private static function isDeadlockError(\Exception $e)
    {
        return $e instanceof \PDOException &&
               ($e->getCode() === '40001' || $e->getCode() === '1213');
    }
}

// Usage
TransactionHelper::retryOnDeadlock(function () use ($orderId) {
    $order = Order::lockForUpdate()->find($orderId);
    // Process order
    return $order;
});

3. Lock Records in a Consistent Order

class TransferService
{
    public function transfer(Account $from, Account $to, $amount)
    {
        // Always lock accounts in order of ID to prevent deadlocks
        $firstAccount = $from->id < $to->id ? $from : $to;
        $secondAccount = $from->id < $to->id ? $to : $from;

        DB::transaction(function () use ($firstAccount, $secondAccount, $from, $to, $amount) {
            // Lock in consistent order
            $firstAccount = Account::lockForUpdate()->find($firstAccount->id);
            $secondAccount = Account::lockForUpdate()->find($secondAccount->id);

            if ($firstAccount->id === $from->id) {
                $this->debit($firstAccount, $amount);
                $this->credit($secondAccount, $amount);
            } else {
                $this->debit($secondAccount, $amount);
                $this->credit($firstAccount, $amount);
            }
        });
    }
}

Real-World Examples

Order Processing System

class OrderProcessor
{
    public function process(Order $order)
    {
        return TransactionHelper::retryOnDeadlock(function () use ($order) {
            // Lock order and related inventory items
            $order = Order::with('items')
                ->lockForUpdate()
                ->find($order->id);

            // Sort inventory IDs to prevent deadlocks
            $inventoryIds = $order->items
                ->pluck('inventory_id')
                ->sort()
                ->values();

            // Lock inventory in consistent order
            $inventories = Inventory::whereIn('id', $inventoryIds)
                ->orderBy('id')
                ->lockForUpdate()
                ->get()
                ->keyBy('id');

            // Validate inventory levels
            foreach ($order->items as $item) {
                $inventory = $inventories[$item->inventory_id];
                if ($inventory->quantity < $item->quantity) {
                    throw new InsufficientInventoryException();
                }
            }

            // Update inventory
            foreach ($order->items as $item) {
                $inventory = $inventories[$item->inventory_id];
                $inventory->quantity -= $item->quantity;
                $inventory->save();
            }

            $order->status = 'processed';
            $order->save();

            return $order;
        });
    }
}

Handling Deadlocks

Deadlocks occur when multiple transactions are waiting for each other to release locks. Here's a comprehensive approach to handling them:

1. Implement a Deadlock-Aware Transaction Manager

class DeadlockAwareTransaction
{
    private $maxAttempts;
    private $baseDelay;
    private $logger;

    public function __construct($maxAttempts = 3, $baseDelay = 10, $logger = null)
    {
        $this->maxAttempts = $maxAttempts;
        $this->baseDelay = $baseDelay;
        $this->logger = $logger ?? Log::channel('deadlocks');
    }

    public function execute(callable $callback)
    {
        $attempt = 1;
        $lastException = null;

        while ($attempt <= $this->maxAttempts) {
            try {
                return DB::transaction($callback);
            } catch (\Exception $e) {
                $lastException = $e;

                if (!$this->isRetryableException($e)) {
                    throw $e;
                }

                if ($attempt === $this->maxAttempts) {
                    $this->logger->error('Max retry attempts reached', [
                        'error' => $e->getMessage(),
                        'attempts' => $attempt
                    ]);
                    throw $e;
                }

                $this->handleRetry($attempt, $e);
                $attempt++;
            }
        }

        throw $lastException;
    }

    private function isRetryableException(\Exception $e)
    {
        // MySQL deadlock error codes
        return $e instanceof \PDOException && in_array($e->getCode(), [
            '40001', // Generic deadlock
            '1213',  // Deadlock found when trying to get lock
            '1205'   // Lock wait timeout exceeded
        ]);
    }

    private function handleRetry($attempt, \Exception $e)
    {
        // Exponential backoff with jitter
        $delay = $this->baseDelay * pow(2, $attempt - 1);
        $jitter = rand(0, 10);
        $sleepMs = ($delay + $jitter) * 1000;

        $this->logger->warning('Deadlock detected, retrying', [
            'attempt' => $attempt,
            'delay_ms' => $sleepMs,
            'error' => $e->getMessage()
        ]);

        usleep($sleepMs);
    }
}

2. Use it in Your Services

class InventoryManager
{
    private $transaction;

    public function __construct(DeadlockAwareTransaction $transaction)
    {
        $this->transaction = $transaction;
    }

    public function adjustInventory(array $items)
    {
        return $this->transaction->execute(function () use ($items) {
            // Sort items by ID to prevent deadlocks
            $sortedItemIds = collect($items)
                ->pluck('inventory_id')
                ->sort()
                ->values();

            $inventories = Inventory::whereIn('id', $sortedItemIds)
                ->orderBy('id')
                ->lockForUpdate()
                ->get();

            foreach ($inventories as $inventory) {
                $adjustment = $items[$inventory->id] ?? 0;
                $inventory->quantity += $adjustment;

                if ($inventory->quantity < 0) {
                    throw new InsufficientInventoryException();
                }

                $inventory->save();
            }

            return $inventories;
        });
    }
}

Performance Considerations

Monitor Lock Wait Times

DB::listen(function ($query) {
    if (str_contains($query->sql, 'FOR UPDATE')) {
        // Log or monitor lock acquisition time
        Log::debug('Lock wait time', [
            'sql' => $query->sql,
            'time' => $query->time
        ]);
    }
});

Set Appropriate Timeouts

// In your database configuration
'mysql' => [
    // ...
    'options' => [
        PDO::ATTR_TIMEOUT => 5, // 5 seconds
    ],
],

Use Indexes Properly

// Always lock through indexed columns
DB::transaction(function () {
    // Good - uses primary key
    $user = User::lockForUpdate()->find(1);

    // Bad - full table scan with lock
    $user = User::lockForUpdate()
        ->where('email', 'user@example.com')
        ->first();
});

Conclusion

Properly implementing database locking in Laravel requires careful consideration of:

Transaction boundaries
Lock ordering
Deadlock handling
Performance implications

By following these best practices and implementing proper error handling, you can build robust concurrent applications that maintain data consistency while providing a good user experience.

Remember:

Keep transactions short
Lock records in a consistent order
Implement retry logic for deadlocks
Monitor and log lock-related issues
Use indexes effectively

With these tools and patterns in your arsenal, you'll be well-equipped to handle complex concurrent operations in your Laravel applications.

DEV Community