Optimistic vs. Pessimistic Locking: Retry or Wait?

Part 2 of a 3-part series on concurrency in payment systems.

In Part 1, two $50 payments hit MegaStore's $100 balance at the same moment and the final balance came out as $150. Both requests read the balance, did some logic in application code and wrote the result back, and one write silently erased the other. That's a lost update.

We also saw that SET balance = balance + 50 dodges the problem, but only when you don't need to read the balance first. Real payment logic almost always does (fraud checks, limits, insufficient funds), so we need a way to make read → decide → write safe.

The interviewer's follow-up:

"Walk me through it. How do you serialize these updates?"

There are two classic answers. They solve the same problem from opposite philosophies:

Let's take them one at a time.

Pessimistic locking: lock first, ask questions later

The idea is simple: when you read a row you intend to change, lock it. Anyone else who wants it waits in line until you commit.

In PostgreSQL or MySQL, that's SELECT ... FOR UPDATE:

BEGIN;
 
SELECT balance FROM accounts
WHERE id = 'MegaStore'
FOR UPDATE;              -- row is now locked; other FOR UPDATE / UPDATE calls block
 
-- application logic: limits, fraud checks, compute new balance...
 
UPDATE accounts SET balance = 150 WHERE id = 'MegaStore';
 
COMMIT;                  -- lock released, next transaction in line proceeds

(SQL Server spells it SELECT ... WITH (UPDLOCK, ROWLOCK).)

Replay Alice and Bob: Alice's transaction locks the row and reads $100. Bob's SELECT ... FOR UPDATE blocks. Alice writes $150 and commits. Only then does Bob's read return, and it returns $150, not $100. Bob writes $200. Correct.

It's simple and linear. There's no retry loop and no version column, and the database enforces exclusivity for you.

So why not always do this?

The interviewer's next question:

"Why not just use SELECT FOR UPDATE? Isn't that safer?"

It is strict. The problem is how long you hold the lock. The row stays locked from the SELECT until COMMIT, and everything in between (every line of business logic, every network hop) happens while every other writer waits.

Now suppose that "business logic" includes calling a card processor that takes 200 ms to respond, and 10,000 customers are paying the same merchant during a flash sale:

10,000 payments × 200 ms (lock held each) = 2,000 seconds
2,000 s ÷ 60 ≈ 33 minutes

The 10,000th customer waits half an hour to pay. In reality they won't: their request times out long before that, and you've lost the sale.

That's the headline problem, but there are quieter failure modes too:

• Connection pool starvation. Every transaction waiting on a lock is holding a database connection. A hot row can exhaust your pool, and then requests for completely unrelated merchants start failing because there are no connections left.
• Deadlocks. Transfer A→B locks A then B. A simultaneous transfer B→A locks B then A. Each waits for the other forever until the database kills one. (Fix: always lock rows in a consistent order, e.g. by ID.)
• Latency grows linearly with contention. Each additional concurrent writer adds one more lock-hold to the queue.

If you do use pessimistic locks, protect yourself with a timeout so a stuck transaction can't freeze the row indefinitely:

SET LOCAL lock_timeout = '2s';   -- PostgreSQL: fail fast instead of queueing forever

FOR UPDATE NOWAIT (error immediately if locked) and FOR UPDATE SKIP LOCKED (skip locked rows, which is great for job queues) are also worth knowing.

Optimistic locking: let everyone work, check at the end

Optimistic locking flips the assumption. Instead of locking up front, it lets everyone read freely and detects conflicts at write time.

You add a version column to the table:

Every write does three things:

-- 1. READ: grab the value AND its version
SELECT balance, version FROM accounts WHERE id = 'MegaStore';
-- → balance = 100, version = 5
 
-- 2. LOGIC: in application code
-- new_balance = 100 + 50 = 150
 
-- 3. WRITE, conditionally: only if nobody changed the row since we read it
UPDATE accounts
SET balance = 150, version = version + 1
WHERE id = 'MegaStore' AND version = 5;

The magic is AND version = 5 in the WHERE clause. Replay Alice and Bob:

1. Both read balance = 100, version = 5.
2. Alice's UPDATE ... WHERE version = 5 matches. The row becomes 150, version 6. 1 row affected.
3. Bob's UPDATE ... WHERE version = 5 matches nothing, because the version is now 6. 0 rows affected.
4. Bob's code sees 0 rows, re-reads (150, version 6), recomputes 200 and retries with WHERE version = 6. Success.

Nothing was overwritten and the final balance is $200. And at no point did anyone hold a lock while running application logic.

The key mental model: "0 rows affected" isn't an error. It's a signal that the world changed, so go re-read and try again.

The retry loop (the part people forget)

Optimistic locking is only half-implemented without a retry loop. Here's a reasonable one in Python. The important details are a bounded number of attempts and backoff with jitter, so that conflicting requests don't retry in lockstep and collide again:

import random
import time
 
 
class ConcurrentUpdateError(Exception):
    pass
 
 
def credit_account(conn, account_id: str, amount, max_attempts: int = 5):
    for attempt in range(1, max_attempts + 1):
        with conn.cursor() as cur:
            cur.execute(
                "SELECT balance, version FROM accounts WHERE id = %s",
                (account_id,),
            )
            balance, version = cur.fetchone()
 
            # Business rules go here: limits, fraud checks, etc.
            new_balance = balance + amount
 
            cur.execute(
                """
                UPDATE accounts
                SET balance = %s, version = version + 1
                WHERE id = %s AND version = %s
                """,
                (new_balance, account_id, version),
            )
 
            if cur.rowcount == 1:
                conn.commit()
                return new_balance
 
        # Someone else won the race: roll back, back off, re-read, retry.
        conn.rollback()
        time.sleep(random.uniform(0, 0.005 * 2 ** attempt))
 
    raise ConcurrentUpdateError(f"Gave up on {account_id} after {max_attempts} attempts")

Most ORMs have this built in, so you rarely write the SQL by hand: JPA/Hibernate's @Version, Rails' lock_version, SQLAlchemy's version_id_col, and similar. They add the version check and raise an exception on conflict. You still own the retry.

A caveat on side effects

A retry re-runs everything between the read and the write. If that window contains a side effect, such as charging a card, sending an email or publishing an event, a retry can do it twice.

So keep the read-to-write window short and free of side effects. In practice that means calling external services outside the balance update and making them idempotent (most payment processors accept an idempotency key for exactly this reason). This also happens to be the fix for pessimistic locking's 33-minute problem: don't hold a lock, or a version window, open across a network call to someone else's system.

The head-to-head

Or in one line: optimistic pays with retries; pessimistic pays with waiting.

When pessimistic locking is the right call

Pessimistic locking isn't wrong. It's just the wrong default for a high-throughput gateway. It's a good fit when:

• Contention on the row is low: a handful of concurrent writers at most.
• The lock is held briefly: short transactions, no external calls inside.
• A retry is more expensive than a wait: for example, the work between read and write is heavy or not safely repeatable.
• Simplicity matters more than throughput: a back-office tool, a batch job, an admin action.

When optimistic locking wins

• High overall concurrency, but conflicts on any single row are rare. That describes most payment gateways: millions of transactions spread across millions of accounts.
• You care about tail latency. Nobody queues behind anybody.
• Load is spiky. Optimistic locking degrades gracefully when traffic suddenly jumps, while lock queues can collapse.

A third option: let the database do it

Worth a mention in an interview: raising the transaction isolation level can also prevent lost updates. In PostgreSQL, under REPEATABLE READ or SERIALIZABLE, the second of two concurrent updates to the same row fails with a serialization error, and you retry. That's effectively optimistic concurrency managed by the database.

Behavior varies between engines, though. MySQL's InnoDB, for example, does not prevent lost updates at its default REPEATABLE READ level unless you use locking reads. So check your database's docs rather than assuming. The explicit version column has the advantage of behaving the same everywhere and being visible in your code.

The verdict

For a general-purpose payment gateway, optimistic locking is the better default. It keeps throughput high, avoids deadlocks, and scales gracefully when load spikes. Reach for pessimistic locking when contention is low, transactions are short, and you want the simplest possible correct code.

The rule I'd keep: use the simplest approach that is correct and performs well for your workload.

Recap

1. Pessimistic locking (SELECT ... FOR UPDATE) prevents conflicts by making others wait. It's simple and safe, but it becomes a bottleneck when the lock is held long or contention is high.
2. Optimistic locking (a version column plus a conditional UPDATE) detects conflicts at write time. Zero rows affected means re-read and retry.
3. Optimistic locking needs a bounded retry loop with jittered backoff, and a read-to-write window with no side effects.
4. Never hold a lock (or a version window) open across a slow external call.

Optimistic locking rests on one assumption: conflicts are rare. What happens when they aren't, when a single merchant receives 500 payments per second and nearly every write collides?

Next: Part 3: The Hot Row Problem: Scaling One Balance to 500 Writes per Second