Implementing Optimistic Locking with Version Columns

1. What optimistic locking is and when it makes sense

Optimistic locking assumes write conflicts are rare and therefore forgoes database locks while reading. Instead of locking a row on read, as pessimistic locking does, the application reads the data freely, works with it, and only checks at write time whether the row has been changed by someone else since. This optimism assumption is realistic in many web applications, because the time between reading and writing, driven by user input, often spans several seconds, and genuine conflicts rarely happen at exactly the same moment.

The central advantage of optimistic locking is that no database lock needs to be held for the duration of a user interaction. A form a user leaves open for minutes before saving would, under pessimistic locking, lock a row for the entire time and block other users. With optimistic locking, no lock exists until the actual write happens, which significantly increases throughput in systems with many concurrent readers and few actual conflicts.

The following sections show the concrete version column pattern, from the schema change through the crucial UPDATE statement to the question of how application code should react to a detected conflict.

2. The version column pattern: schema design

The version column pattern adds an extra integer column to every relevant table, usually called version, which is incremented by one on every successful UPDATE. This column is the central building block of optimistic locking: it makes every row version uniquely identifiable without the database itself needing to implement any additional locking logic. All conflict handling happens in the WHERE clause of the UPDATE statement.

-- Schema design for optimistic locking with a version column
CREATE TABLE products (
  product_id   INT PRIMARY KEY,
  name         VARCHAR(255) NOT NULL,
  price        NUMERIC(10,2) NOT NULL,
  stock        INT NOT NULL DEFAULT 0,
  version      INT NOT NULL DEFAULT 1
);

-- Adding the column to an existing table
ALTER TABLE products ADD COLUMN version INT NOT NULL DEFAULT 1;

-- No index needed, version is always used
-- together with the primary key in the WHERE clause

An important part of schema design is that the version column must never be set directly from an application form field, it must only be incremented by the database or a controlled application layer. If a user could manipulate the version number, all conflict handling would become meaningless, because any arbitrary version could be faked as valid.

3. The update with WHERE version=x

The core of optimistic locking is an UPDATE statement that carries the last-read version number in its WHERE clause and increments the new version by one in the SET part. If the version at write time still matches the one seen at read time, the UPDATE affects exactly one row and the write is considered successful. If another transaction has changed the row in the meantime, thereby incrementing the version, the UPDATE affects zero rows, which is the clear indicator of a conflict.

-- Step 1: read the row, remember the current version
SELECT product_id, name, price, stock, version
FROM products
WHERE product_id = 42;
-- Result: version = 7

-- Step 2: prepare the change (in application code, not in SQL)
-- New price: 29.99

-- Step 3: update with version check
UPDATE products
SET price = 29.99,
    version = version + 1
WHERE product_id = 42
  AND version = 7;

-- Application code checks the number of affected rows:
-- 1 row affected  -> update succeeded, no conflict
-- 0 rows affected -> conflict, someone else wrote in the meantime,
--                    version is now higher than 7

What matters most about this pattern is that the entire conflict check happens within a single atomic SQL statement. There is no separate SELECT for a version check immediately before the UPDATE, which would itself be prone to a race condition. The database checks the condition and performs the write in one single, atomic step, which is why optimistic locking works reliably even under high concurrency.

4. Conflict detection: checking affected rows

Conflict detection in optimistic locking depends entirely on the application code explicitly checking the number of affected rows after every UPDATE. Almost every database driver returns this value, in JDBC as the return value of executeUpdate(), in PDO as rowCount(), in most modern ORMs as part of the result object. If this value is ignored, the entire protective effect of optimistic locking disappears, because a failed UPDATE with zero affected rows would silently be treated as success.

A common mistake is to instead rely on the exit code or the absence of an exception: an UPDATE with a WHERE condition that matches no row is not an SQL error, it is a perfectly valid statement that just happens to change zero rows. That is why checking the affected row count must always be an explicit step in application code, directly after every version-guarded UPDATE.

5. Conflict handling in application code

When a conflict is detected because the UPDATE affected zero rows, the application must decide how to react. The three common strategies are: showing the conflict to the user and having them reload the current data, automatically reloading the current version and retrying the change, or offering a field-by-field merge of the changes. Which strategy fits depends heavily on the use case: for a simple product price, a clear notice is usually enough, for a collaborative document, a merge strategy is often the better choice.

-- Pseudocode for application logic with an optimistic locking conflict
-- (modeled after common ORM and database driver APIs)

-- function updateProductPrice(productId, newPrice, expectedVersion) {
--   const result = db.execute(
--     "UPDATE products SET price = ?, version = version + 1 " +
--     "WHERE product_id = ? AND version = ?",
--     [newPrice, productId, expectedVersion]
--   );
--
--   if (result.affectedRows === 0) {
--     // Conflict detected: reload the current row
--     const current = db.query(
--       "SELECT * FROM products WHERE product_id = ?", [productId]
--     );
--     throw new OptimisticLockException(
--       "Product was modified by another user", current
--     );
--   }
--
--   return { success: true, newVersion: expectedVersion + 1 };
-- }

-- Retry strategy with a bounded number of attempts
-- for (let attempt = 0; attempt < 3; attempt++) {
--   try {
--     return updateProductPrice(productId, newPrice, currentVersion);
--   } catch (OptimisticLockException e) {
--     currentVersion = e.currentRow.version;
--     // For simple fields: reapply the change on the new base
--     // For complex changes: surface the error to the user
--   }
-- }

An automatic retry loop only makes sense when the change itself can be combined conflict-free with the new version, for example a relative change like reducing stock. For absolute value changes, such as a new price typed in by a user, an automatic retry is risky because it would silently overwrite another user's change. In that case, it is more correct to make the conflict visible and let the user decide actively.

6. Alternative: timestamp or hash instead of an integer version

Instead of a simple integer counter, the version column can also be implemented as a timestamp that is set to the current time on every UPDATE. This approach has the advantage of doubling as audit information about when a row was last changed, but has the drawback that with a very high update frequency and low timestamp precision, two changes could theoretically receive the same timestamp.

Another alternative is a hash over the relevant column values of the row, computed on every read and compared on write, instead of maintaining a dedicated version column. This approach detects conflicts even when an external change happened outside your own application and never incremented a version column, but costs more computation per read and write. In practice, the classic integer version column is the simplest and most widely used solution, because it is explicit, performant, and easy to reason about.

-- Timestamp-based version column as an alternative
CREATE TABLE documents (
  document_id  INT PRIMARY KEY,
  content      TEXT NOT NULL,
  updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Update with a timestamp check instead of an integer version
UPDATE documents
SET content = 'New content',
    updated_at = CURRENT_TIMESTAMP
WHERE document_id = 17
  AND updated_at = '2026-07-24 10:15:32.123456';
-- 0 affected rows means a conflict here too

-- Trigger that maintains updated_at automatically (PostgreSQL)
CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER documents_set_updated_at
BEFORE UPDATE ON documents
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

7. Optimistic vs. pessimistic locking compared

The choice between optimistic locking and pessimistic locking with SELECT FOR UPDATE depends on the expected conflict rate and the time span between reading and writing. The table below compares both approaches.

8. Limits of optimistic locking

Optimistic locking reaches its limits when the conflict rate is high. With many concurrent writes to the same row, for example a heavily contested stock level during a sale event, repeated failed updates lead to a high number of retries, which in total causes more database roundtrips than a single pessimistic-locking-based UPDATE. Past a certain conflict density, the performance advantage of optimistic locking reverses.

Another risk is an unbounded retry loop with no upper limit: under very high contention on a single row, an application could theoretically retry forever without ever writing successfully. A fixed maximum number of attempts followed by an error surfaced to the user is therefore mandatory, as is a short, randomized backoff between attempts to avoid thundering-herd effects from simultaneous retries.

9. Combining with isolation levels

Optimistic locking is an application-level addition, not a replacement for an appropriate isolation level. Even with a correct version column pattern, READ COMMITTED alone does not protect against non-repeatable reads while computing new values if the application performs several related reads before issuing the version-guarded UPDATE. In such cases, REPEATABLE READ is the safer foundation, combined with the version column for the actual write-conflict detection.

The great advantage of this combination is that the isolation level provides read consistency within the transaction, while the version column specifically detects the write conflict between two separate transactions, each of which runs correctly in isolation on its own. Details on the four isolation levels and their respective guarantees are covered in the deep-dive article on isolation levels from Read Committed to Serializable.

-- Optimistic locking combined with Repeatable Read
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Several related reads, stable thanks to Repeatable Read
SELECT stock, version FROM inventory WHERE product_id = 42;
SELECT reserved FROM reservations WHERE product_id = 42;

-- Computation in application code based on both consistent values

-- Version-guarded UPDATE as the final write
UPDATE inventory
SET stock = stock - 3,
    version = version + 1
WHERE product_id = 42
  AND version = 7;

COMMIT;
-- Repeatable Read secures the read phase,
-- the version column secures the write phase

10. Summary

Optimistic locking with a version column detects write conflicts without holding a database lock while reading. The core pattern is an UPDATE with WHERE version = expectedVersion, using the affected row count as the conflict indicator: one affected row means success, zero rows mean a conflict that application code must handle explicitly. This check must always happen as a separate step after every UPDATE, never assumed implicitly.

Optimistic locking works best for scenarios with rare conflicts and longer time spans between reading and writing, such as forms with user input. Under very high conflict density, pessimistic locking with SELECT FOR UPDATE is often the better choice. Both approaches are not mutually exclusive and can be combined deliberately depending on the table and use case, always embedded in an appropriately chosen isolation level.

11. FAQ: Implementing Optimistic Locking with Version Columns