Preventing Data Races with Pessimistic Locking in Laravel

In concurrent database operations, ensuring data integrity can be challenging. Laravel provides powerful tools for implementing pessimistic locking in your database queries, helping you prevent data races and maintain consistency in your application.

Understanding Pessimistic Locking

Pessimistic locking is a strategy where a record is locked before it's updated, preventing other transactions from modifying it simultaneously. Laravel offers two main methods for pessimistic locking: sharedLock() and lockForUpdate().

Using sharedLock()

The sharedLock() method applies a shared lock to the selected rows. This prevents the selected rows from being modified until your transaction is committed:

DB::table('users')
    ->where('votes', '>', 100)
    ->sharedLock()
    ->get();

In this example, other transactions can read the locked rows but cannot modify them.

Using lockForUpdate()

The lockForUpdate() method applies a more restrictive lock. It prevents the selected records from being modified or from being selected with another shared lock:

DB::table('users')
    ->where('votes', '>', 100)
    ->lockForUpdate()
    ->get();

This lock is more severe, blocking other transactions from even reading the locked rows in some database systems.

Practical Examples

• Updating a user's balance:

DB::transaction(function () use ($userId, $amount) {
    $user = DB::table('users')
        ->where('id', $userId)
        ->lockForUpdate()
        ->first();

    if ($user->balance >= $amount) {
        DB::table('users')
            ->where('id', $userId)
            ->update(['balance' => $user->balance - $amount]);

        return true;
    }

    return false;
});

• Reserving inventory:

DB::transaction(function () use ($productId, $quantity) {
    $product = DB::table('products')
        ->where('id', $productId)
        ->lockForUpdate()
        ->first();

    if ($product->stock >= $quantity) {
        DB::table('products')
            ->where('id', $productId)
            ->update(['stock' => $product->stock - $quantity]);

        return true;
    }

    return false;
});

When to Use Pessimistic Locking

• High-concurrency scenarios where data integrity is crucial
• Financial transactions or inventory management
• Situations where the cost of a conflict is higher than the performance impact of locking

By leveraging pessimistic locking in Laravel, you can ensure data consistency in scenarios where multiple processes might try to modify the same data simultaneously. This feature is particularly valuable in applications dealing with critical data where accuracy is paramount.

If this guide was helpful to you, subscribe to my daily newsletter and give me a follow on X/Twitter. It helps a lot!