Defeating Race Conditions: Optimistic Locking in Laravel 🛡️

#laravel #php #postgres #database

The Lost Update Problem in High-Concurrency Systems

In enterprise SaaS applications, data integrity is paramount. When multiple users or background processes attempt to modify the same database record simultaneously, you encounter a classic concurrency conflict known as the "Lost Update" problem.

Imagine a stock inventory system. Agent A reads a product record showing 10 items in stock. Agent B reads the exact same record milliseconds later. Agent A processes a sale and updates the stock to 9. Agent B processes a separate sale and, based on their initial read of 10, updates the stock to 9 as well. In a standard Laravel setup, Agent B’s UPDATE query simply overwrites Agent A’s, and the database now incorrectly shows 9 items instead of the correct 8. You have just lost data, corrupted your inventory, and failed a transaction audit.

At Smart Tech Devs, we guarantee absolute data integrity in high-concurrency environments. We abandon the default "last-write-wins" mentality and implement Optimistic Locking utilizing PostgreSQL Version Columns.

Philosophy: Pessimistic vs. Optimistic Locking

There are two primary architectural approaches to solving race conditions:

Pessimistic Locking: You assume the worst. Before reading a record, you lock it (e.g., SQL SELECT ... FOR UPDATE). No other user can read or write that record until you commit your transaction. While mathematically secure, this creates massive performance bottlenecks in high-traffic systems, leading to connection pool exhaustion and deadlocks.
Optimistic Locking: You assume the best. You allow multiple users to read and modify the data simultaneously. However, at the exact moment of the UPDATE query, you verify that no other user has changed the data since you read it. It prioritizes high availability and concurrency over immediate locking, making it the ideal choice for modern web architecture.

Phase 1: Architecting the Postgres Version Column

The standard way to implement optimistic locking in enterprise systems is via an integer version column that is automatically incremented on every update. Laravel’s Eloquent has native support for this, but we prefer to enforce it at the database schema layer for absolute security.


use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->integer('stock');
            // 1. Define the Version Column (defaulting to 0)
            $table->integer('lock_version')->default(0); 
            $table->timestamps();
        });

        // 2. (Optional but Recommended) Force RLS on Postgres to 
        // mathematically guarantee the version increments.
        // This ensures raw DB queries also respect the lock.
        DB::statement('ALTER TABLE products ENABLE ROW LEVEL SECURITY;');
    }
};

Phase 2: Extending Eloquent for Automagic Locking

Instead of manually adding `WHERE lock_version = ?` to every query in our controllers, we architect a reusable Eloquent Trait. This trait hooks into the model's booting process and overrides the default saving logic.


namespace App\Traits;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Builder;
use App\Exceptions\ModelVersionConflictException;

trait InterceptsOptimisticLocking
{
    public static function bootInterceptsOptimisticLocking()
    {
        // 1. Hook into the 'saving' event (fires before update/create)
        static::saving(function (Model $model) {
            if ($model->exists) {
                // Perform the version increment check ONLY on updates
                $model->performOptimisticUpdate();
            }
        });
    }

    protected function performOptimisticUpdate()
    {
        $currentVersion = $this->getOriginal('lock_version');
        $newVersion = $currentVersion + 1;

        // 2. We use raw SQL to execute an atomic update with a strict WHERE clause.
        // UPDATE products SET stock = ?, lock_version = 2 WHERE id = ? AND lock_version = 1
        $affectedRows = $this->getConnection()->table($this->getTable())
            ->where($this->getKeyName(), $this->getKey())
            ->where('lock_version', $currentVersion)
            ->update(array_merge(
                $this->getDirty(), // Only update changed columns
                ['lock_version' => $newVersion, 'updated_at' => now()]
            ));

        // 3. Critically: We check if ANY rows were affected.
        // If 0 rows are affected, it means another user successfully updated 
        // the record and incremented the version since we read it.
        if ($affectedRows === 0) {
            throw new ModelVersionConflictException($this, $currentVersion);
        }

        // 4. Update the current model instance's version in memory
        $this->lock_version = $newVersion;
        $this->syncOriginalAttribute('lock_version');
    }
}

Phase 3: Handling the Conflict (Retries)

When the ModelVersionConflictException is thrown, it means the user's action was based on stale data. We should not show them a generic 500 error. Instead, we can architect a mechanism to automatically catch the exception, re-read the fresh data, re-apply the user's intended mutation, and try again (a transparent retry).


namespace App\Http\Controllers;

use App\Models\Product;
use Illuminate\Http\Request;
use App\Exceptions\ModelVersionConflictException;
use Illuminate\Support\Facades\DB;

class ProductController extends Controller
{
    public function updateStock(Request $request, string $id)
    {
        $maxRetries = 3;
        $attempts = 0;

        while ($attempts < $maxRetries) {
            try {
                // 1. Read the fresh data inside a transaction
                return DB::transaction(function () use ($request, $id) {
                    $product = Product::findOrFail($id);

                    // 2. Perform business logic on the fresh data
                    $decrement = $request->input('quantity');
                    $product->stock -= $decrement;

                    // 3. The saving Trait will execute and potentially throw the exception
                    $product->save();

                    return response()->json(['message' => 'Stock updated securely.']);
                });
            } catch (ModelVersionConflictException $e) {
                $attempts++;
                // 4. (Optional) Log the conflict for performance analysis
                logger()->warn("Version conflict detected on Product {$id}. Retry attempt {$attempts}.");
                
                // Sleep briefly to reduce contention before retrying the loop
                usleep(50000); // 50ms
            }
        }

        // Final failure: Return a polite UI message
        return response()->json([
            'error' => 'Could not complete operation due to high contention. Please refresh and try again.'
        ], 409);
    }
}

The Engineering ROI

Implementing Optimistic Locking with Postgres Version Columns and Eloquent Traits fundamentally transforms the robustness of your data layer. By moving the security perimeter directly to the SQL `UPDATE` statement, you guarantee absolute, unforgeable data integrity without incurring the debilitating performance costs of pessimistic locks. Your controllers become slightly more complex by requiring retry logic, but your enterprise platform achieves mathematically provable audit trails and unparalleled operational resilience, ensuring that critical business transactions—like stock inventory, bank balances, or seat reservations—never suffer from a "Lost Update" flaw.

DEV Community