Database

Use Optimistic Locking to Prevent Lost Updates

Detect concurrent edits with version columns or conditional updates instead of silently overwriting newer data.

A lost update occurs when two users read the same record, make different changes and the later save overwrites the earlier one. Optimistic locking detects that the record changed after it was read, allowing the application to ask the user to review or retry.

Add a version field

A row can include an integer version that increments whenever the record changes. When saving, update only if the stored version still matches the version originally read. If no row is affected, another update occurred and the operation should be treated as a conflict.

Use conditional updates

An update can include both the record identifier and expected version in its WHERE clause. This check and the write happen atomically in the database. Do not perform a separate read-then-write check without a transaction or condition, because another update can occur between the two statements.

Handle conflicts in the interface

A conflict is not necessarily a server error. Explain that the record changed and offer a way to reload or compare values. Avoid automatically retrying a user's edit against newer data unless the merge behavior is well-defined.

Test concurrent behavior

Use two independent database connections in integration tests to simulate competing updates. Verify that exactly one update succeeds for the same expected version.

Checklist

  • Add a version or comparable concurrency token.
  • Include the expected version in the update condition.
  • Treat zero affected rows as a conflict.
  • Provide a clear user recovery path.
  • Test with concurrent database connections.
Share this page Help other developers discover this resource.