The Lost Update Problem: Atomic Updates, Pessimistic Locks, Version Columns and If-Match

Two requests read the same row, both modify it in memory, both write it back. The second write silently erases the first. There is no error, the logs are clean, and the only symptom is a user saying "the change I saved is gone." This is the lost update, and it waits in every piece of code shaped like read, modify, write: decrementing stock, adjusting a balance, saving a form, appending to a list inside a JSON column. There is no single correct fix. There are three tools, each suited to a different shape of operation, and picking the wrong one either leaves the bug in place or replaces it with a new one.

Name the problem precisely first

A lost update is a race, but not every race wants the same medicine. Ask two questions:

• Can the new value be computed from the old one by a formula the database can evaluate (stock - 1, balance + amount), or does it come from a decision made by the application or a person?
• How much time passes between the read and the write? Milliseconds, or the minutes a user spends with a form open?

The answers almost decide the approach on their own.

Tool one: the atomic update

If the new value can be derived from the old one, never pull the read into the application. Let the database do the arithmetic:

UPDATE products
   SET stock = stock - 1
 WHERE id = $1 AND stock > 0
RETURNING stock;

This single statement locks the row, sees the current value and applies the condition. If no row comes back, the item is out of stock; there is no separate "check first" query to race against. It is the fastest, simplest and least error-prone option, and it should be your first choice for counters, balances, quotas and inventory.

The classic mistake is getting used to the ORM's "load the object, change a field, save" flow and writing counter increments the same way. The code reads nicely and passes tests, then loses increments under concurrent load. Most ORMs offer a direct increment or a raw-expression update. Use it for counters.

Tool two: pessimistic locking

When the decision is made in application code and the read and write live inside the same short transaction, lock the row as you read it:

BEGIN;
SELECT * FROM orders WHERE id = $1 FOR UPDATE;
-- application decides
UPDATE orders SET status = 'approved' WHERE id = $1;
COMMIT;

A second transaction reaching the same SELECT ... FOR UPDATE waits until the first finishes, then sees the current row. In PostgreSQL under READ COMMITTED, the waiting transaction re-evaluates the WHERE clause against the updated row once it acquires the lock, so a row that no longer matches simply is not returned. People who do not know this are surprised when "I locked it, but no row came back."

If you do not want to wait, FOR UPDATE NOWAIT fails immediately and FOR UPDATE SKIP LOCKED skips locked rows. The latter is ideal for job queue tables where several workers must not grab the same job, but it is wrong for any read that needs to be consistent: skipped rows are simply absent from the result.

Where pessimistic locking bites:

Holding the lock too long. An outbound HTTP call, a file upload or waiting for confirmation inside the transaction keeps the lock open for seconds. Other requests queue behind it, the connection pool fills up, and the problem surfaces as "the database got slow," which looks unrelated to locking. Rule: no network calls inside a transaction that holds row locks.

Lock ordering. Two transactions locking two rows in opposite order deadlock. The database kills one, but if you see this regularly, always lock multiple rows in the same order, for example sorted by primary key.

User think time. Taking a lock when a form opens and releasing it on save does not work; the transaction ends when the HTTP request ends. The only way to hold something across user think time is an application-level "this record is being edited by X" flag, which brings its own timeout and abandoned-lock problems. For this case the right tool is the next one.

Tool three: optimistic concurrency

Add a version column. Read the version with the row, and only write if it has not changed:

UPDATE documents
   SET title = $2, body = $3, version = version + 1
 WHERE id = $1 AND version = $4;

Zero affected rows means someone wrote before you. No lock is held, and hours may pass between read and write. This is the right approach for any screen where people edit data, for long-lived forms and for distributed clients. JPA and Hibernate provide it through @Version, Rails through a lock_version column.

HTTP has a standard form of the same idea. The server returns the resource with an ETag header, and the client sends that value back in If-Match when updating. If it no longer matches, the server answers 412 Precondition Failed. To forbid unconditional updates entirely, answer 428 Precondition Required to requests that lack If-Match. If-Match uses strong comparison, so a weak W/ ETag never matches; generate strong ETags for versioned resources.

The traps here are sneakier, because the code appears to work:

Taking the version from the wrong place. The most common bug: the save request arrives, the server reloads the record from the database, modifies the fields and saves. The version check always passes, because it compares against a version read a millisecond ago, not the one the user saw. The version must come from the client: a hidden form field, the If-Match header or the request body.

Using a timestamp as the version. updated_at is tempting, but two writes can land in the same millisecond, the column precision may be lower than the application's, and fractions can be truncated during serialization. Use an integer counter.

Blindly retrying a conflict. Resending the same request after zero affected rows means writing stale data under a fresh version number, which is the lost update reproduced by hand. There are two correct reactions. If the change is computed by the machine, re-read the current row, reapply the operation and retry a bounded number of times. If the change comes from a person, show them the conflict: "this record changed while you were editing it," with the difference.

Hot rows. When many requests write to the same row, optimistic concurrency produces a conflict and a retry on nearly every write, and the success rate falls as load rises. For such rows switch to an atomic update or to a queue that serializes writes through a single worker.

Doesn't the isolation level handle this?

Partly, and it depends on the database. In PostgreSQL under REPEATABLE READ, when two transactions update the same row the second fails with could not serialize access due to concurrent update (SQLSTATE 40001). The lost update is prevented, but the price is that the application must catch that error and rerun the entire transaction. Code that does not will return random 500s. SERIALIZABLE catches a wider set of anomalies and demands the same retry discipline.

MySQL InnoDB behaves differently. Under its default REPEATABLE READ, a plain SELECT reads from a snapshot while UPDATE operates on the current row. Code that reads, computes in the application and writes back still loses updates at that level. The assumption that "the default isolation level protects me" breaks silently when the database changes.

Isolation also does not cover user think time, since a transaction does not outlive an HTTP request. Treat it as an extra safety net for short transactions, not a replacement for a version column.

Two places people miss

ORMs that write every column. Some ORMs write the whole row on save instead of only the changed fields. One request edits the title, another edits the description, and although they touch different columns one overwrites the other. Check whether your ORM updates only dirty fields, and use a version column regardless.

JSON and array columns. Appending to a list inside a JSON document by reading the document and writing all of it back loses one of two concurrent appends. In PostgreSQL update in place with jsonb_set or ||, or move a frequently changing list into its own table.

Which one to choose

Taking a side: make the atomic update your default. Wherever the database can do the computation, let it, and both locking and retry logic disappear. Use optimistic versions on every resource that people edit, and support ETag and If-Match in your API from day one, because adding it later means changing every client. Reserve pessimistic locks for short transactions that must change several rows consistently and contain no network calls.

Avoid optimistic concurrency where writes concentrate on a handful of rows; the conflict rate will make it useless. Avoid pessimistic locks anywhere you cannot control how long the transaction lasts.

One practical closing suggestion: concurrency tests are how you catch these bugs. A small test that fires twenty parallel requests at the same record and asserts the expected final value will catch every read, modify, write path that slipped through code review.