Optimistic Locking with Version Columns

Optimistic locking uses a version column to detect whether another transaction changed a database row after it was read. An update succeeds only when the stored version still matches the version the application originally read.

A typical implementation adds a numeric `version` column to a table. When an application reads a row, it also reads its current version, such as `5`.

When the application later updates the row, the SQL includes the previously read version in its `WHERE` clause. Conceptually, the update is `UPDATE account SET balance = ?, version = version + 1 WHERE id = ? AND version = ?`.

If another transaction has already updated the row, its version is no longer `5`, so the `UPDATE` affects zero rows. The application can then detect the conflict instead of silently overwriting the other transaction's changes.

In Java persistence frameworks such as JPA, `@Version` identifies a field used for optimistic version checking. The persistence provider normally increments the version when the entity is successfully updated and raises an optimistic-locking exception when the expected version no longer matches.

Optimistic locking is useful when conflicts are relatively uncommon because transactions do not normally hold database locks while application code works with the data. If conflicts are frequent or a transaction must prevent concurrent access immediately, pessimistic locking may be more appropriate.

Key points

• A version column detects concurrent modifications without holding a long-lived database lock.
• The update checks the previously read version in its `WHERE` condition.
• Zero updated rows indicates that the expected version is stale.
• JPA commonly uses `@Version` for automatic optimistic version checking.
• A conflict must be handled explicitly, often by retrying, refreshing, or reporting the conflict.

Interview questions

How does the version column participate in an UPDATE?

Suppose the application read a row with version `5`. The generated update can require `version = 5` and increment the version as part of the successful update. If another transaction already changed the row, the database contains a different version, so the condition matches nothing. The application or persistence provider can interpret that result as an optimistic-locking conflict.

What does `@Version` mean in JPA?

`@Version` marks an entity attribute as the version used for optimistic concurrency control. The persistence provider uses that value when updating or deleting the entity. It verifies that the database version is still the expected one and advances the version after a successful modification. If another transaction changed the entity first, the operation can fail with an optimistic-locking exception.

What should an application do after an optimistic-locking conflict?

The correct response depends on the business operation. The application might reload the current entity and ask the user to reconcile changes, or it might retry when the operation is safe to repeat. A retry should normally start from fresh state rather than reuse stale entity data. For non-idempotent operations, blindly retrying can cause incorrect business effects.

What problem does optimistic locking with a version column solve?

It prevents one transaction from silently overwriting changes made by another transaction after the first transaction read the row. The application remembers the row's version when it reads it. The later update includes that expected version in its condition. If the version changed, the update does not succeed and the conflict can be handled.

When would you choose pessimistic locking instead?

Pessimistic locking can be appropriate when contention is high or when the application needs to prevent conflicting access during a transaction rather than discover the conflict at commit time. For example, a transaction may select a row using a database locking mode so competing transactions must wait or fail according to the database's behavior. This can reduce repeated conflict handling but can increase blocking and deadlock risk. The choice depends on contention, transaction duration, workload, and database behavior.