Optimistic Locking #

In systems with many concurrent users, there’s a fundamental question to answer: what happens when two requests try to change the same data at almost the same time? The answer depends on the concurrency control strategy used. Optimistic locking is a strategy assuming conflicts rarely happen — so there’s no need to lock data when reading; just verify the data hasn’t changed when writing. If verification fails, reject the update and signal the client to retry. This approach is far lighter than pessimistic locking for read-heavy systems with low conflicts — but if implemented the wrong way, especially by combining a version check with SELECT FOR UPDATE and long business logic in one transaction, it turns into a more expensive pessimistic locking. This article covers how it works correctly, concrete implementations, safe retry patterns, and when optimistic locking truly isn’t the right choice.

The Problem It Solves: Lost Updates #

Before getting into the implementation, it’s important to understand the concrete problem optimistic locking solves. It’s called lost update — an update lost because two transactions read the same data, modify it, then both write back, and one write overwrites the other.

The Lost Update scenario without concurrency control:

sequenceDiagram
    participant A as Request A (buy 3)
    participant B as Request B (buy 5)
    participant DB as Database (products)

    Note over A, B: "Lost Update scenario without concurrency control (stock=10)"
    Note over A, DB: "T1"
    A->>DB: "SELECT stock FROM products"
    DB-->>A: "stock = 10"

    Note over B, DB: "T2"
    B->>DB: "SELECT stock FROM products"
    DB-->>B: "stock = 10"

    Note over A: "T3: new_stock = 10 - 3 = 7"
    Note over B: "T4: new_stock = 10 - 5 = 5"

    Note over A, DB: "T5"
    A->>DB: "UPDATE ... SET stock=7"

    Note over B, DB: "T6"
    B->>DB: "UPDATE ... SET stock=5 (overwrites A's update)"
    Note over DB: "Final result: stock = 5 (should be 2)"

Optimistic locking detects this condition and ensures one request fails (which will retry or be reported to the user) rather than letting corrupt data into the database.

How It Works: The Version Column as a Guardian #

The core mechanism of optimistic locking is a version column (or updated_at) acting as the “timestamp” of a row’s latest state. Every time a row changes, its version rises. When updating, the client includes the version it read — if the database version already differs, another update got in first, and the update is rejected.

The Database Schema #

-- Add a version column to the table needing concurrency control
ALTER TABLE products
ADD COLUMN version BIGINT UNSIGNED NOT NULL DEFAULT 1;

-- Or use updated_at as the version (alternative approach)
ALTER TABLE products
ADD COLUMN updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP;

-- No separate index is needed on the version column because
-- the WHERE always includes id (primary key):
-- WHERE id = ? AND version = ?
-- → id is already a primary index, version is an extra filter after the PK lookup

The Correct Optimistic Locking Flow #

The correct optimistic locking flow:

flowchart TD
    Step1["1. READ (no lock, no transaction)<br>SELECT stock, version FROM products WHERE id = 42<br>→ stock=10, version=7"] --> Step2["2. LOGIC (in the application layer, outside the DB transaction)<br>new_stock = 10 - 3 = 7"]
    Step2 --> Step3["3. WRITE (short, atomic, with a version check)<br>UPDATE products SET stock = 7, version = version + 1 WHERE id = 42 AND version = 7"]
    Step3 --> Step4{"4. CHECK rows_affected"}
    Step4 -->|"rows_affected = 1"| Success["Success (version is now 8)"]
    Step4 -->|"rows_affected = 0"| Conflict["Conflict! (another update got in first)<br>→ Retry from step 1 / return an error"]

Key: the database transaction is only for the short WRITE operation. Heavy business logic lives outside the transaction.

How this solves the lost update problem:

The same scenario with optimistic locking:

sequenceDiagram
    participant A as Request A (buy 3)
    participant B as Request B (buy 5)
    participant DB as Database (products)

    Note over A, B: "Scenario with optimistic locking (stock=10, version=7)"
    Note over A, DB: "T1"
    A->>DB: "SELECT stock, version"
    DB-->>A: "stock=10, version=7"

    Note over B, DB: "T2"
    B->>DB: "SELECT stock, version"
    DB-->>B: "stock=10, version=7"

    Note over A: "T3: new_stock = 10-3 = 7"
    Note over B: "T4: new_stock = 10-5 = 5"

    Note over A, DB: "T5"
    A->>DB: "UPDATE ... WHERE version=7"
    DB-->>A: "Success (rows_affected=1, version becomes 8)"

    Note over B, DB: "T6"
    B->>DB: "UPDATE ... WHERE version=7"
    DB-->>B: "Failed (rows_affected=0 because version is already 8!) -> CONFLICT"

The Fatal Mistake: FOR UPDATE Inside Optimistic Locking #

The most common mistake found in production is thinking optimistic locking has been implemented when actually a far more expensive pessimistic locking is being done, wrapped in long logic.

-- ANTI-PATTERN: this is NOT optimistic locking
BEGIN;
SELECT id, stock, version FROM products WHERE id = 42 FOR UPDATE;
-- ↑ The row is now locked. Nobody can read or write this row.

-- Do long business logic here (often happens in real practice):
-- - Validate against the inventory service (100ms)
-- - Check active promos (50ms)
-- - Calculate discounts (20ms)
-- - Log to an audit table (30ms)
-- Total: ~200ms with the row locked

UPDATE products
SET stock = stock - 3, version = version + 1
WHERE id = 42 AND version = 3;  -- the version check is useless here!
COMMIT;
-- Only now is the lock released

Why this is so problematic:

The impact of FOR UPDATE with heavy logic inside a transaction:
──────────────────────────────────────────────────────────────
  The products id=42 row is locked for ~200ms per request.
  If there are 100 concurrent requests for the same product:

  Request 1: lock held for 200ms
  Requests 2-100: queued waiting

  Total wait time for Request 100:
    99 × 200ms = ~20 seconds just to get the lock!

  Cascading impact:
    → The database connection pool runs out (all connections waiting for locks)
    → New requests: "connection timeout"
    → The application thread pool runs out
    → The entire system becomes unresponsive

  Ironically, the version check in WHERE is useless because
  FOR UPDATE already ensures no other update can slip through —
  pessimistic locking has taken over.
──────────────────────────────────────────────────────────────

SELECT ... FOR UPDATE inside a transaction containing slow operations (external API calls, large loops, complex validation) is a recipe for deadlocks and system outages. If you see this pattern in a codebase, it’s a high priority to fix.

The Correct Implementation in Go #

Here’s a complete optimistic locking implementation for a product purchase case, with safe retry logic using exponential backoff:

// ErrConflict is returned when the version check fails
var ErrConflict = errors.New("data has been modified by another request")
var ErrMaxRetry = errors.New("max retry attempts reached")

type Product struct {
    ID      int64
    Name    string
    Stock   int
    Version int64
}

// BuyProduct reduces product stock with optimistic locking
func BuyProduct(ctx context.Context, db *sql.DB, productID int64, qty int) error {
    const maxRetries = 3

    for attempt := 0; attempt < maxRetries; attempt++ {
        err := attemptBuy(ctx, db, productID, qty)
        if err == nil {
            return nil  // success
        }
        if !errors.Is(err, ErrConflict) {
            return err  // another error, no need to retry
        }

        // Version conflict — wait a moment then retry
        if attempt < maxRetries-1 {
            backoff := time.Duration(1<<uint(attempt)) * 50 * time.Millisecond
            // Add jitter to avoid a thundering herd
            jitter := time.Duration(rand.Int63n(int64(backoff / 2)))
            select {
            case <-time.After(backoff + jitter):
            case <-ctx.Done():
                return ctx.Err()
            }
        }
    }

    return ErrMaxRetry
}

func attemptBuy(ctx context.Context, db *sql.DB, productID int64, qty int) error {
    // STEP 1: Read without a lock, without a transaction
    var product Product
    err := db.QueryRowContext(ctx,
        "SELECT id, name, stock, version FROM products WHERE id = ?",
        productID,
    ).Scan(&product.ID, &product.Name, &product.Stock, &product.Version)
    if err != nil {
        return fmt.Errorf("read product: %w", err)
    }

    // STEP 2: Business logic in the application layer (outside the DB transaction)
    if product.Stock < qty {
        return fmt.Errorf("insufficient stock: available %d, requested %d",
            product.Stock, qty)
    }
    newStock := product.Stock - qty

    // Simulate: additional validation that may take time
    // (outside the database transaction — KEY POINT)
    if err := validatePurchasePolicy(ctx, product, qty); err != nil {
        return err
    }

    // STEP 3: Write with a version check — short and atomic
    result, err := db.ExecContext(ctx, `
        UPDATE products
        SET stock   = ?,
            version = version + 1
        WHERE id      = ?
          AND version = ?
          AND stock   >= ?
    `, newStock, productID, product.Version, qty)
    if err != nil {
        return fmt.Errorf("update product: %w", err)
    }

    rowsAffected, err := result.RowsAffected()
    if err != nil {
        return fmt.Errorf("check rows affected: %w", err)
    }

    if rowsAffected == 0 {
        // Version mismatch or stock already changed — conflict
        return ErrConflict
    }

    return nil
}

// validatePurchasePolicy — an example of logic deliberately kept outside the transaction
func validatePurchasePolicy(ctx context.Context, p Product, qty int) error {
    // For example: check whether the user may buy more than a daily limit
    // This can take 50-200ms — it MUST NOT be inside the DB transaction
    return nil
}

Note the extra AND stock >= ? condition in the UPDATE — this is an additional guard so no race condition occurs between the stock check in step 2 and the update in step 3, even when the version is the same.

Retry with Exponential Backoff #

The retry pattern with exponential backoff + jitter:
──────────────────────────────────────────────────────────────
  Attempt 1: failed due to a conflict
  Wait: 50ms + random(0-25ms)  → e.g. 63ms

  Attempt 2: failed due to a conflict
  Wait: 100ms + random(0-50ms) → e.g. 137ms

  Attempt 3: success (or return ErrMaxRetry)

  Why jitter matters:
    Without jitter: all retries from many concurrent requests
    will try at the same time → thundering herd
    → conflicts keep repeating

    With jitter: retries spread across different times
    → the conflict probability drops drastically
──────────────────────────────────────────────────────────────

Version Column vs Updated_at: Choosing the Right One #

There are two common approaches for the “version” column in optimistic locking, each with different trade-offs.

Approach 1: Integer Version Column #

-- Schema
version BIGINT UNSIGNED NOT NULL DEFAULT 1

-- Update
SET version = version + 1
WHERE id = ? AND version = ?

-- Advantages:
--   ✓ Deterministic — easy to debug, easy to assert in tests
--   ✓ Doesn't depend on timestamp precision
--   ✓ Always increments, never "collides" due to clock skew

-- Disadvantages:
--   ✗ Needs an extra column in the schema
--   ✗ Must be explicitly updated in every UPDATE query

Approach 2: Updated_at Timestamp #

-- Schema (MySQL)
updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
           ON UPDATE CURRENT_TIMESTAMP(6)  -- microsecond precision

-- Update
SET status = ?
WHERE id = ? AND updated_at = ?

-- Advantages:
--   ✓ Independently useful information (when the data was last changed)
--   ✓ Automatically updated without mentioning it explicitly in SET

-- Disadvantages:
--   ✗ Depends on clock precision — use microseconds (6), not seconds
--   ✗ In distributed systems, clock skew between nodes can cause problems
--   ✗ Two very fast consecutive updates can get the same timestamp

-- ANTI-PATTERN: updated_at with second-level precision only
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
           ON UPDATE CURRENT_TIMESTAMP
-- → Two updates in the same second have identical timestamps
-- → The version check doesn't detect conflicts!

-- CORRECT: use microsecond precision
updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
           ON UPDATE CURRENT_TIMESTAMP(6)
-- → 1 microsecond resolution, far safer for version checks

Recommendation: use an integer version for new systems. It’s more deterministic, has no clock dependency, and is easier to test.

Real Case: Updating Stock with Multiple Products #

When one operation needs to update several rows at once (for example, an order containing several products), optimistic locking must be handled carefully to avoid partial updates.

type OrderItem struct {
    ProductID int64
    Qty       int
}

// ReserveStock reduces stock for all items in one order
func ReserveStock(ctx context.Context, db *sql.DB, items []OrderItem) error {
    const maxRetries = 3

    for attempt := 0; attempt < maxRetries; attempt++ {
        err := attemptReserve(ctx, db, items)
        if err == nil {
            return nil
        }
        if !errors.Is(err, ErrConflict) {
            return err
        }
        // If there's a conflict on one product, retry the whole operation
        backoff := time.Duration(1<<uint(attempt)) * 100 * time.Millisecond
        time.Sleep(backoff)
    }
    return ErrMaxRetry
}

func attemptReserve(ctx context.Context, db *sql.DB, items []OrderItem) error {
    // Step 1: Read all needed products — without locks
    type ProductSnapshot struct {
        ID      int64
        Stock   int
        Version int64
    }

    productIDs := make([]int64, len(items))
    for i, item := range items {
        productIDs[i] = item.ProductID
    }

    // Read all products in one query
    snapshots := make(map[int64]ProductSnapshot)
    rows, err := db.QueryContext(ctx,
        "SELECT id, stock, version FROM products WHERE id IN (?)",
        productIDs...,
    )
    // ... scan rows into the snapshots map

    // Step 2: Validate stock for all products (outside the transaction)
    for _, item := range items {
        snap := snapshots[item.ProductID]
        if snap.Stock < item.Qty {
            return fmt.Errorf("insufficient stock for product %d", item.ProductID)
        }
    }

    // Step 3: Update all products in one short transaction
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()

    for _, item := range items {
        snap := snapshots[item.ProductID]
        newStock := snap.Stock - item.Qty

        result, err := tx.ExecContext(ctx, `
            UPDATE products
            SET stock = ?, version = version + 1
            WHERE id = ? AND version = ?
        `, newStock, item.ProductID, snap.Version)
        if err != nil {
            return err
        }

        affected, _ := result.RowsAffected()
        if affected == 0 {
            // A conflict on one product — roll back everything
            return ErrConflict
        }
    }

    return tx.Commit()
}

When updating several rows in one transaction with optimistic locking, always order the updates by primary key (e.g. ORDER BY product_id ASC) to avoid deadlocks. Two transactions updating the same rows in different orders can wait on each other.

Optimistic vs Pessimistic Locking: Choosing Between Them #

A comprehensive comparison:
──────────────────────────────────────────────────────────────────────
  Aspect                  │ Optimistic Locking  │ Pessimistic Locking
──────────────────────────────────────────────────────────────────────
  How it works            │ Free reads,         │ Lock on read,
                          │ check on write      │ release after write
  Database locks          │ None                │ Held during the transaction
  Blocks other readers    │ No                  │ Yes (depending on the level)
  Best for                │ Rare conflicts      │ Frequent conflicts
  Read-heavy throughput   │ Very high           │ Limited by locks
  Conflict handling       │ Retry in the app    │ Automatically waits
  Deadlock risk           │ Low                 │ Higher
  Application complexity  │ Higher              │ Lower
  Example use cases       │ User profile edits, │ Bank balance transfers,
                          │ metadata updates    │ seat ticket bookings
──────────────────────────────────────────────────────────────────────

Choosing guidance:

  Use Optimistic Locking if:
    ✓ Conflict probability is low (< 10% of requests will conflict)
    ✓ Reads are far more frequent than writes
    ✓ Retries can happen without disturbing the user experience
    ✓ Business operations don't involve non-idempotent external sources
    ✓ Data can be "stale" briefly without serious consequences

  Use Pessimistic Locking (FOR UPDATE) if:
    ✓ Conflicts happen very often
    ✓ Data must be strongly consistent
    ✓ Retries aren't possible (e.g. real-time payments)
    ✓ Transactions are short and predictable (no external I/O)
    ✓ Operation order must be deterministic (FIFO)

Anti-Patterns to Avoid #

// ✗ Anti-pattern 1: FOR UPDATE + heavy logic in one transaction
tx.Begin()
row := tx.QueryRow("SELECT ... FOR UPDATE WHERE id = ?", id)
callExternalAPI()           // 200ms — the row is still locked!
sendEmailNotification()     // 100ms — the row is still locked!
tx.Exec("UPDATE ...")
tx.Commit()
// ✓ Solution: move all logic outside the transaction, the transaction is only for writes

// ✗ Anti-pattern 2: retry without backoff — thundering herd
for {
    err := attemptUpdate(ctx)
    if err == nil { break }
    if !errors.Is(err, ErrConflict) { return err }
    // Immediate retry without a pause — all goroutines retry together
    // → contention rises, conflicts become more frequent
}
// ✓ Solution: exponential backoff + jitter like the implementation above

// ✗ Anti-pattern 3: updates without a version check (no optimistic locking at all)
db.Exec("UPDATE products SET stock = ? WHERE id = ?", newStock, id)
// → Lost updates can happen
// ✓ Solution: always include AND version = ? and check rows_affected

// ✗ Anti-pattern 4: version check but ignoring rows_affected
result, _ := db.Exec("UPDATE products SET stock = ?, version = version + 1 WHERE id = ? AND version = ?",
    newStock, id, version)
// No rows_affected check — no idea whether the update succeeded or conflicted
// ✓ Solution: always read RowsAffected() and handle 0 as a conflict

// ✗ Anti-pattern 5: unlimited retries without a max attempt
attempts := 0
for {
    err := attemptUpdate()
    if err == nil { break }
    attempts++
    // No limit — can loop forever if conflicts keep happening
}
// ✓ Solution: set a reasonable max retry (3-5 times), return an error when exhausted

Optimistic Locking Implementation Checklist #

SCHEMA:
  □ Is there a version column (INT) or updated_at (DATETIME(6))?
  □ Does the version column have the right DEFAULT (1 for INT, CURRENT_TIMESTAMP for datetime)?
  □ Is the version column updated in ALL relevant UPDATE queries?

QUERIES:
  □ Is READ done without FOR UPDATE, outside transactions?
  □ Is slow business logic outside the database transaction?
  □ Does the UPDATE include AND version = ? in the WHERE clause?
  □ Does the UPDATE increment the version (SET version = version + 1)?
  □ Is rows_affected always checked after the UPDATE?
  □ Is rows_affected = 0 handled as ErrConflict, not success?

RETRY LOGIC:
  □ Is there a clear max retry (3-5 times)?
  □ Is there exponential backoff between retries?
  □ Is there jitter to avoid thundering herds?
  □ Is context cancellation respected (ctx.Done()) while waiting for retries?
  □ Is ErrMaxRetry returned to the caller when all retries fail?

IDEMPOTENCY:
  □ Is the business logic safe to re-run (retries don't cause duplication)?
  □ Do side effects (emails, notifications, payment charges) happen only after a successful update?

Summary #

• Optimistic locking works with a version column, not database locks — read data freely without locks, do business logic in the app, then verify the version when writing. If the version changed, reject and retry.
• AND version = ? in WHERE is the core of the mechanism — rows_affected = 0 means a conflict was detected. Always check this value — don’t ignore the result.
• Slow business logic must live outside the database transaction — the transaction may only contain short write operations. Put validation, API calls, and complex calculations before BEGIN.
• SELECT FOR UPDATE + heavy logic = a far more expensive pessimistic locking — this is the most common mistake. The row stays locked for the entire logic duration, causing long queues in concurrent systems.
• Retry with exponential backoff + jitter is a mandatory part — without jitter, all concurrent retries try at the same time and worsen contention. A max retry limit is also mandatory.
• Use an integer version, not a plain updated_at TIMESTAMP — second-precision timestamps can’t detect conflicts happening within milliseconds. If you must use a timestamp, use DATETIME(6) with microsecond precision.
• Order multi-row updates by primary key — to avoid deadlocks when two transactions update the same row set in different orders.
• Optimistic locking doesn’t fit frequent conflicts — if more than 10-20% of requests end in conflicts and retries, consider pessimistic locking or a queue-based architecture for those operations.

← Previous: Pagination   Next: N+1 Effect →