Optimistic Locking With a Version Column: Fix Lost Updates in Laravel (vs lockForUpdate)

Two support agents open the same product in the admin panel. Agent A lowers the price from 100 to 90 and saves. Agent B, still looking at the form she loaded a minute earlier, fixes the stock count and saves. The price is back to 100. No error, no warning, no log line. The next morning finance asks why a promotion never went live, and nobody can explain it, because from the database's point of view nothing went wrong.

This is the lost update problem. Each request did a perfectly valid read-modify-write; the bug is the gap between the read and the write. Database transactions alone do not fix it, because the read happened in one HTTP request and the write in another, minutes apart. The fix is to make the write conditional on the data not having changed since it was read.

The Lost Update, Step by Step

Here is what the two saves look like at the SQL level. Most CRUD forms send every field back, not just the changed ones, which is exactly what makes the second save overwrite the first:

-- 10:00:00  Agent A loads product 42: price = 100, stock = 7
-- 10:00:02  Agent B loads product 42: price = 100, stock = 7
-- 10:00:05  Agent A saves a new price
UPDATE products SET price = 90, stock = 7 WHERE id = 42;
-- 10:00:09  Agent B saves a stock correction, using the price it loaded
UPDATE products SET price = 100, stock = 5 WHERE id = 42;
-- Result: price is back to 100. Agent A's change is gone and nobody got an error.

Even if your form only sent changed fields, the problem returns as soon as a value is derived from what was read: "add 10% to the price", "subtract 2 from stock". Any read-modify-write that spans time is exposed.

Optimistic Locking With a Version Column

Optimistic locking assumes conflicts are rare and checks for them at write time instead of preventing them up front. Add an integer version to the row. Every reader gets the version along with the data. Every writer says "apply this change only if the version is still the one I saw" and bumps it in the same statement:

-- Each writer sends the version it read. Only one of them can match.
UPDATE products
SET price = 90, version = version + 1
WHERE id = 42 AND version = 7;   -- Agent A: 1 row affected, version is now 8

UPDATE products
SET stock = 5, version = version + 1
WHERE id = 42 AND version = 7;   -- Agent B: 0 rows affected, conflict detected

The WHERE ... AND version = 7 makes the check and the write a single atomic operation. The database already serializes updates to the same row, so exactly one of the two statements can match. The loser sees zero affected rows and knows its view of the data is stale.

You could use updated_at as the version, but timestamps have second or millisecond resolution and two saves inside the same tick would both match. An integer that only ever increments is unambiguous.

Laravel Implementation

Start with the column. A default of 1 makes existing rows valid immediately:

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('products', function (Blueprint $table) {
            $table->unsignedInteger('version')->default(1)->after('id');
        });
    }

    public function down(): void
    {
        Schema::table('products', function (Blueprint $table) {
            $table->dropColumn('version');
        });
    }
};

Eloquent's save() does not tell you how many rows an update affected, so do the conditional update through the query builder, where update() returns the affected row count:

namespace App\Services;

use App\Exceptions\StaleModelException;
use App\Models\Product;
use Illuminate\Support\Facades\DB;

final class ProductWriter
{
    /**
     * @param array<string, mixed> $changes
     * @throws StaleModelException when someone else saved first
     */
    public function update(int $productId, int $expectedVersion, array $changes): int
    {
        $affected = Product::query()
            ->whereKey($productId)
            ->where('version', $expectedVersion)
            ->update([...$changes, 'version' => DB::raw('version + 1')]);

        if ($affected === 0) {
            throw new StaleModelException(
                Product::class,
                $productId,
                $expectedVersion,
                Product::whereKey($productId)->value('version'),
            );
        }

        return $expectedVersion + 1;
    }
}

A dedicated exception carries enough context to log and to answer the client. Laravel calls render() on exceptions that define it, so controllers stay clean:

namespace App\Exceptions;

use RuntimeException;

final class StaleModelException extends RuntimeException
{
    public function __construct(
        public readonly string $model,
        public readonly int $id,
        public readonly int $expectedVersion,
        public readonly ?int $currentVersion,
    ) {
        parent::__construct(
            $currentVersion === null
                ? "{$model} #{$id} no longer exists."
                : "{$model} #{$id} was modified: expected version {$expectedVersion}, found {$currentVersion}."
        );
    }

    public function render(): \Illuminate\Http\JsonResponse
    {
        return response()->json([
            'message' => 'This record was changed by someone else. Reload and try again.',
            'current_version' => $this->currentVersion,
        ], $this->currentVersion === null ? 404 : 409);
    }
}

The version has to travel with the data. For a JSON API, return it in the body and as an ETag, and require the client to send back the version it edited. For a Blade or Livewire form, a hidden version field does the same job:

namespace App\Http\Controllers;

use App\Models\Product;
use App\Services\ProductWriter;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;

final class ProductController
{
    public function show(Product $product): JsonResponse
    {
        return response()
            ->json($product)
            ->header('ETag', '"'.$product->version.'"');
    }

    public function update(Request $request, Product $product, ProductWriter $writer): JsonResponse
    {
        $data = $request->validate([
            'price' => ['sometimes', 'integer', 'min:0'],
            'stock' => ['sometimes', 'integer', 'min:0'],
            'version' => ['required', 'integer', 'min:1'],
        ]);

        $version = $writer->update($product->id, $data['version'], collect($data)->except('version')->all());

        return response()->json(['version' => $version])->header('ETag', '"'.$version.'"');
    }
}

The 409 response is a feature. The frontend can show "Someone changed this product while you were editing", reload the latest data, and let the user reapply their change knowingly. That is far better than silently discarding work.

Retrying When There Is No Human

Background jobs and scheduled tasks have nobody to show a conflict dialog to. For them, the right response to a stale version is usually: re-read, recompute, try again, a bounded number of times. Laravel's retry() helper fits well:

namespace App\Services;

use App\Exceptions\StaleModelException;
use App\Models\Product;
use Closure;

final class PriceAdjuster
{
    public function __construct(private ProductWriter $writer) {}

    /** Background jobs have no human to ask, so they re-read and retry a few times. */
    public function applyDiscount(int $productId, int $percent, int $maxAttempts = 3): void
    {
        retry($maxAttempts, function () use ($productId, $percent): void {
            $product = Product::findOrFail($productId);
            $newPrice = intdiv($product->price * (100 - $percent), 100);

            $this->writer->update($product->id, $product->version, ['price' => $newPrice]);
        }, sleepMilliseconds: fn (int $attempt): int => random_int(10, 50) * $attempt,
            when: fn (\Throwable $e): bool => $e instanceof StaleModelException);
    }
}

The re-read is essential: retrying the same write with the same old version would fail forever. The small random sleep spreads out competing workers so they do not collide again on the next attempt.

Optimistic vs Pessimistic: lockForUpdate

The alternative is to prevent conflicts instead of detecting them. lockForUpdate() issues SELECT ... FOR UPDATE, which locks the row until the transaction commits; any other transaction that wants the same lock waits:

use App\Models\Product;
use Illuminate\Support\Facades\DB;

// Pessimistic: lock the row, everyone else waits until commit.
DB::transaction(function () use ($productId, $quantity): void {
    $product = Product::whereKey($productId)->lockForUpdate()->firstOrFail();

    if ($product->stock < $quantity) {
        throw new \DomainException('Not enough stock.');
    }

    $product->decrement('stock', $quantity);
}, attempts: 3);

// Often better for a single counter: let the database do the check atomically.
$reserved = Product::whereKey($productId)
    ->where('stock', '>=', $quantity)
    ->decrement('stock', $quantity);

Pessimistic locking only works inside a single transaction. You cannot hold a row lock while an admin thinks for two minutes; that would block every other writer and exhaust connections. So the two approaches cover different shapes of work:

• Pessimistic for short, server-side critical sections where conflicts are frequent: reserving stock at checkout, allocating seats, moving money between wallets.
• Optimistic for anything with think time between read and write (forms, editors, mobile apps syncing offline changes) and for rows that are read often but rarely edited at the same time.

The second snippet above shows a third option that beats both for simple counters: an atomic conditional update. stock = stock - 3 WHERE stock >= 3 needs no version and no lock held across round trips.

Testing the Conflict Path

The conflict path is the code you hope never runs, which is exactly why it needs a test. Simulate the two agents by writing twice with the same starting version:

use App\Exceptions\StaleModelException;
use App\Models\Product;
use App\Services\ProductWriter;

it('rejects the second writer that loaded the same version', function () {
    $product = Product::factory()->create(['price' => 100, 'stock' => 7, 'version' => 1]);
    $writer = app(ProductWriter::class);

    expect($writer->update($product->id, 1, ['price' => 90]))->toBe(2);

    expect(fn () => $writer->update($product->id, 1, ['stock' => 5]))
        ->toThrow(StaleModelException::class);

    expect($product->fresh())
        ->price->toBe(90)
        ->stock->toBe(7)
        ->version->toBe(2);
});

it('returns 409 with the current version over HTTP', function () {
    $product = Product::factory()->create(['version' => 3]);

    $this->actingAs(\App\Models\User::factory()->create())
        ->putJson("/api/products/{$product->id}", ['price' => 50, 'version' => 2])
        ->assertStatus(409)
        ->assertJsonPath('current_version', 3);
});

The first test proves that the losing write changes nothing (stock is still 7). The second proves the HTTP contract the frontend depends on. If you have a retrying writer, add a test where the first attempt is made stale on purpose, as the challenge below does.

Common Pitfalls

• Bumping the version in PHP instead of SQL. ['version' => $model->version + 1] without the WHERE version = ? check is just a counter. The condition is the lock.
• Ignoring the affected row count. On MySQL, an UPDATE that matches a row but changes no values can report 0 affected rows by default. Because the version always increments, a matching row always changes, so 0 really means "no match".
• Taking the version from the database at save time. If the controller loads the model and uses its current version, the check always passes. The version must come from what the client read.
• Mass updates that skip the version. A maintenance script or Product::query()->update([...]) that does not bump version lets stale clients overwrite its changes. Every writer must participate.
• Retrying forever. Unbounded retries on a hot row turn a conflict into a livelock. Cap attempts and surface the failure.

When to Use It, and When Not To

Use optimistic locking when edits are human-paced, conflicts are uncommon, and losing a write is unacceptable: admin panels, CMS content, settings, orders being edited by staff, offline-first mobile sync. It costs one integer column and one extra WHERE condition, and it never blocks readers or other writers.

Do not use it for highly contended rows where many processes write constantly, such as a global counter or a popular item's stock during a flash sale; most writers will lose and retry. Use an atomic update or a short pessimistic lock there. And do not add it to append-only tables or rows that only one process ever writes; there is no conflict to detect.