Optimistic Locking in Databases

Overview

I use optimistic locking to stop lost updates when several clients work at once. It checks whether the row changed since I last read it. I keep an integer version on each row and include that version in the WHERE clause. If another transaction changed it, the update returns zero rows and I either retry with fresh data or return a 409 conflict. That beats holding database locks while the user thinks.

Below you’ll find how I implement optimistic locking with integer versioning in PostgreSQL-compatible SQL, Node.js, Java/JPA and beyond. Related recipes: Database Migrations Safely, Manage Database Migrations Safely, and Create and Use Database Views and Materialized Views.

When to Use

I reach for optimistic locking when:

• Several users, background jobs or microservices often try to update the same row at the same time. See Database Transactions for ACID patterns.
• I want to avoid pessimistic locks that hurt throughput and can deadlock.
• My app follows a read-modify-write pattern and there’s a gap between the read and the write.
• I need conflict detection in REST APIs, offline-first apps, or distributed systems.

I don’t use it when:

• Contention is so high that retries become expensive or impractical. For those cases, I prefer pessimistic locks or atomic operations such as SELECT FOR UPDATE.
• I can redesign the flow to skip the read-modify-write pattern entirely — append events or use CRDTs, for example.
• The same row is being hit several times a second from different sources. In my experience, a pessimistic lock or a queue is usually less painful.
• My database already supports serializable isolation (e.g., PostgreSQL SERIALIZABLE) and the workload tolerates its overhead.

Solution

Python

import psycopg2
from psycopg2.extras import RealDictCursor

def update_user_email(conn, user_id: int, new_email: str, expected_version: int):
    with conn.cursor(cursor_factory=RealDictCursor) as cur:
        cur.execute("""
            UPDATE users
            SET email = %s, version = version + 1
            WHERE id = %s AND version = %s
            RETURNING id, version;
        """, (new_email, user_id, expected_version))

        updated = cur.fetchone()
        if not updated:
            raise ValueError(
                f"Conflict: user {user_id} was modified by another transaction. "
                "Please refresh and retry."
            )
        conn.commit()
        return updated

# Usage
try:
    result = update_user_email(conn, user_id=42, new_email="new@example.com", expected_version=3)
    print(f"Updated to version {result['version']}")
except ValueError as e:
    print(e)  # Trigger retry logic in the API layer

JavaScript

const { Pool } = require('pg');
const pool = new Pool({ /* config */ });

async function updateProductPrice(productId, newPrice, expectedVersion) {
  const result = await pool.query(
    `UPDATE products
     SET price = $1, version = version + 1, updated_at = NOW()
     WHERE id = $2 AND version = $3
     RETURNING id, version;`,
    [newPrice, productId, expectedVersion]
  );

  if (result.rowCount === 0) {
    const current = await pool.query('SELECT version FROM products WHERE id = $1', [productId]);
    throw new Error(
      `Version conflict: expected ${expectedVersion}, found ${current.rows[0]?.version}. Please retry.`
    );
  }

  return result.rows[0];
}

// Express route with retry
app.put('/products/:id', async (req, res) => {
  try {
    const product = await updateProductPrice(req.params.id, req.body.price, req.body.version);
    res.json(product);
  } catch (err) {
    res.status(409).json({ error: err.message });
  }
});

Java

// JPA / Hibernate with @Version
import jakarta.persistence.*;

@Entity
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private BigDecimal price;

    @Version
    private Integer version;  // Auto-incremented by Hibernate on every flush

    // Getters and setters...
}

// Service layer
@Service
@Transactional
public class ProductService {
    @Autowired
    private ProductRepository repo;

    public Product updatePrice(Long id, BigDecimal newPrice) {
        Product product = repo.findById(id)
            .orElseThrow(() -> new EntityNotFoundException("Product not found"));
        product.setPrice(newPrice);
        return repo.save(product);  // Version checked automatically on flush
    }
}

// Catching the optimistic lock exception
@ExceptionHandler(OptimisticLockingFailureException.class)
public ResponseEntity<Map<String, String>> handleConflict(OptimisticLockingFailureException ex) {
    return ResponseEntity.status(HttpStatus.CONFLICT)
        .body(Map.of("error", "Resource modified by another user. Please refresh and retry."));
}

Explanation

I think of optimistic locking as a bet that conflicts will be rare. The database doesn’t lock the row while I read it. The actual update is conditional:

UPDATE table SET ... WHERE id = ? AND version = ?

If rowsAffected == 0, the version changed between the read and the write. I then handle the conflict: retry with fresh data, return HTTP 409, or merge the changes.

Trade-offs:

• Optimistic: reads stay lock-free and the system scales, but I need to handle conflicts and retry.
• Pessimistic: SELECT FOR UPDATE locks the row right away; the logic is simpler, but it serializes access and can deadlock.

For more concurrency patterns, see Concurrent Data Structures.

Variants

Technology	Approach	Notes
Integer version	version column incremented on every update	Most common; works across all relational databases
Timestamp	updated_at column compared at write time	Prone to clock skew issues; use database timestamps, not app clocks
Checksum / hash	Hash of row contents stored and compared	Detects any change, even if you bypass the version
JPA @Version	Automatic integer version	Hibernate handles increment and conflict detection transparently
DynamoDB	Conditional writes with Expected	No native versioning; use attribute_exists or value comparisons
MongoDB	findAndModify with query criteria	Include version in filter; retry if document was modified

What Works

1. I always return the current version to the client after every read, so it can send it back on update.
2. I implement exponential backoff retry (1–3 attempts) for transient conflicts in automated processes.
3. I use integer version over timestamps; in production, I’ve seen clocks drift and cause false positives across nodes.
4. I keep transactions short; the gap between read and write is the vulnerability window.
5. I log version conflicts at INFO level to monitor contention hotspots without alarming on every retry.

Common Mistakes

1. Not exposing version to API consumers — clients can’t send it back if they never received it. I’ve debugged this in an API where the frontend silently lost every second update.
2. Infinite retry loops — I always cap retries and surface persistent conflicts to the user.
3. Updating the version in application code — I let the database or ORM increment it atomically.
4. Using pessimistic locking for everything — it kills throughput; I reserve FOR UPDATE for true inventory or banking scenarios. See [Locks and Mutexes] (/recipes/locks-and-mutexes/) for lock patterns.
5. Ignoring the conflict in UI — users need clear feedback that their data is stale and must be refreshed.

Implementation Examples

Retry logic with exponential backoff

import random
import time
from functools import wraps

def retry_on_conflict(max_retries=3, base_delay=0.05):
    def decorator(func):
        @wraps(func)
        def wrapper(*args, **kwargs):
            for attempt in range(max_retries):
                try:
                    return func(*args, **kwargs)
                except ValueError as e:
                    if "Conflict" not in str(e):
                        raise
                    if attempt == max_retries - 1:
                        raise
                    delay = base_delay * (2 ** attempt) + random.uniform(0, 0.05)
                    time.sleep(delay)
            return None
        return wrapper
    return decorator

@retry_on_conflict(max_retries=3)
def update_user_with_retry(conn, user_id, new_email, expected_version):
    return update_user_email(conn, user_id, new_email, expected_version)

async function withRetry(fn, maxRetries = 3, baseDelay = 50) {
  for (let attempt = 0; attempt < maxRetries; attempt++) {
    try {
      return await fn();
    } catch (err) {
      if (!err.message.includes('Version conflict') || attempt === maxRetries - 1) {
        throw err;
      }
      const delay = baseDelay * (2 ** attempt) + Math.random() * 50;
      await new Promise(r => setTimeout(r, delay));
    }
  }
}

// Usage with automatic version refresh
async function updateProductWithRetry(productId, updateFn) {
  let product = await getProduct(productId);
  for (let attempt = 0; attempt < 3; attempt++) {
    const updated = updateFn(product);
    try {
      return await pool.query(
        'UPDATE products SET price = $1, version = version + 1 WHERE id = $2 AND version = $3 RETURNING *',
        [updated.price, productId, product.version]
      );
    } catch (err) {
      if (attempt === 2) throw err;
      product = await getProduct(productId); // Refresh and retry
    }
  }
}

MongoDB optimistic locking with findAndModify

const { MongoClient } = require('mongodb');
const client = new MongoClient(process.env.MONGO_URI);

async function updateProductOptimistic(db, productId, newPrice, expectedVersion) {
  const result = await db.collection('products').findOneAndUpdate(
    { _id: productId, version: expectedVersion },
    {
      $set: { price: newPrice },
      $inc: { version: 1 },
    },
    { returnDocument: 'after' }
  );

  if (!result) {
    const current = await db.collection('products').findOne({ _id: productId });
    throw new Error(
      `Version conflict: expected ${expectedVersion}, found ${current?.version}. Please retry.`
    );
  }

  return result;
}

// Mongoose plugin for automatic versioning
const optimisticLockPlugin = (schema) => {
  schema.add({ version: { type: Number, default: 0 } });

  schema.pre('findOneAndUpdate', function () {
    const filter = this.getFilter();
    const update = this.getUpdate();

    if (filter.version !== undefined && update.$inc) {
      update.$inc.version = (update.$inc.version || 0) + 1;
    } else if (filter.version !== undefined) {
      this.setUpdate({ ...update, $inc: { version: 1 } });
    }
  });
};

productSchema.plugin(optimisticLockPlugin);

DynamoDB conditional writes

import boto3

dynamodb = boto3.resource('dynamodb')
table = dynamodb.Table('products')

def update_price_optimistic(product_id, new_price, expected_version):
    response = table.update_item(
        Key={'product_id': product_id},
        UpdateExpression='SET price = :p, version = :new_v',
        ConditionExpression='version = :expected',
        ExpressionAttributeValues={
            ':p': new_price,
            ':new_v': expected_version + 1,
            ':expected': expected_version,
        },
        ReturnValues='ALL_NEW'
    )
    return response

# Handle conditional check failure
from botocore.exceptions import ClientError

try:
    update_price_optimistic('prod-42', 99.99, 3)
except ClientError as e:
    if e.response['Error']['Code'] == 'ConditionalCheckFailedException':
        print("Version conflict: another process modified this item")

ETag and If-Match for HTTP APIs

// Express middleware for ETag-based optimistic locking
const crypto = require('crypto');

function generateETag(resource) {
  const hash = crypto.createHash('md5');
  hash.update(JSON.stringify(resource));
  return `"${hash.digest('hex')}"`;
}

app.put('/products/:id', async (req, res) => {
  const ifMatch = req.headers['if-match'];
  if (!ifMatch) {
    return res.status(428).json({ error: 'If-Match header required' });
  }

  const product = await getProduct(req.params.id);
  const currentETag = generateETag(product);

  if (ifMatch !== currentETag) {
    return res.status(412).json({
      error: 'Precondition failed: resource has been modified',
      currentETag,
    });
  }

  const updated = await updateProduct(req.params.id, req.body);
  res.set('ETag', generateETag(updated));
  res.json(updated);
});

Batch optimistic locking

def batch_update_with_versions(conn, updates):
    """Update multiple rows with optimistic locking in a single transaction."""
    results = []
    with conn.cursor() as cur:
        for item in updates:
            cur.execute("""
                UPDATE products
                SET price = %s, version = version + 1
                WHERE id = %s AND version = %s
                RETURNING id, version;
            """, (item['new_price'], item['id'], item['expected_version']))

            updated = cur.fetchone()
            if not updated:
                conn.rollback()
                raise ValueError(
                    f"Conflict on product {item['id']}: "
                    f"expected version {item['expected_version']}"
                )
            results.append(updated)
    conn.commit()
    return results

# Usage
try:
    results = batch_update_with_versions(conn, [
        {'id': 1, 'new_price': 19.99, 'expected_version': 5},
        {'id': 2, 'new_price': 29.99, 'expected_version': 3},
        {'id': 3, 'new_price': 39.99, 'expected_version': 7},
    ])
except ValueError as e:
    print(f"Batch failed: {e}")
    # All updates rolled back, client must refresh and retry

Conflict resolution strategies

If the fields don’t overlap, both changes can survive. When the client updates the email and the server updates the name, neither value is lost. Read the current version, merge the changes, and write back with a fresh version check:

def merge_update(conn, user_id, client_changes, expected_version):
    with conn.cursor() as cur:
        cur.execute("SELECT name, email, version FROM users WHERE id = %s", (user_id,))
        current = cur.fetchone()
        if not current:
            raise ValueError("User not found")

        merged = {
            'name': client_changes.get('name', current['name']),
            'email': client_changes.get('email', current['email']),
        }

        cur.execute("""
            UPDATE users
            SET name = %s, email = %s, version = version + 1
            WHERE id = %s AND version = %s
            RETURNING id, version;
        """, (merged['name'], merged['email'], user_id, current['version']))

        updated = cur.fetchone()
        if not updated:
            raise ValueError("Conflict: the record changed during the merge. Please retry.")
        conn.commit()
        return updated

When fields overlap, the right call depends on the domain: show a diff to the user, pick a winner, or ask for confirmation.

Production Notes

1. Index the version column. The WHERE id = ? AND version = ? clause needs an index on both columns.

CREATE INDEX idx_products_id_version ON products (id, version);

2. Keep the read-modify-write gap short. The longer the gap, the more likely conflicts occur. I skip external API calls and heavy computation between reading and writing.

3. Use RETURNING to avoid a second query. I get the updated version in the same statement:

UPDATE products SET price = $1, version = version + 1
WHERE id = $2 AND version = $3
RETURNING id, version;

4. Monitor conflict rates with pg_stat_database. I track deadlocks and conflicts at the database level:

SELECT datname, deadlocks, conflicts, temp_files
FROM pg_stat_database
WHERE datname = current_database();

5. Consider SERIALIZABLE isolation instead of manual versioning. PostgreSQL’s SERIALIZABLE handles conflicts automatically using SSI (Serializable Snapshot Isolation). I’ve found it simpler than managing versions by hand for complex transactions.

Key Takeaways

• Optimistic locking avoids long-held database locks by making every update conditional on a version number. That’s why I prefer it for most read-heavy workflows.
• I use an integer version column rather than timestamps; I increment it atomically in the database or ORM.
• I always return the current version on reads, and return a clear 409 Conflict (or equivalent) when the version doesn’t match.
• I keep the read-modify-write window short and cap retries to prevent thundering herds.
• When contention is high, I fall back to pessimistic locks, SELECT FOR UPDATE or serializable isolation.

Further Reading

• PostgreSQL concurrency control
• MySQL locking reads
• Jakarta Persistence @Version
• Database Transactions for ACID patterns
• Retry Backoff for conflict retry logic
• Locks and Mutexes for pessimistic locking patterns