Implementing Optimistic Concurrency with Version Columns

Part of: Optimistic vs Pessimistic Concurrency Control

This is a runbook for adding version-column-based optimistic concurrency control to an existing table: the schema change, the conditional UPDATE, conflict handling, and retry-with-backoff, in Java, Python, and raw SQL. You need the guarantee model and control-flow background from Optimistic vs Pessimistic Concurrency Control and the broader coordination context from Distributed Coordination & Locking Strategies before applying the steps below — this page assumes you already know why unconditional writes lose updates and jumps straight to implementation.

---

Problem statement and prerequisites

What you are implementing: a version (or row-version) integer column that turns an unconditional UPDATE into a compare-and-swap, so a writer whose read is stale gets a hard, detectable rejection instead of silently overwriting a concurrent change.

Prerequisites:

• A table with a primary key and at least one mutable column that multiple writers can update concurrently.
• Application code currently performing a plain read-modify-write cycle (SELECT then UPDATE with no version predicate) — this is the pattern being replaced.
• A retry policy at the call site; version-column CAS is not useful unless the caller actually retries on conflict rather than surfacing a raw error to the end user.

---

Step-by-step implementation

Step 1 — Add the version column

-- PostgreSQL / MySQL: add an integer version column, default 0 for existing rows
ALTER TABLE orders ADD COLUMN version INTEGER NOT NULL DEFAULT 0;

Back-fill is automatic via the DEFAULT 0 clause — every existing row starts at version 0, and the first update from any writer moves it to 1. Do not reset or reuse version numbers during a later migration; a version column that decreases or wraps reintroduces the ABA problem (a stale writer’s version accidentally matching the current one again).

Step 2 — Write the conditional UPDATE

The core primitive is a single atomic statement: increment the version and apply the change, but only if the version still matches what the caller last read.

-- Raw SQL: compare-and-swap via the version predicate
UPDATE orders
SET    status  = 'shipped',
       version = version + 1
WHERE  id      = 4821
  AND  version = 6;          -- the version this caller read

-- Check the affected row count in application code:
--   1 row updated  → success, new version is 7
--   0 rows updated → conflict: someone else updated this row since the read

This statement is safe under arbitrary concurrency because the WHERE clause evaluation and the row mutation happen inside a single atomic database operation — there is no window between “check the version” and “apply the change” for a second writer to interleave.

Step 3 — Java: JPA @Version

JPA (Hibernate) automates the version-column pattern: annotate a field with @Version, and the provider automatically appends the version predicate to every UPDATE and throws OptimisticLockException on a zero-row-affected result.

@Entity
@Table(name = "orders")
public class Order {

    @Id
    private Long id;

    private String status;

    @Version
    private Long version;   // Hibernate manages the WHERE version = ? and the increment

    // getters/setters omitted
}

@Service
public class OrderService {

    @PersistenceContext
    private EntityManager em;

    public void shipWithRetry(Long orderId, int maxAttempts) {
        int attempt = 0;
        while (true) {
            attempt++;
            try {
                Order order = em.find(Order.class, orderId);
                order.setStatus("shipped");
                em.flush();  // triggers UPDATE ... WHERE id = ? AND version = ?
                return;
            } catch (OptimisticLockException e) {
                if (attempt >= maxAttempts) {
                    throw new ConflictExhaustedException("order " + orderId, e);
                }
                em.clear();  // discard stale entity state before retrying
                sleepWithJitterBackoff(attempt);
            }
        }
    }
}

Step 4 — Python: SQLAlchemy version_id_col

SQLAlchemy’s declarative mapping supports the same automatic pattern via __mapper_args__.

from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm.exc import StaleDataError

Base = declarative_base()

class Order(Base):
    __tablename__ = "orders"

    id = Column(Integer, primary_key=True)
    status = Column(String)
    version = Column(Integer, nullable=False, default=0)

    __mapper_args__ = {
        "version_id_col": version,
    }

import time
import random

def ship_with_retry(session, order_id: int, max_attempts: int = 5) -> None:
    for attempt in range(1, max_attempts + 1):
        try:
            order = session.query(Order).filter_by(id=order_id).one()
            order.status = "shipped"
            session.commit()   # emits UPDATE ... WHERE id = ? AND version = ?
            return
        except StaleDataError:
            session.rollback()
            if attempt == max_attempts:
                raise
            backoff_ms = min(2000, 50 * (2 ** attempt)) + random.randint(0, 100)
            time.sleep(backoff_ms / 1000)

Step 5 — Handle zero-rows-affected explicitly with raw SQL (no ORM)

When not using an ORM, check the affected row count directly and retry with exponential backoff and jitter.

import psycopg2
import time
import random

def update_with_cas(conn, order_id: int, new_status: str, max_attempts: int = 5) -> bool:
    for attempt in range(1, max_attempts + 1):
        with conn.cursor() as cur:
            cur.execute("SELECT version FROM orders WHERE id = %s", (order_id,))
            row = cur.fetchone()
            if row is None:
                return False
            current_version = row[0]

            cur.execute(
                """
                UPDATE orders
                SET status = %s, version = version + 1
                WHERE id = %s AND version = %s
                """,
                (new_status, order_id, current_version),
            )
            conn.commit()

            if cur.rowcount == 1:
                return True   # success

            # 0 rows affected: conflict — back off and retry with a fresh read
            backoff_ms = min(2000, 50 * (2 ** attempt)) + random.randint(0, 100)
            time.sleep(backoff_ms / 1000)

    return False  # exhausted retries — surface as a conflict to the caller

Wire the backoff formula to the same jitter policy used elsewhere in the system — see implementing exponential backoff without overlapping retries for the full derivation of base delay, cap, and jitter width.

---

Verification and testing

Concurrent writer test (raw SQL, two terminals)

-- Terminal 1: read the current version
SELECT id, status, version FROM orders WHERE id = 4821;
-- version = 6

-- Terminal 2: another writer updates first
UPDATE orders SET status = 'cancelled', version = version + 1
WHERE id = 4821 AND version = 6;
-- 1 row updated; version is now 7

-- Terminal 1: attempt the write with the stale version = 6
UPDATE orders SET status = 'shipped', version = version + 1
WHERE id = 4821 AND version = 6;
-- Expected: UPDATE 0 — zero rows affected, conflict correctly detected

Load-test with concurrent processes (bash)

# Launch 20 concurrent writers against the same row and confirm the version
# only ever increments by exactly 1 per successful write, with no lost updates
for i in $(seq 1 20); do
  python update_with_cas.py --order-id 4821 --status "shipped" &
done
wait

psql -c "SELECT id, version FROM orders WHERE id = 4821;"
# version should equal the number of writers whose call returned True,
# not simply 20 - some calls legitimately lose the race and retry

Assert no lost updates end-to-end

# Run N concurrent increments of a counter column and verify the final
# value equals exactly N, proving no update was silently dropped
psql -c "UPDATE counters SET value = 0, version = 0 WHERE id = 'test-counter';"
for i in $(seq 1 100); do
  python increment_with_cas.py --counter-id test-counter &
done
wait
psql -c "SELECT value FROM counters WHERE id = 'test-counter';"
# Expected: value = 100

---

Failure scenarios and debugging

---

SRE checklist

Verify or emit each of the following before shipping version-column concurrency control to production:

1. cas_conflict_rate — Prometheus counter ratio of 0-row updates to total attempted updates on the versioned table; alert if it exceeds 20% over a rolling 60-second window, which signals contention high enough to warrant a pessimistic fallback.
2. cas_retry_attempts_p99 — histogram of retry counts per logical write; a rising p99 indicates growing contention before conflicts start being exhausted outright.
3. cas_retries_exhausted_total — counter incremented whenever a write exhausts max_attempts without succeeding; any non-zero rate must page, since it means a client-visible failure is being returned for what should be a transient conflict.
4. version_decrease_detected_total — should always read zero; a non-zero value indicates a migration or manual data fix violated the monotonic version invariant.
5. Structured log fields on every CAS attempt — entity_id, version_read, version_expected, attempt, outcome (committed / conflict / exhausted) so incident review can reconstruct the exact interleaving of concurrent writers.
6. db_update_latency_p99_ms for the versioned table — a rising baseline latency alongside a rising conflict rate is the earliest signal that the table needs a pessimistic lock or partitioning strategy instead of optimistic retries.

---

Related

• Optimistic vs Pessimistic Concurrency Control — parent page covering the guarantee model, control-flow contrast, and when to choose optimistic concurrency over a pessimistic lock.
• Distributed Coordination & Locking Strategies — the broader coordination reference covering lock acquisition, lease management, and consensus-based deduplication.
• Implementing Exponential Backoff Without Overlapping Retries — the backoff-and-jitter formula used in the retry loops on this page.
• Preventing Race Conditions in Microservices — applying the same conditional-write discipline across service boundaries rather than within a single table.