Imagine two shoppers reserving the final two seats for the same event at nearly the same time. Both see "2 seats available." Shopper A checks out first. When Shopper B checks out, their reservation request still carries the older snapshot even though one seat remains.

Optimistic Locking handles this race by storing a version alongside the inventory row and requiring each write to match the version it read. It is "optimistic" because no database lock is held across a shopper's checkout: each worker assumes the row will stay unchanged and lets the version guard detect when that assumption was wrong. The guarded UPDATE still takes an ordinary row lock for the duration of its short write transaction.

The version token crosses transaction boundaries. In this example, sku is a unique key and each read or write attempt runs in its own short transaction. Worker A commits before Worker B submits its stale update. After a conflict, Worker B starts a new transaction, rereads the current row, recomputes the reservation, and retries with the new version.

Optimistic locking SQL outcomes

After a stale write is rejected, its worker must reread the current version before constructing a successful retry.

  1. Worker Aread v7expected v7ROW_COUNT() = 1reservation saved
  2. Worker Balso read v7expected v7ROW_COUNT() = 0stale write rejected
  3. Worker Breread v8expected v8ROW_COUNT() = 1retry saved

Those row counts are the decision points. The replay below follows the inventory row itself: the rejected attempt leaves available 1 · version 8 untouched, and only the reread enables the second decrement.

A stale version cannot decrement inventory

Both workers read version 7. Worker B can reserve the second unit only after its stale write is rejected and it rereads version 8.

SKU-42 begins with 2 available units at version 7. Worker A and Worker B both read version 7. Worker A's guarded update succeeds and advances the row to 1 available unit at version 8. Worker B's stale version 7 update changes zero rows. Worker B rereads version 8, retries against version 8, and advances the row to 0 available units at version 9.

The version predicate turns the update into a compare-and-swap: a stale version changes zero rows, so the application must reread before retrying.

The version guard

The guarded UPDATE carries the version from the earlier read into the WHERE clause, making the write a compare-and-swap on one row. It either changes both available and version, or changes nothing:

UPDATE inventory_items
SET
  available = available - 1,
  version = version + 1
WHERE sku = 'SKU-42'
  AND available > 0
  AND version = :version;

-- Same connection, immediately after the UPDATE.
SELECT ROW_COUNT();

MySQL's ROW_COUNT() reports the number of rows changed by the preceding statement. Application code can usually read the same affected-row count from its database driver instead of issuing the extra SELECT. A result of 1 means the reservation was saved. A result of 0 means no row matched every guard, so the worker must reread before deciding whether the cause was a stale version, no remaining stock, or a missing SKU.

With pessimistic row locking, Worker A would begin a short transaction, read the inventory row with SELECT ... FOR UPDATE, make the reservation decision, update the row, and commit. Worker B would wait for that transaction to finish, then read the current available and version values before deciding whether to reserve anything. This avoids the stale-write retry path, but holds the row lock until the transaction ends, so the transaction should not span shopper checkout, network calls, or other slow application work.

Complete two-worker SQL sequence
-- Assume sku is PRIMARY KEY or UNIQUE.

-- Worker A reads the row in a short transaction.
START TRANSACTION READ ONLY;

SELECT available, version
FROM inventory_items
WHERE sku = 'SKU-42';
-- available 2, version 7

COMMIT;

-- Worker B independently reads the same version.
START TRANSACTION READ ONLY;

SELECT available, version
FROM inventory_items
WHERE sku = 'SKU-42';
-- available 2, version 7

COMMIT;

-- Worker A's guarded write succeeds.
START TRANSACTION;

UPDATE inventory_items
SET
  available = available - 1,
  version = version + 1
WHERE sku = 'SKU-42'
  AND available > 0
  AND version = 7;

SELECT ROW_COUNT();
-- 1 row

COMMIT;

-- Worker B's stale write changes nothing.
START TRANSACTION;

UPDATE inventory_items
SET
  available = available - 1,
  version = version + 1
WHERE sku = 'SKU-42'
  AND available > 0
  AND version = 7;

SELECT ROW_COUNT();
-- 0 rows

COMMIT;

-- Worker B starts a fresh transaction and rereads.
START TRANSACTION READ ONLY;

SELECT available, version
FROM inventory_items
WHERE sku = 'SKU-42';
-- available 1, version 8

COMMIT;

-- Worker B recomputes the reservation and retries with v8.
START TRANSACTION;

UPDATE inventory_items
SET
  available = available - 1,
  version = version + 1
WHERE sku = 'SKU-42'
  AND available > 0
  AND version = 8;

SELECT ROW_COUNT();
-- 1 row

COMMIT;

-- Settled state.
START TRANSACTION READ ONLY;

SELECT available, version
FROM inventory_items
WHERE sku = 'SKU-42';
-- available 0, version 9

COMMIT;

At LinkedIn

We use Temporal Workflows to orchestrate order placement. Some steps can execute in parallel and create a race condition when they update the same order state. Optimistic locking makes that conflict explicit: one guarded attempt commits, while stale attempts change zero rows.

A retrying activity must reread the latest row and recompute its change before trying again; replaying the same stale version cannot succeed. Temporal gives us durable retry orchestration, while the version guard prevents those retries from overwriting newer state.

References