Optimistic Locking: Versioning, Conflicts, and High-Concurrency Writes

Optimistic Locking matters when many writers touch the same rows but almost never at the same moment. You let them read and write without holding a lock across the whole edit. You reject the save if someone else changed the row first. That keeps the data correct without a long queue, until the conflict rate climbs and retries become the load.

What Optimistic Locking Is

Optimistic locking means you assume clashes are rare. You read a row and remember a version. You later update the row only if that version is still current.

If the version changed, the update touches zero rows, and you treat that as a conflict. The caller reloads and tries again, or shows the new value to the user.

The version can be an integer, a timestamp the database sets, or a hash of the row. An integer you increment in the same update is the easiest to reason about. Client clocks are a bad version. A delayed request with an old time can overwrite a newer edit.

This is not the same as hoping two updates do not clash. A lost update happens when both clients read version 1 and both write back. Without the check, the second write wipes the first.

With the check, the second write fails and the first change stays. You must actually put the version in the WHERE clause. A version column you never read is not a lock.

The database can also do this with isolation. PostgreSQL snapshot isolation and the transaction isolation docs describe how concurrent updates interact. An update still locks the row for the statement itself. Optimistic locking is about not holding that lock while the user thinks or while you call another service.

Why It Fails in Production

The pattern fails when the assumption is wrong. If many workers update one hot row, almost every write conflicts. Each retry reads again and writes again. The database does more work than a short pessimistic lock would have, and the clients see errors.

I have seen a counter row implemented with a version check. Every checkout bumped it. Conflict rate sat near one hundred percent, and the retry storm was the incident.

A second failure is a check that does not cover the read you care about. You version the profile row, then you also change a child row in the same screen. Another user updates the child only.

Your version check passes, and you still overwrite their child change. The version must live on every row you merge, or you must lock the set.

Retries without a limit are the operational failure. A client that retries at once on every conflict will hammer the row. Add a small random delay and a cap.

After the cap, return a conflict the user can see. Also make the handler safe to run twice, because a timeout can hide a commit that actually landed.

Teams sometimes mix this with replica reads. The version you show comes from a lagged copy, so the save conflicts even when the user was alone. Read the version from the primary when you are about to write.

Eventual consistency is fine for the feed. It is a bad source for the token you will send back on save.

How to Implement the Check

Add a version column that starts at one. On update, set the new values and set version to the old version plus one. The WHERE clause includes the primary key and the version the client saw. Check the row count.

Zero means conflict. One means you won. More than one means your predicate was too wide, and you should roll back.

Keep the read and the write in short units. Do not open a transaction, show a form, and commit minutes later. Read, close, let the user edit, then open a new unit for the conditional update.

The only lock you hold is the brief row lock inside that update statement. That is the point of the pattern.

If you must change two rows together, version both and update both in one unit. If either version mismatches, roll the unit back. Partial success leaves a mix of old and new.

Order the updates by id so two clashes cannot cycle. Even optimistic paths can deadlock if two statements lock rows in opposite orders. Read about deadlocks before you update a graph of rows.

The PostgreSQL concurrency control chapter explains why readers do not block this update and why the update still waits on a writer that holds the row. Optimistic does not mean lock free. It means the lock is short. The explicit locking guide is the other path when you decide the short lock is not enough.

Where the version should live

Store the version in the database, not only in an app cache. Two app servers must see the same number. A cache can serve the row for display, but the update must compare against the database value. If you check the cache and then write the database, you reintroduce the lost update.

When to switch to a hard lock

Switch when conflicts are common or when a failed guess is expensive. A seat map, a wallet, or a stock count often wants pessimistic locking so the second writer waits instead of retrying. Measure the conflict rate. If more than a small share of writes retry, the optimistic bet is losing.

Trade-offs You Should Weigh

Optimistic locking buys throughput when clashes are rare. It charges you retries and a more careful UI when they are not. Compare it with the alternatives before you spread it across the schema.

Last write wins is simpler and wrong for most business rows. A version check is a small amount of extra SQL for a clear failure. A row lock is clearer under contention and worse when the critical section includes a human or a remote call. If the edit takes more than a few milliseconds of think time, do not hold a lock across it.

Adding the column is a schema change. Ship it with database migrations at scale so you do not rewrite a hot table in one step. Backfill the version to one, deploy the check, and only then reject clients that omit the version. Old code that updates without the version will clobber new writers if you skip that order.

Pitfalls and Failure Modes

The check is easy to get almost right. Almost right still loses updates. Walk this list in review.

1. The version is in the select list but not in the WHERE clause.
2. You check the version in the app and then issue a plain update.
3. A retry repeats a non-idempotent side effect such as a charge.
4. You version the parent and still overwrite child rows.
5. The client auto-retries a form and overwrites a newer save.
6. Two statements lock rows in different orders and deadlock on the clash path.

A common mistake I have seen is to increment the version in code that also has a second updater. A batch job sets columns and forgets to bump the version. User saves then succeed against a stale number, or they fail forever if the job and the user disagree.

Every writer must use the same conditional update. Ban bare updates on that table.

Null versions break the compare. A new row must start at a real number, not null. In SQL, null equality is not true, so the WHERE clause never matches. Default the column to one.

A Realistic Update Example

The statement below saves a display name only if the version still matches. It bumps the version in the same write. The application must look at the affected row count and must not treat zero as success.

UPDATE profiles
SET display_name = $1,
    version = version + 1,
    updated_at = now()
WHERE profile_id = $2
  AND version = $3;

-- row count 1: commit
-- row count 0: reload and return conflict

Bind the types so the version compare stays an integer compare. A cast can hide the index or surprise you with a match you did not intend. Then reload on conflict and return both the user’s draft and the current row. The UI needs both to ask a human what to keep.

If this update is part of a larger unit, roll back on a zero count before you touch the next table. Do not commit the other writes and report success. The version check only protects the row it names.

What to test

When you review the change, run two sessions. Both read version 1. Both try to set a different name.

One commit must win. The other must affect zero rows. If both win, the WHERE clause is missing the version.

Also test the retry cap. A loop that tries until it succeeds will hang a test and will hang production when the row is hot. Fail on the third try in the test and in the service.

Performance, Scale, and Cost

When clashes are rare, the extra cost is one integer column and a slightly wider update. Readers do not wait on each other. In an illustrative production range, a quiet table can take far more concurrent editors this way than with a lock held for a whole form. Your limit appears when the conflict percent rises, not when the column is added.

A hot row is the expensive case. Each conflict is a read plus a failed write plus a retry. Ten retries per success multiply load by ten and still frustrate the user.

At that point a short row lock, or a sharded counter, is cheaper. Measure conflicts per success. If the ratio is high, stop tuning the retry delay and change the model.

Watch conflict rate, retry count, and p99 on the save endpoint. A rising conflict rate with flat traffic means a hotter key, not a slower disk. Split that key or serialize it. Adding app servers will only add more retries.

Key Takeaways

• Put the version in the UPDATE predicate, not only in the application if statement.
• Treat a zero row count as a conflict and reload before you try again.
• Keep transactions short so the real row lock lasts only for the statement.
• Cap retries and add jitter so a hot key cannot stampede.
• Use a row lock or a single writer when conflicts are common.
• Version every row in the edit, or you will still lose child updates.
• Read the version from the primary so a replica does not fake a conflict.

FAQ

Is optimistic locking the same as no locks?

No. The update statement still takes a brief row lock so two writes cannot both change the row at the same instant. What you skip is the long lock across user think time or across a remote call.

Readers can proceed. A second writer waits only for that short statement, then may fail the version check.

What version type should you use?

Use an integer you increment in the database. It is simple and it does not depend on clocks. A timestamp from the client is easy to forge and easy to skew.

A hash of the whole row also works, but it is harder to explain in a conflict response. Start with the integer.

Should the client retry on its own?

Retry only when the save has no extra side effect and the user is not staring at a form. A background job can retry with a cap and jitter. A form should reload and ask the user. An automatic retry of a form will overwrite the winner and hide the clash you added the version to catch.

When should you prefer a pessimistic lock?

Prefer it when many writers hit one row, or when a failed attempt is costly, such as a double booking. The waiter lines up and then proceeds. That is calmer than a retry storm.

Keep the lock short. If you cannot, stay optimistic and push the conflict to the user.

Add a version column and a conditional update on the tables where two editors are possible but uncommon. Test that one of two concurrent saves fails, and return that failure to the user with the current row. If the conflict chart climbs, move that hot key to a short pessimistic lock instead of raising the retry count.

Last updated on 14 September 2026.