Optimistic vs Pessimistic Locking

When two users try to update the same data at the same time, we need a strategy. There are two main approaches: pessimistic locking (lock first, then work) and optimistic locking (work first, check for conflicts at the end).

Pessimistic Locking

With pessimistic locking, we assume conflicts are likely and lock the data before we even start working on it. “I’m going to grab this row and hold onto it until I’m done.”

-- Pessimistic: lock the row before reading
BEGIN;

SELECT * FROM products WHERE id = 42 FOR UPDATE;
-- Row is now locked — nobody else can modify it

-- Check stock, calculate price, etc.
-- ... application logic ...

UPDATE products SET stock = stock - 1 WHERE id = 42;
COMMIT;
-- Lock released

If another transaction tries to SELECT ... FOR UPDATE on the same row, it will wait (block) until our transaction finishes.

Optimistic Locking

With optimistic locking, we assume conflicts are rare. We read the data, do our work, and only check for conflicts when we try to save. No locks are held during the work.

The most common pattern is using a version column:

-- Step 1: Read the row (no lock)
SELECT id, name, stock, version FROM products WHERE id = 42;
-- Returns: id=42, name="Widget", stock=10, version=5

-- Step 2: Do application processing (no lock held)
-- ... calculate new values ...

-- Step 3: Update only if version hasn't changed
UPDATE products
SET stock = 9, version = version + 1
WHERE id = 42 AND version = 5;
-- If rows affected = 1 → success
-- If rows affected = 0 → someone else updated it, retry!

If another transaction modified the row (bumping the version to 6), our WHERE clause won’t match, the update affects 0 rows, and we know there was a conflict.

When to Use Which

Optimistic locking works best when:

• Conflicts are rare (most users edit different data)
• We want high throughput with many concurrent readers
• The cost of retrying on conflict is acceptable
• Examples: editing a user profile, updating preferences, CMS content

Pessimistic locking works best when:

• Conflicts are frequent (many users competing for the same resource)
• The cost of a failed operation is high (can’t just retry easily)
• We need to guarantee the operation succeeds on the first try
• Examples: booking a seat, purchasing limited inventory, bank transfers

Implementation: Optimistic with Version Column

-- Table setup
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    stock INT NOT NULL DEFAULT 0,
    version INT NOT NULL DEFAULT 1  -- version column
);

-- Application pseudocode:
-- 1. Read
-- product = SELECT * FROM products WHERE id = 42;
-- currentVersion = product.version;

-- 2. Process (no database locks)
-- newStock = product.stock - orderQuantity;

-- 3. Conditional update
-- result = UPDATE products
--          SET stock = newStock, version = version + 1
--          WHERE id = 42 AND version = currentVersion;

-- 4. Check result
-- if (result.rowsAffected === 0) {
--     // Conflict! Someone else updated the row. Retry from step 1.
-- }

Some ORMs have built-in optimistic locking. For example, Hibernate uses @Version annotation, Sequelize has optimisticLocking option, and Rails has lock_version column.

Implementation: Pessimistic with FOR UPDATE

-- Booking a concert seat (can't afford a conflict)
BEGIN;

-- Lock the seat row
SELECT * FROM seats WHERE event_id = 1 AND seat_number = 'A15' FOR UPDATE;

-- Check if it's available
-- if (seat.status !== 'available') throw new Error('Already booked');

-- Book it
UPDATE seats SET status = 'booked', booked_by = 42
WHERE event_id = 1 AND seat_number = 'A15';

COMMIT;

Alternative: updated_at Instead of Version

Instead of a version number, we can use a timestamp:

-- Read the row
SELECT * FROM products WHERE id = 42;
-- Returns: updated_at = '2024-03-15 10:30:00'

-- Update only if timestamp matches
UPDATE products
SET stock = 9, updated_at = NOW()
WHERE id = 42 AND updated_at = '2024-03-15 10:30:00';

The version number approach is generally safer because timestamps can have precision issues (two updates in the same millisecond).

Quick Comparison

In simple language, pessimistic locking is like reserving a table at a restaurant before we go — guaranteed spot but others can’t use it while we’re on the way. Optimistic locking is like showing up and hoping there’s a table — usually works, but sometimes we have to try again. Pick the strategy that matches how often our users compete for the same data.