Optimistic Locking: Detect Conflicting Updates with a Version Column

Optimistic locking detects when a record has changed since a caller last read it. Instead of holding a database lock throughout a human editing session, the application includes an expected version in a conditional update. A successful change increments that version; a stale submission affects no matching row and must be handled as a conflict or another relevant outcome. This technique is especially useful for settings, documents, and administrative forms. It is not a replacement for transactions when a business rule spans several records.

optimistic locking: a practical implementation workflow

Model the editing contract before the SQL

Return a version alongside editable fields when loading a record. Require the client to submit that expected version with the change, and validate its type and supported range. Store the version in the database as a non-null value with a defined initial state. Do not ask the client to invent the next version. The database update owns the increment. A timestamp can have precision and collision problems if used carelessly; a dedicated integer version often makes the intended comparison clearer for a simple record-editing workflow.

Make the comparison and write one operation

The key step is an UPDATE whose WHERE clause includes both the authorized record identity and expected version. Increment the version in that same statement and inspect the result. Performing a SELECT comparison and later issuing an unconditional UPDATE leaves a race between those operations. PostgreSQL RETURNING can give the caller the new values after a match. At stronger isolation levels, a concurrent change can also produce a transaction error requiring appropriate handling. Test the actual database and isolation configuration instead of assuming identical behavior across every engine.

Explain a zero-row result carefully

No returned row can indicate a stale version, a missing record, or a record outside the caller access scope. Decide which distinctions your API should reveal and avoid leaking another account existence through an error message. Within an already authorized editing workflow, a conflict response can offer to reload current data and preserve unsaved changes for comparison. Use an appropriate documented HTTP contract, such as a conflict response for this application design. Do not return success when nothing changed, and do not silently overwrite the newer value as a convenience.

Retry according to business semantics

A stale document edit usually requires user input or a deliberate merge. Automatically rereading and retrying the same replacement values can merely hide the lost-update problem. Some operations, such as an independently defined increment, may be better represented by a different atomic statement. Choose the operation based on intent rather than treating every mutation as whole-record replacement. If retrying a transaction, bound retries and repeat the full necessary read-and-write logic. External side effects should not be repeated accidentally during database conflict recovery.

Cover all writers and related invariants

Every path that changes the protected data must maintain the version, including jobs, imports, and administrative tooling. A writer that bypasses the increment can make stale data look current. For multi-record rules, use the required constraints, transaction isolation, or locking strategy in addition to a version column. Optimistic locking also does not remove ordinary database locks during an UPDATE. Its benefit is avoiding a long-lived editing-session lock while detecting stale writes, not eliminating concurrency costs or guaranteeing that conflicts never occur.

Code example

This PostgreSQL statement assumes a documents table with id, tenant_id, body, and a non-null integer version. Bind all four values through the driver. Interpret the returned row count and handle transaction-level concurrency errors separately.

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

Worked example

Two authorized editors in a hypothetical documentation tool both load version 7. The first submits an update that matches version 7 and receives version 8. The second submits the old version, so the conditional write cannot overwrite the first change. The interface retains the second editor text and offers current content for comparison. This example does not claim automatic merge behavior. A background import also increments versions, ensuring that a user editing before the import gets the same clear conflict protection.

Verification checklist

• Compare the version inside the conditional UPDATE.
• Return the incremented version after a successful match.
• Preserve unsaved edits when a conflict occurs.
• Require every protected writer to update the version.

Practice task

Create a disposable documents table and load the same record through two clients. Submit two different replacement bodies using the same expected version. Assert that only one succeeds and that the other does not overwrite it. Repeat with a background update and with a record outside the tenant scope. Add a UI conflict state that preserves user input. Finally, identify a business rule involving two records and explain why this single-row pattern alone does not establish that rule consistency.

Sources and related reading

Consult PostgreSQL UPDATE and RETURNING; PostgreSQL transaction isolation for API contracts and implementation details. Continue with Database Transactions and Http Status Codes Api for related concepts.