Optimistic Locking: "Hope for the Best

Detecting Conflicts at Commit Time

Optimistic Locking takes the opposite philosophy: assume conflicts are rare, let everyone read and write freely, and only check for conflicts at the very end when committing. If a conflict is detected, the transaction is rejected and the application retries.

This is implemented at the application level using a version number (or timestamp) column on the table. The database itself does not need to be configured differently.

How It Works

Schema: in practice this just means adding an integer version column (defaulting to 1) to the table.

Read-Modify-Write cycle:

-- Step 1: Read the row, capture the version
SELECT id, price, version FROM products WHERE id = 5;
-- Returns: id=5, price=10.00, version=1

-- (Application logic runs: calculate new price = 20.00)

-- Step 2: Update, but only if version hasn't changed
UPDATE products
SET price = 20.00,
    version = version + 1      -- Increment version
WHERE id = 5
  AND version = 1;             -- Guard: only update if nobody else changed it

-- Step 3: Check rows affected
-- 1 row affected → success, no conflict
-- 0 rows affected → conflict! Someone else updated the row. Retry.

If another transaction updated the row between Step 1 and Step 2, its version is now 2. The WHERE version = 1 condition fails, 0 rows are updated, and the application knows to retry.

Application-Level Retry Logic

MAX_RETRIES = 3

for attempt in range(MAX_RETRIES):
    product = db.query("SELECT id, price, version FROM products WHERE id = %s", [product_id])
    new_price = calculate_new_price(product.price)

    rows_updated = db.execute("""
        UPDATE products SET price = %s, version = version + 1
        WHERE id = %s AND version = %s
    """, [new_price, product.id, product.version])

    if rows_updated == 1:
        db.commit()
        break  # Success
    else:
        db.rollback()
        if attempt == MAX_RETRIES - 1:
            raise ConflictError("Too many concurrent updates. Please try again.")
        time.sleep(0.1 * (2 ** attempt))  # Exponential backoff

Optimistic vs. Pessimistic: When to Use Each

Senior insight: Optimistic locking works well for user-facing workflows (editing a document, updating a profile) where conflicts are rare and retrying is acceptable. It breaks down under high contention — if 100 threads compete for the same row, 99 will fail on every attempt, creating a retry storm that makes the system slower than pessimistic locking would have been. Monitor your retry rates; a spike is a signal that optimistic locking is the wrong choice for that workload.