Prevent lost updates with a conditional UPDATE

Two editors load the same profile. One changes the display name; the other changes the interface language. If the second editor sends an old copy of every field, saving it can silently restore the old name. Putting that save in a transaction does not tell PostgreSQL which version the editor originally saw.

A version-checked update supplies that missing condition: apply this change only if the row still has the revision I read. If you know basic SQL updates and transactions, you can run this example in two sessions. It uses PostgreSQL 17 at Read Committed, an ordinary table without triggers or row-level security, and a profile identified by 7. Authorization and an HTTP interface are separate concerns.

Keep the version with the values it describes

Create a disposable fixture:

CREATE TABLE profiles (
  id bigint PRIMARY KEY,
  display_name text NOT NULL
    CHECK (char_length(display_name) BETWEEN 1 AND 80),
  locale text NOT NULL CHECK (locale IN ('en', 'vi')),
  version bigint NOT NULL DEFAULT 0 CHECK (version >= 0)
);
INSERT INTO profiles (id, display_name, locale)
VALUES (7, 'Ada', 'en');

The small language allowlist belongs to this example; it is not a complete language-tag registry. Both editors initially execute:

SELECT display_name, locale, version FROM profiles WHERE id = 7;

Each receives Ada, en and version zero. The application must retain that version alongside the editable values. A later save should send the version of the loaded form, not fetch the newest version just before writing and pretend the user reviewed it.

David Rice’s 5 March 2003 Optimistic Offline Lock catalog entry describes the gap between a business operation and individual database transactions. An editor may spend minutes thinking between loading and saving. Here, “offline” concerns that separation; it does not require a disconnected browser. The check happens when saving, so the database need not hold a row lock throughout the user’s editing time.

A counter alone does not prevent lost changes

This deliberately weak replay puts both saves in one temporary transaction so you can inspect the result and discard it:

BEGIN;
UPDATE profiles
SET display_name = 'Ada Nguyen', version = version + 1
WHERE id = 7;
UPDATE profiles
SET display_name = 'Ada', locale = 'vi', version = version + 1
WHERE id = 7;
SELECT display_name, locale, version FROM profiles WHERE id = 7;
ROLLBACK;

The result is Ada, vi, version two. The counter increased twice, but the second payload restored the old name. Both SQL updates were valid. ROLLBACK returns the fixture to its starting row for the following examples. The failure is the missing comparison with each editor’s loaded version, not a failure to increment the counter.

A later transaction cannot infer that Ada came from a stale form rather than an intentional name change. That information must be part of the write condition. The UPDATE reference defines which rows qualify through the WHERE clause and which updated values can be returned.

Compare and advance the revision in one statement

Editor A changes the name using the version it actually read:

UPDATE profiles
SET display_name = 'Ada Nguyen', version = version + 1
WHERE id = 7 AND version = 0
RETURNING display_name, locale, version;

Run this outside an open transaction so the successful statement commits. It returns Ada Nguyen, en, version one. The comparison and change occur in the same database statement. The new version is calculated from the row, rather than accepting an arbitrary replacement version supplied by the client.

Editor B still has the original full form, with its language field changed to Vietnamese:

UPDATE profiles
SET display_name = 'Ada', locale = 'vi', version = version + 1
WHERE id = 7 AND version = 0
RETURNING display_name, locale, version;

This returns no rows. It does not raise a SQL exception. The row exists, but its version is now one, so it no longer matches B’s condition. The name remains Ada Nguyen and the language remains en.

View full-size image ↗

The version condition rejects a stale write. It does not choose how two people’s intentions should be combined.

For this primary-key lookup, one returned row means the update happened and zero means no row matched. A missing profile produces zero too. PostgreSQL explicitly documents zero updated rows as a successful command, so exception handling alone cannot recognize this conflict. Check the returned row or your driver’s affected-row count.

Resolve the conflict using the current values

Run the original read again and show B the current name, language and version. If B still intends only to switch the language, it can submit that field against the freshly reviewed version:

UPDATE profiles
SET locale = 'vi', version = version + 1
WHERE id = 7 AND version = 1
RETURNING display_name, locale, version;

The result is Ada Nguyen, vi, version two. This preserves A’s name because the new write deliberately changes only the language. It is a concrete resolution choice for this example, not a universal automatic merge rule. Fields with dependent business meaning may require another decision or another validation step.

Simply replacing B’s expected zero with one while resending the old full payload would also pass the version check. It would restore Ada and lose A’s change anyway. A retry loop that silently fetches the newest version can therefore defeat the very protection it was meant to provide. Refreshing a token is different from reviewing the new state.

A follow-up read is also a new observation. In a local two-session control, B’s update first returned zero because the version was stale; another session then deleted the row before B’s diagnostic read. That read found nothing. It describes the current database, not an atomic explanation of why the earlier update matched zero rows. Keep that distinction when designing conflict and missing-resource messages.

Separate a nonmatch from a database error

Starting from version two, try an unsupported language value:

BEGIN;
UPDATE profiles
SET locale = 'xx', version = version + 1
WHERE id = 7 AND version = 2
RETURNING display_name, locale, version;
ROLLBACK;
SELECT display_name, locale, version FROM profiles WHERE id = 7;

The matching update raises check-constraint error 23514. After rollback, the name, language and version remain unchanged. The constraint documentation explains how the database enforces conditions on stored rows. The attempted version increment does not survive this failed statement.

Use interactive psql for the expected-error example and issue its rollback after the error. If a script stops on errors, its caller must arrange cleanup. In application code, a failed explicit transaction must be rolled back before reusing the connection. By contrast, a zero-row version nonmatch does not put the transaction into an aborted state.

Nor does a nonmatch certify that the submitted values were valid. If the WHERE condition finds no row, there is no updated row on which to evaluate this language check. Validate the request’s representation and interpret the database result separately.

View full-size image ↗

One row, zero rows and an exception are different outcomes. A driver wrapper should preserve that distinction for its caller.

Every relevant writer must honor the revision

From version two, this rollback-only control models an older administrative write that forgets the protocol:

BEGIN;
UPDATE profiles SET display_name = 'Operations change' WHERE id = 7;
UPDATE profiles
SET display_name = 'Old form', version = version + 1
WHERE id = 7 AND version = 2
RETURNING display_name, locale, version;
ROLLBACK;

The result is Old form, vi, version three. The unversioned operations change left the revision at two, so an older form still matched. A version column cannot detect changes that its writers do not record. Inventory your background jobs, scripts and maintenance paths as well as the main endpoint.

The chosen protocol increments the version for every accepted update, including a save that writes the same field value. The number records accepted revisions, not a count of characters changed. A finite bigint counter can also overflow; the local maximum-version check produced an error and preserved the row rather than wrapping. Do not reset the revision to get around that error.

Row identity has a lifecycle too. Deleting a profile and recreating the same identifier with version zero can make an old version-zero form match again. Preserve identity or include a suitable generation in the concurrency token when reuse is possible. The version is also not authorization: a caller who knows it still needs permission to change that profile.

Optimistic saves still use database locks

Two real PostgreSQL connections tested the same expected version concurrently. A held its update open while B’s update waited. When A committed, B rechecked the row and returned zero without a SQL error. When A rolled back instead, B matched version zero and saved its own change as version one.

Those outcomes follow the Read Committed update rules. “Optimistic” describes detecting a conflict at save time across the editing interval; it does not mean PostgreSQL performs the update without locks. The example makes no throughput or lock-free claim.

Finally, a revision is not a request-idempotency key. Repeating A’s already successful request also returns zero because the version advanced. That alone cannot distinguish a previously applied request from another writer’s intervening change, especially after a lost response.

For an exercise, run both possible commit orders using two sessions. Record the returned rows, final field values and revision. Then try the missing identifier, invalid language and unversioned-writer controls. The useful outcome is an application decision for each result, including when the user must inspect a newer profile before choosing what to save.