Database Transactions & Pessimistic Locking in Laravel

· 5 min read
#laravel #database #transactions #concurrency

Concurrency is a challenge in production systems. When multiple requests modify the same data simultaneously, you risk race conditions and data corruption. Laravel provides powerful tools to manage this: transactions and pessimistic locking.

Database Transactions

A transaction ensures a group of operations either all succeed or all fail atomically.

use Illuminate\Support\Facades\DB;

DB::transaction(function () {
    $user = User::find(1);
    $user->decrement('balance', 100);

    Invoice::create([
        'user_id' => $user->id,
        'amount' => 100,
    ]);
    
    // If anything throws an exception, all changes are rolled back
});

Handling Transaction Failures

try {
    DB::transaction(function () {
        // Database operations
    });
} catch (Exception $e) {
    Log::error('Transaction failed: ' . $e->getMessage());
    // Handle failure gracefully
}

Savepoints

For nested transactions, use savepoints:

DB::transaction(function () {
    User::find(1)->increment('points', 10);

    DB::transaction(function () {
        // This is a savepoint
        Order::create(['user_id' => 1]);
    }, attempts: 1);
});

Pessimistic Locking

Prevent race conditions by locking rows:

$user = User::where('id', 1)
    ->lockForUpdate()  // Exclusive lock
    ->first();

$user->decrement('balance', 100);
$user->save();

The row is locked until the transaction commits.

Shared Lock

Allow other readers but prevent updates:

$user = User::where('id', 1)
    ->sharedLock()  // Read lock
    ->first();

// Another request can read this row with sharedLock()
// But cannot lockForUpdate()

Practical Example: Transfer Funds

public function transferFunds(int $fromUserId, int $toUserId, float $amount): void
{
    DB::transaction(function () use ($fromUserId, $toUserId, $amount) {
        // Lock both rows in consistent order to prevent deadlocks
        $sender = User::whereIn('id', [$fromUserId, $toUserId])
            ->orderBy('id')
            ->lockForUpdate()
            ->get()
            ->firstWhere('id', $fromUserId);

        $receiver = User::whereIn('id', [$fromUserId, $toUserId])
            ->orderBy('id')
            ->lockForUpdate()
            ->get()
            ->firstWhere('id', $toUserId);

        if ($sender->balance < $amount) {
            throw new InsufficientFundsException();
        }

        $sender->decrement('balance', $amount);
        $receiver->increment('balance', $amount);

        Transaction::create([
            'from_user_id' => $fromUserId,
            'to_user_id' => $toUserId,
            'amount' => $amount,
        ]);
    });
}

Optimistic Locking with Versioning

Instead of locking, use version columns to detect conflicts:

Schema::create('posts', function (Blueprint $table) {
    $table->id();
    $table->string('title');
    $table->longText('content');
    $table->integer('version')->default(0);
    $table->timestamps();
});

class Post extends Model
{
    protected $attributes = [
        'version' => 0,
    ];

    public function updateContent(string $content): void
    {
        $this->update([
            'content' => $content,
            'version' => DB::raw('version + 1'),
        ]);
    }

    // In controller or service
    public function saveChanges(int $postId, string $content, int $knownVersion): void
    {
        $updated = DB::transaction(function () use ($postId, $content, $knownVersion) {
            return DB::table('posts')
                ->where('id', $postId)
                ->where('version', $knownVersion)
                ->update([
                    'content' => $content,
                    'version' => $knownVersion + 1,
                ]);
        });

        if (!$updated) {
            throw new OptimisticLockException('Post was modified since you last read it.');
        }
    }
}

Named Locks

Use named locks for distributed systems:

DB::transaction(function () {
    DB::select('SELECT GET_LOCK(?, 30)', ['inventory_lock']);
    
    try {
        // Safe section
        Inventory::where('sku', 'ABC123')->decrement('stock');
    } finally {
        DB::select('SELECT RELEASE_LOCK(?)', ['inventory_lock']);
    }
});

Deadlock Handling

When multiple transactions lock resources in different orders, deadlocks occur:

$maxAttempts = 3;
$attempt = 0;

while ($attempt < $maxAttempts) {
    try {
        DB::transaction(function () {
            // Lock operations
        });
        break;
    } catch (QueryException $e) {
        if ($e->getCode() === 'HY000' && strpos($e->getMessage(), 'Deadlock') !== false) {
            $attempt++;
            if ($attempt >= $maxAttempts) {
                throw new DeadlockException('Max retry attempts exceeded');
            }
            sleep(random_int(1, 3)); // Back off with random delay
        } else {
            throw;
        }
    }
}

Or use a helper class:

namespace App\Database;

use Closure;
use Illuminate\Database\QueryException;

class TransactionHelper
{
    public static function withDeadlockHandling(Closure $callback, int $maxAttempts = 3): mixed
    {
        for ($attempt = 1; $attempt <= $maxAttempts; $attempt++) {
            try {
                return DB::transaction($callback);
            } catch (QueryException $e) {
                if ($attempt === $maxAttempts || !self::isDeadlock($e)) {
                    throw;
                }
                
                usleep(random_int(1000, 3000000)); // 1-3 seconds
            }
        }
    }

    private static function isDeadlock(QueryException $e): bool
    {
        $message = $e->getMessage();
        return str_contains($message, 'Deadlock') || 
               str_contains($message, 'deadlock') ||
               $e->getCode() === '40P01'; // PostgreSQL deadlock code
    }
}

Usage:

TransactionHelper::withDeadlockHandling(function () {
    // Concurrent-safe operation
});

Common Locking Patterns

Inventory Management

public function deductInventory(string $sku, int $quantity): void
{
    DB::transaction(function () use ($sku, $quantity) {
        $inventory = Inventory::where('sku', $sku)
            ->lockForUpdate()
            ->first();

        if ($inventory->available_stock < $quantity) {
            throw new OutOfStockException();
        }

        $inventory->decrement('available_stock', $quantity);
    });
}

Race Condition Prevention

public function claimReward(User $user): void
{
    DB::transaction(function () use ($user) {
        $user = User::where('id', $user->id)
            ->lockForUpdate()
            ->first();

        if ($user->daily_claim_count >= 3) {
            throw new DailyLimitExceededException();
        }

        $user->increment('daily_claim_count');
        $user->increment('points', 100);
    });
}

Best Practices

  1. Keep transactions short - Minimize lock duration
  2. Lock consistently - Always lock in same order to prevent deadlocks
  3. Use shared locks for reads - Allows concurrent reads
  4. Consider optimistic locking - For mostly-read scenarios
  5. Log deadlocks - Monitor and analyze patterns
  6. Handle exceptions - Retry strategies matter
  7. Test concurrency - Use load testing tools

Transaction Isolation Levels

Different isolation levels provide different guarantees:

// Dirty reads possible (risky)
DB::connection()->statement('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED');

// Prevents dirty reads
DB::connection()->statement('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');

// Prevents dirty and non-repeatable reads
DB::connection()->statement('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ');

// Strongest - serializable
DB::connection()->statement('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');

Summary

Handling concurrent operations correctly is critical:

  • Transactions - Atomicity at the database level
  • Pessimistic Locking - Lock rows to prevent concurrent modifications
  • Optimistic Locking - Version columns to detect conflicts
  • Deadlock Handling - Retry with backoff
  • Isolation Levels - Different guarantees for different needs

Choose the right strategy for your use case. Most applications benefit from pessimistic locking for critical sections and optimistic locking for read-heavy operations.

Comments