Optimistic Locking — Version Column

DatabasesMidconcurrencydb

The Question

How does optimistic locking with a version column work?

What a Strong Answer Covers

• Version column
• conditional UPDATE
• retry on conflict.

Senior-Level Answer

Optimistic locking is a concurrency control strategy that avoids holding database locks during reads. Instead of locking a row when you read it, you record its current version, perform your computation, and then verify the version hasn't changed at write time. If another transaction modified the row between your read and your write, the version check fails and your update is rejected — you then retry with fresh data.

The implementation adds a `version` column (integer or timestamp) to the table. The pattern is:

1. Read: `SELECT id, balance, version FROM accounts WHERE id = 42` — returns version = 7. 2. Process: compute the new balance in application code. 3. Write: `UPDATE accounts SET balance = 950, version = 8 WHERE id = 42 AND version = 7`. 4. Check rows affected: if 0 rows updated, a concurrent modification occurred — retry from step 1.

The `WHERE version = 7` clause is the guard. If another transaction incremented the version to 8 before your update, your WHERE clause matches 0 rows, the update silently does nothing, and your application detects the conflict via `affected rows = 0`.

Optimistic locking is well-suited for low-contention scenarios where conflicts are rare. The read path has no overhead — no locks held, no blocking. Under high contention, retry storms can develop: many concurrent writers all reading the same version, all but one failing, all retrying — creating thundering herd behavior that can be worse than pessimistic locking.

ORMs like Hibernate and JPA support optimistic locking natively via `@Version` annotations. The framework handles version increment and conflict detection automatically, throwing `OptimisticLockException` on conflict.

Timestamp-based versions (using `updated_at`) are less reliable than integer counters because two updates within the same millisecond can produce identical timestamps, creating a false sense of no conflict.

What Separates a 2/3 from a 3/3

Common Mistakes

• Not checking affected rows after the UPDATE — without this check, silent lost updates occur even with the version column.
• Using updated_at timestamp as the version column without accounting for same-millisecond collision false negatives.
• Claiming optimistic locking is always better than pessimistic — under high contention it can cause retry storms worse than row locking.
• Forgetting to increment the version number in the UPDATE statement.

Follow-Up Questions

• What happens if two transactions read the same row at version 5 and both try to update it? — First writer wins — updates to version 6. Second writer's WHERE version=5 matches nothing. It detects 0 rows affected and must retry.
• When would you prefer SELECT FOR UPDATE over optimistic locking? — High-contention scenarios where many concurrent writers race on the same rows — retry storms under optimistic locking can be worse than upfront pessimistic locking.
• Why is an integer version column more reliable than an updated_at timestamp? — Integers increment atomically per update. Timestamps can collide if two updates happen within the same clock resolution window.
• How does Hibernate's @Version annotation implement optimistic locking under the hood? — Hibernate appends AND version = :currentVersion to UPDATE statements and increments the version field automatically. Throws OptimisticLockException on 0-row update.

Related Questions

• ACID
• Indexes
• Clustered vs Non-clustered
• Isolation Levels
• Normalization