Optimistic Locking Version Column: Preventing Lost Updates

The lost update problem, reproduced step by step

A lost update happens when two writers read the same row, each change their copy in application memory, and each write the whole result back. The second write replaces the first one. No error is raised and no constraint fails.

Take an orders row with id 42, status = 'pending' and address = 'Old St'. A user edits the address while a background job marks the order paid. Both code paths load the entity and save all of its columns.

The final row is paid, Old St. The user's address change is gone.

Wrapping each request in a transaction does not prevent this under PostgreSQL's default READ COMMITTED level. Each statement sees data committed before that statement started, so both SELECTs return the same row. If the two UPDATEs overlap, B's UPDATE waits on A's row lock. When A commits, B re-checks its WHERE id = 42 against the new row version. The condition still matches, so B goes ahead and writes values it computed from the stale read. The database protected the row during the write. It never learned that B's values were derived from an older read.

Optimistic locking closes that gap: a write succeeds only if the row has not changed since it was read. This applies to any backend team with user-edited or job-updated rows in a single relational database.

Copy-- Reproduce the lost update in two psql sessions (READ COMMITTED, the default).
CREATE TABLE orders (
  id      integer PRIMARY KEY,
  status  text NOT NULL,
  address text NOT NULL
);
INSERT INTO orders VALUES (42, 'pending', 'Old St');

-- t1, session A (user edit)
BEGIN;
SELECT status, address FROM orders WHERE id = 42;  -- pending, Old St

-- t2, session B (payment job)
BEGIN;
SELECT status, address FROM orders WHERE id = 42;  -- pending, Old St

-- t3/t4, session A: app changed address in memory, saves every column
UPDATE orders SET status = 'pending', address = 'New St' WHERE id = 42;

-- t5, session B: app changed status in memory, saves every column.
-- This blocks on A's row lock until A commits.
UPDATE orders SET status = 'paid', address = 'Old St' WHERE id = 42;

-- session A: B's UPDATE now re-checks WHERE id = 42, which still matches
COMMIT;

-- session B: no error; values computed from the stale read are written
COMMIT;

-- Either session:
SELECT status, address FROM orders WHERE id = 42;  -- paid, Old St (New St is lost)

Two psql sessions at PostgreSQL's default READ COMMITTED level run the timeline from the table, and session B's full-row write silently overwrites session A's address change.

How a version column works: schema and the conditional UPDATE

The mechanism needs one extra column and one extra predicate. Add an integer version column declared NOT NULL with a default of 0 (or 1). The constraint matters because a NULL never matches version = ?, so a row with a null version can never be updated. The default means existing inserts keep working without changes. Every write you want protected must increment the column. A single code path that skips the increment makes the check meaningless for every writer.

Why an integer and not something else

The conditional write

The core statement is UPDATE orders SET status = ?, version = version + 1 WHERE id = ? AND version = ?, with the version the client originally read. The database evaluates the predicate and the increment atomically, so two writers cannot both match the same version. After the statement runs, check the affected row count. A count of one means the write succeeded and the new version is the old version plus one. A count of zero means the write was rejected.

A zero count is ambiguous on its own, so issue a follow-up SELECT version FROM orders WHERE id = ?. If no row comes back, the record was deleted and the correct response is "not found". If a row comes back with a different version, the write lost a race and should be reported as a conflict.

Copyimport sqlite3

class ConflictError(Exception): pass
class NotFoundError(Exception): pass

conn = sqlite3.connect(":memory:")
conn.execute("""
    CREATE TABLE orders (
        id      INTEGER PRIMARY KEY,
        status  TEXT    NOT NULL,
        version INTEGER NOT NULL DEFAULT 0  -- NULL would never match version = ?
    )""")
conn.execute("INSERT INTO orders (id, status) VALUES (42, 'pending')")  # version = 0

def update_status(conn, order_id, new_status, expected_version):
    with conn:  # commits on success, rolls back on exception
        cur = conn.execute(
            "UPDATE orders SET status = ?, version = version + 1 "
            "WHERE id = ? AND version = ?",
            (new_status, order_id, expected_version),
        )
        if cur.rowcount == 1:
            return expected_version + 1  # caller keeps this for its next write
        # Zero rows matched: find out whether the row is gone or was changed.
        row = conn.execute("SELECT version FROM orders WHERE id = ?",
                           (order_id,)).fetchone()
    if row is None:
        raise NotFoundError(f"order {order_id} was deleted")
    raise ConflictError(f"order {order_id} is at version {row[0]}, "
                        f"not {expected_version}")

# Two clients both read version 0; only the first write can match it.
print(update_status(conn, 42, "paid", 0))           # -> 1
try:
    update_status(conn, 42, "cancelled", 0)
except ConflictError as e:
    print("conflict:", e)                            # lost the race
conn.execute("DELETE FROM orders WHERE id = 42")
try:
    update_status(conn, 42, "shipped", 1)
except NotFoundError as e:
    print("not found:", e)                           # row no longer exists

A runnable SQLite example showing the NOT NULL DEFAULT 0 version column and the conditional UPDATE with its row-count check, plus a follow-up SELECT that reports a zero-row result as either a version conflict or a deleted record.

Implementing it in the ORM and on aggregates

Most ORMs can generate that conditional write for you. You mark one attribute as the version field, through an annotation, a mapping option, or a model setting, and the ORM changes every update it issues for that entity. It appends AND version = :loaded_version to the predicate, increments the column in the SET clause, and checks the affected-row count. A count of zero raises a stale-object or optimistic-lock exception. Names and defaults differ between frameworks, and some also check the version on deletes. Confirm the exact behavior in your framework's documentation instead of assuming it.

Paths that skip the check

The protection only applies to writes that go through entity tracking. Bulk update statements, raw SQL, and query-builder updates usually bypass it. They don't add the version predicate, and they often don't increment the version either. That second gap is the more dangerous one, because a client holding an old version can then overwrite the change without detection. Any write that bypasses the ORM must include version = version + 1 explicitly.

Version the aggregate root

Concurrency is a property of the whole aggregate, so the version belongs on the root. An order with edited line items has changed even if the orders row has not. Many ORMs only bump the version when the root's own columns are dirty. When only a child changed, force the bump by touching the root or by calling the framework's explicit increment or lock option.

Volatile fields

Columns like last_seen_at or view counters change constantly. If they bump the version, they cause spurious conflicts. Exclude them from versioning or move them to a separate table.

Copy-- Hand-written writes that bypass the ORM's version check.
-- :order_id and :loaded_version come from the client's earlier read.

BEGIN;

-- 1. Only a child row changes. Bump the aggregate root first.
--    This detects a concurrent edit and makes this change visible to
--    any other client holding the old version.
UPDATE orders
   SET version = version + 1
 WHERE id = :order_id
   AND version = :loaded_version;
-- If the affected-row count is 0, someone else changed the order.
-- ROLLBACK and report a conflict instead of continuing.

UPDATE order_line_items
   SET quantity = 3
 WHERE order_id = :order_id
   AND id = :line_item_id;

COMMIT;

-- 2. Bulk or admin update. Without the increment, clients holding an
--    old version would still pass their own version check and silently
--    overwrite this change.
UPDATE orders
   SET status  = 'archived',
       version = version + 1
 WHERE status = 'delivered'
   AND delivered_at < now() - interval '90 days';

-- 3. A volatile counter lives in its own table, so frequent writes
--    never touch orders.version or cause spurious conflicts.
CREATE TABLE order_view_stats (
    order_id    bigint PRIMARY KEY REFERENCES orders(id),
    view_count  bigint      NOT NULL DEFAULT 0,
    last_seen_at timestamptz
);

UPDATE order_view_stats
   SET view_count = view_count + 1, last_seen_at = now()
 WHERE order_id = :order_id;

Raw SQL that keeps optimistic locking intact outside the ORM: it bumps the root version when only a line item changes, increments the version in a bulk update, and moves a volatile view counter into a separate unversioned table (PostgreSQL syntax).

Carrying the version across the API: ETags, If-Match, 409 and 412

The client has to send back the version it read. Otherwise the server cannot tell a fresh write from one based on stale data. Return the optimistic locking version column on every read, either as a version field in the body or as an ETag header such as "42". Then require it on every write. If a write arrives without it, reject it with 428 Precondition Required rather than applying it unconditionally. A missing version usually comes from an outdated client, and accepting it silently brings back the lost update problem.

Use one error payload for 409 and 412. It should contain an error code, the currentVersion, and, if the caller may read it, the currentState. With that, a client can show a diff or retry without a second GET. Leave the state out when it would expose fields the caller cannot see.

ETag pitfalls:

• Weak vs. strong. If-Match uses strong comparison, so a weak ETag (W/"42") never matches. Emit strong ETags for versioned resources.
• Intermediaries. A proxy or compression layer may weaken or strip ETags. Caches can also serve a stale representation with an old ETag. Mark these responses so shared caches revalidate them.
• Timestamps as versions. A timestamp reveals when the record changed. Two writes within the same clock tick can also produce the same value, which means a real conflict goes undetected. Use a monotonic integer instead.

Copyimport express from "express";
import { Pool } from "pg";

const app = express();
app.use(express.json());
const db = new Pool(); // connection settings come from the standard PG* env vars
const COLS = "id, status, total, version";

// Strong ETag derived from the integer version column, e.g. "42"
const etag = (v: number) => `"${v}"`;

app.get("/orders/:id", async (req, res) => {
  const { rows } = await db.query(`SELECT ${COLS} FROM orders WHERE id = $1`, [req.params.id]);
  if (!rows[0]) return res.sendStatus(404);
  // no-cache: shared caches must revalidate instead of serving a stale ETag
  res.set({ ETag: etag(rows[0].version), "Cache-Control": "no-cache" }).json(rows[0]);
});

app.put("/orders/:id", async (req, res) => {
  const ifMatch = req.get("If-Match");
  // Never apply an unconditional write: an old client would bring back lost updates
  if (!ifMatch) return res.status(428).json({ error: "precondition_required" });
  // Strong comparison only: a weak tag such as W/"42" never matches
  const m = /^"(\d+)"$/.exec(ifMatch.trim());
  const expected = m ? Number(m[1]) : -1;

  // Compare-and-set: the update only applies if nobody changed the row since it was read
  const { rows } = await db.query(
    `UPDATE orders SET status = $1, version = version + 1
     WHERE id = $2 AND version = $3 RETURNING ${COLS}`,
    [req.body.status, req.params.id, expected]);
  if (rows[0]) return res.set("ETag", etag(rows[0].version)).json(rows[0]);

  // Zero rows updated: the row is gone or the precondition failed
  const cur = await db.query(`SELECT ${COLS} FROM orders WHERE id = $1`, [req.params.id]);
  if (!cur.rows[0]) return res.sendStatus(404);
  // Same payload shape as the 409 used for body-carried versions
  res.status(412).set("ETag", etag(cur.rows[0].version)).json({
    error: "version_conflict",
    currentVersion: cur.rows[0].version,
    currentState: cur.rows[0], // omit when the caller may not read every field
  });
});

An Express and PostgreSQL handler that returns the version column as a strong ETag, rejects writes without If-Match with 428, uses a conditional UPDATE for the version check, and on a failed precondition returns 412 with the shared error payload containing currentVersion and currentState.

Choosing a conflict handling strategy per operation

A version mismatch says only that someone else wrote first. What to do next depends on what the caller meant to change, so pick the strategy per operation, not per service.

Retry the intent, not the payload. A correct retry re-reads the row, reapplies the operation ("add 5 to stock") to the fresh state, and writes with the new version. Cap the attempts and surface a 409 when they run out. If you blindly retry a client-supplied full-object PUT, you overwrite the newer row with the client's stale snapshot. That is the lost update again, with an extra step in front of it.

Keep side effects outside the retried block. Emails, payment calls, and webhooks inside the loop fire once per attempt. Record them in an outbox table within the same transaction, or send them only after the commit succeeds.

Merging requires the base. You can only tell which fields the user changed if you diff their submission against the version they started from. Without that base, every field looks edited.

Long-lived edit sessions go stale often, because a form left open for an hour will usually be behind. For these, prefer a diff view over a bare rejection. Consider a soft "someone else is editing" indicator so users learn about conflicts early, before they have typed for twenty minutes.

Testing concurrency deterministically

Concurrency bugs rarely show up under normal test load, so don't wait for a race to happen. Create the race explicitly. Open two separate sessions or units of work and load the same row in each, so both copies hold the same version. Modify and commit the first copy. Then modify the second and assert that its save raises your ORM's stale-object or conflict exception. Repeat the sequence through the HTTP layer: fetch the resource twice to capture the same ETag, send a successful PUT with the first, and assert that the second returns 409 (or 412 if the request used If-Match). The test is deterministic because the steps run in a fixed order. You don't rely on threads or timing.

A stress test for lost updates

Next, fire many parallel requests that each increment a counter by one, with retry-on-conflict enabled. Assume 50 workers and a starting value of 0. The final value must be exactly 50, and the version must have advanced by the same amount. A lower value means at least one write skipped the version check.

Catching bypass paths

The stress test is also how you audit code that touches the table outside the ORM. Run the same parallel workload while each of these runs alongside it:

• a bulk SQL statement
• a data migration
• a background job
• an admin script

Then assert the count again. Any UPDATE that changes data without incrementing version lets a concurrent request overwrite it silently, and the counter will come up short. Fix each offender by adding version = version + 1 and the version predicate, or by routing it through the same repository code the API uses.

When to avoid it: pessimistic locks, atomic updates and other alternatives

Optimistic locking assumes conflicts are rare. On a hot row, such as a view counter, stock for a popular item, or a shared settings record, that assumption fails. Most writers lose the race, retry, and collide again. The retries add load exactly when the row is busiest.

Keep FOR UPDATE transactions short. A row lock held across a network call stalls every other writer behind it.

The multi-row case is write skew. Two transactions each read the same set of rows, each update a different row, and both version checks pass, yet the invariant ("at least one on-call engineer") is broken. To prevent it, bump a version on the shared parent, run under SERIALIZABLE and retry serialization failures, or encode the rule as a unique, check, or exclusion constraint.

Optimistic locking also costs engineering time. Every write endpoint must return 409 or 412, and every client must handle it. For low-value data such as a UI preference, a "reload and redo your edit" prompt can annoy users more than an occasional lost update would. For data that matters, a conflict caught at write time is far cheaper than tracking down silent corruption weeks later.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to optimistic locking version column, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.