SELECT FOR UPDATE vs. Optimistic Locking: What Race Conditions Actually Need

Four 2026 CVEs prove the same mistake. The fix depends on what your app is actually doing.

A promotion with a 10-use cap gets redeemed 50 times. A wallet transfer runs twice from the same balance. A single-use impersonation token works on three concurrent requests. These are not hypothetical failure modes.

They are real 2026 CVEs: CVE-2026–31824 in Sylius, CVE-2026–34368 in AVideo, CVE-2026–27128 in Craft CMS. The root cause in every case is identical: a read-check-act pattern with no synchronization between the read and the write.

The fix seems obvious: add a transaction, add a lock. But that is where the disagreement starts. Three different patterns get cited as the “right” answer: SELECT FOR UPDATE, optimistic locking, and atomic SQL. Teams consistently pick the wrong one for their situation. Getting it wrong does not always blow up immediately. Sometimes it ships quietly, survives code review, and surfaces as a CVE a year later.

The Pattern That Keeps Shipping

Here is the shape of the bug. The application reads a value, evaluates a condition in PHP or Python or whatever application runtime is handling the request, then writes back a new value. The window between read and write is where everything falls apart under load.

// The vulnerable pattern - do not ship this
$promotion = Promotion::find($id);

if ($promotion->used_count >= $promotion->usage_limit) {
    return response()->json(['error' => 'Limit reached'], 422);
}
// Any concurrent request that passed the check above
// will also reach this line before either commits.
$promotion->increment('used_count');

Two requests arrive at the same millisecond. Both read used_count as 9 against a limit of 10, so both pass the eligibility check. Both then increment the counter before either transaction has committed. The counter lands at 11, not 10. CVE-2026-31824 is exactly this, applied to Sylius's promotion eligibility check: the in-memory entity is read during validation, and the usage increment happens later in the order completion step, with no database-level locking or atomic operation between them.

There are three fixes. They are not interchangeable.

Fix 1: Atomic SQL Updates

The simplest and most underused option. If the condition you are checking can be expressed as a SQL WHERE clause, you do not need a lock. You move the check and the write into a single atomic statement.

-- Atomic decrement: zero affected rows means the condition failed
UPDATE wallets
SET balance = balance - 100
WHERE user_id = 1
  AND balance >= 100;

Check the affected row count in your application. Zero means the condition was false at write time. No lock was held between read and write because there was no separate read. The write either succeeds or it does not.

In Laravel:

// Atomic update - check affected rows, no transaction lock required
$affected = DB::table('wallets')
    ->where('user_id', $userId)
    ->where('balance', '>=', $amount)
    ->update(['balance' => DB::raw("balance - {$amount}")]);


if ($affected === 0) {
    throw new InsufficientFundsException();
}

This is faster than SELECT FOR UPDATE in most cases. No shared lock means no blocking. No transaction held open waiting for application-side logic.

Where atomic updates work:

• The entire condition fits in a SQL WHERE clause
• No application-side calculation happens between the read and write
• You only need the result of the write, not the pre-write value (or you use RETURNING to get it atomically)

Where atomic updates do not work:

• Multi-step business logic that cannot be expressed in SQL
• Decisions that span multiple tables and cannot be collapsed into one query
• Cases where you need to read the current value, do non-trivial calculation, then write a result that depends on that calculation in a way SQL cannot express

AVideo’s CVE-2026–34368 would have been fixed with this single pattern. The entire vulnerability was a balance check in PHP followed by a separate update. Atomic SQL removes the window entirely.

Fix 2: SELECT FOR UPDATE

When the logic cannot be expressed in SQL (and by that I mean any decision that requires reading current state, running application-side calculation, and writing back a result that SQL cannot compute on its own), you need a lock that holds across that work. That is what SELECT FOR UPDATE does.

DB::transaction(function () use ($userId, $amount) {
    // Row is locked until the transaction commits or rolls back
    $wallet = Wallet::where('user_id', $userId)
        ->lockForUpdate()
        ->first();

    if ($wallet->balance < $amount) {
        throw new InsufficientFundsException();
    }
    // Application-side logic, external calls, multi-table updates
    // all happen here while the row is locked
    $wallet->balance -= $amount;
    $wallet->save();
    LedgerEntry::create([...]);
});

The lock is held from the SELECT to the COMMIT. Any other transaction trying to SELECT FOR UPDATE on the same row blocks and waits. Concurrent requests serialize. The race condition disappears.

The costs are real, though.

What SELECT FOR UPDATE costs you:

• Lock contention: high-throughput endpoints block each other. A wallet endpoint that handles 500 concurrent transfers per second will see queuing
• Deadlock risk: if two transactions both lock row A then try to lock row B, but in different order, PostgreSQL detects the deadlock and aborts one of them. Your application must handle this with a retry
• Lock duration: any slow application logic, network call, or ORM overhead inside the transaction extends how long the lock is held. Longer lock = more contention
• For non-key updates: SELECT FOR UPDATE also blocks concurrent INSERTs of referencing rows. If you are not modifying a key column, prefer SELECT FOR NO KEY UPDATE in PostgreSQL

Deadlocks in particular catch teams off guard. The rule is simple: always acquire locks in the same order across all code paths. If your application locks users then wallets in one place and wallets then users in another, you will eventually deadlock.

Where SELECT FOR UPDATE works:

• Multi-step logic that must run as a unit with the protected data locked
• Cross-table updates where multiple rows must be consistent with each other
• Low-to-moderate concurrency where lock contention is not a bottleneck
• Cases where you cannot retry, so a conflict must block rather than fail

Where SELECT FOR UPDATE causes problems:

• Hot rows with very high concurrent access (a single popular item in an inventory system)
• Long-running application logic inside the transaction
• Code paths that call external services inside the transaction and hold the lock while waiting on network

Fix 3: Optimistic Locking

This pattern takes a different approach entirely: no lock is acquired at all. Instead, you record what version of the data you read, do your work, then attempt the write with a version check attached. The write succeeds only if the version in the database still matches what you read. If someone else changed it between your read and write, the update affects zero rows. You detect this and retry.

// Schema: add a version column, increment on every write
$product = Product::find($id);
$currentVersion = $product->version;

// ... application logic using $product data ...
$affected = Product::where('id', $id)
    ->where('version', $currentVersion)
    ->update([
        'stock'   => $newStock,
        'version' => $currentVersion + 1,
    ]);

if ($affected === 0) {
    // Someone else updated first. Reload and retry.
    throw new ConflictException('Stale read, please reload and retry.');
}

No lock held. No blocking. In a low-contention scenario, this is faster than SELECT FOR UPDATE because the happy path has zero lock overhead.

What optimistic locking costs you:

• Retry logic is mandatory. If you do not handle the zero-affected-rows case with a retry, you silently drop the update
• Retry storms: under high contention, many concurrent writers will all fail and retry simultaneously, which increases load precisely when you are already under pressure
• Read cost: you need to reload the data on each retry, which means more database round-trips than SELECT FOR UPDATE under contention
• Version column maintenance: every write path must increment the version, without exception. Missing one update path creates a hidden hole in the protection

Where optimistic locking works:

• Low-to-moderate write contention on the same row
• Reads are frequent but writes are infrequent (classic read-heavy CRUD)
• The cost of a retry is cheap and acceptable to the caller
• You want to avoid any lock overhead on the read path

Where optimistic locking fails:

• High write contention: a popular item being updated by thousands of concurrent requests will see most writes fail and retry, creating a feedback loop
• Workflows where retry is not acceptable or not possible (financial transactions where partial state has already been committed elsewhere)
• Long read-to-write windows where stale data is likely

Picking the Right Pattern

These are not three versions of the same thing. They solve different shapes of the problem.

Use atomic SQL when:

1. The check can be fully expressed in a SQL WHERE clause
2. No application-side logic needs to run between read and write
3. You want the lowest possible overhead and no lock contention risk
4. You need the fix to be simple enough that a future engineer cannot accidentally break it

Use SELECT FOR UPDATE when:

1. Business logic must run in application code between the read and write
2. Updates span multiple tables and must be consistent with each other
3. You can tolerate some lock contention and have handled deadlock retry
4. The concurrency level on the hot row is moderate, not extreme

Use optimistic locking when:

1. Write contention on the affected rows is low in practice
2. Retry is cheap and acceptable for the caller
3. You want no lock overhead on the happy path
4. The alternative (SELECT FOR UPDATE) would create contention on a critical read path

One note worth making explicit: most applications benefit from atomic SQL far more than they realize. The reflex toward SELECT FOR UPDATE is understandable. It is the most explicit form of “I am protecting this.” But it pulls in lock contention as a side effect. If your check is expressible in SQL, start there and add more machinery only when the logic actually demands it.

The Pattern the CVEs Share

Every one of the 2026 CVEs mentioned here used application-side logic to enforce a limit that should have been enforced at the database layer. Sylius reads the promotion’s used count into PHP and checks it there. AVideo reads the wallet balance into PHP and checks it there. Craft CMS reads the token’s usage count into PHP and checks it there.

The database cannot lie about the result of an UPDATE that runs atomically. Application-side checks can be raced. That asymmetry is the lesson.

Transactions help. Locks help. But the first question to ask is whether you need either, or whether the check belongs in SQL to begin with. The four CVEs above did not fail because of missing transactions. They failed because the check ran in PHP when it should have run in SQL. That is a shorter fix, less infrastructure to maintain, and nothing to deadlock.