Skip to content

Optimistic Locking

In one line

Optimistic locking: read without locking, check version before update — fail if someone else changed the row.

In simple words

Optimistic locking assumes concurrent conflicts are rare. Instead of locking a row during read, it checks at update time that no one else modified the row in between.

Implementation: add a version column to each row. When reading, note the version. When updating, include a WHERE clause checking the version:

UPDATE products SET price = 100, version = version + 1 WHERE id = 1 AND version = 5

If 0 rows are updated, someone else changed the row first — retry or fail.

Benefits:

  1. No locks held during user think time — better concurrency
  2. No deadlock risk — no locks to deadlock on
  3. Good for read-heavy workloads — conflicts are rare

Best when conflicts are infrequent. If conflicts are common, pessimistic locking is better.

Example

sql
-- Optimistic locking with version column
-- Table: products (id, name, price, version)

-- Step 1: Read product (note version)
SELECT id, name, price, version FROM products WHERE id = 1;
-- Returns: (1, 'Widget', 50.00, 5)

-- ... user edits the price ...

-- Step 2: Update with version check
UPDATE products
SET price = 100.00, version = version + 1
WHERE id = 1 AND version = 5;

-- If 1 row updated: success! No one else changed it.
-- If 0 rows updated: conflict! Someone else updated it first.
-- Application must re-read and retry.

-- Alternative: using updated_at timestamp
UPDATE products SET price = 100.00, updated_at = NOW()
WHERE id = 1 AND updated_at = '2024-01-15 10:30:00';

Read the version, then update only if version hasn't changed. If 0 rows updated, someone else modified it — retry. No locks held during user think time.

Common confusions

  • Confused with: pessimistic locking

    The difference: Optimistic locking doesn't hold locks — it checks for conflicts at update time using versions. Pessimistic locking holds locks during the entire transaction. Optimistic is better when conflicts are rare; pessimistic is better when conflicts are common.

Related terms

Related Resources

Related Lessons

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors

← Back to glossary