Add Optimistic Locking with a Version Column

Prevent lost updates by adding a version column, updating with a version predicate, and returning 409 when two writers modify the same row concurrently.

~25 mindatabaseconcurrencypostgresql

Task

Detect concurrent edits to the same record without holding a database lock for the duration of the user's editing session.

When to use this

Two users open the same document and save different changes. A blind UPDATE lets the last writer silently overwrite the first, and neither user knows data was lost.

Prerequisites

A relational database with transactions
A table that has a stable primary key
An API layer that can return 409 Conflict

Steps

1. Add a version column with a default of 1 and make it non-null.

ALTER TABLE documents ADD COLUMN version integer NOT NULL DEFAULT 1;

Expected output: Existing rows have version 1 and new rows start at 1.

2. Update the row only when the client's version still matches, incrementing the version in the same statement.

const result = await pool.query(
  `UPDATE documents
      SET content = $1,
          version = version + 1,
          updated_at = now()
    WHERE id = $2 AND version = $3
    RETURNING version, updated_at`,
  [content, id, expectedVersion]
);

if (result.rowCount === 0) {
  return res.status(409).json({ error: 'document was modified' });
}
res.json(result.rows[0]);

Expected output: A stale version updates zero rows and the API returns 409; a current version increments and returns the new version.

3. Verify the conflict path with two concurrent updates using the same starting version.

node test/concurrent-update.mjs

Expected output: Exactly one update succeeds and the other receives a conflict.

Troubleshooting

Problem

The update always returns 409

Solution

The client is not sending the version it read, or the version is parsed as a string and compared incorrectly. Cast explicitly to integer.

Problem

The version changes without a user edit

Solution

A background job is updating the row without incrementing the version or without using the predicate. Audit all write paths.

Limitations

The client must carry the version through the edit session; a stateless form needs a hidden field or a read-before-write step.
Optimistic locking does not prevent conflicts, it detects them; the UI must offer a merge or reload path.

Why the last write wins

A blind UPDATE ... WHERE id = $1 overwrites whatever is currently stored. If Alice loads version 3, Bob loads version 3, and both save, Alice’s change disappears when Bob saves. The database did exactly what it was asked; the application failed to express the expectation that the row had not changed since it was read.

Adding a version predicate turns that expectation into a condition. The update applies only when the stored version equals the version the client read. The increment happens atomically in the same statement, so two concurrent updates cannot both satisfy the predicate.

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

Return a conflict, not a silent overwrite

When rowCount is zero, the row was deleted or its version changed. Return 409 with the current version if it is safe to reveal, and let the client reload and merge. Do not retry the update automatically with a fresh version: that would overwrite the other writer’s change and defeat the purpose.

Test concurrency

A unit test that calls the update twice sequentially does not prove the predicate works. Start two transactions or two parallel requests with the same starting version and assert that exactly one succeeds. In PostgreSQL, you can also use SELECT ... FOR UPDATE for pessimistic locking, but that holds a lock while the user edits and is a poor fit for long sessions.

Verify the conflict path

Expected outcome: two concurrent writers using version 3 produce one successful update to version 4 and one 409 response. Confirm that the row’s content matches the winning writer and that the losing writer can reload and retry. If both writes succeed, the predicate is missing or the version is not being sent by the client.