Optimistic vs Pessimistic Locking: SELECT FOR UPDATE vs Version Column

Optimistic vs pessimistic locking in one line: pessimistic locking takes the row lock before reading, with SELECT ... FOR UPDATE, so a second writer waits until you commit; optimistic locking reads a version number, writes with UPDATE ... WHERE id = ? AND version = ?, and treats a row count of zero as "someone got there first, read again and retry". Both prevent the lost update. They differ in where the waiting happens: inside the database, holding a lock, or in your code, repeating the attempt. PostgreSQL syntax below is from its manual, as of October 2026; the SQLite demonstration runs live.

Strategy	How it stops the lost update	Cost
Pessimistic, SELECT ... FOR UPDATE	the second writer blocks until the first commits	a lock held for the whole think time; deadlocks possible
Optimistic, version column	the second writer's UPDATE matches 0 rows and retries	wasted work under contention; the app must loop
Idempotency key	a repeated request finds its own earlier result	a unique index and stored responses

The problem it solves

Two curators lend one more object from the Hallam museum at the same time. Each reads objects = 3, each computes 4, each writes 4. The ledger says 4 where it should say 5: one update is lost, and no error was raised anywhere. Isolation levels alone do not save you here; under READ COMMITTED, the default in PostgreSQL, both reads are legal and both writes succeed. The fix is to make the read and the write belong together, either by locking between them or by checking at write time that nothing changed in between.

The intuition

Pessimistic: assume a conflict, and lock first. SELECT ... FOR UPDATE reads the row and takes a row-level lock as if you were about to update it. Any other transaction that tries to UPDATE, DELETE or SELECT ... FOR UPDATE the same row waits until yours commits or rolls back, then sees your committed value. The lost update becomes impossible because the second reader cannot read until the first writer is done. The price is the lock itself: it is held for as long as the transaction is open, including any time spent thinking, calling another service, or waiting for a user. Two transactions locking the same rows in different orders can deadlock; PostgreSQL detects that and aborts one. FOR UPDATE NOWAIT errors instead of waiting, and FOR UPDATE SKIP LOCKED skips locked rows, which is how queue tables hand out jobs.

Optimistic: assume no conflict, and check at write time. Add a version column. Read the row and its version. Write with UPDATE ... SET objects = ?, version = version + 1 WHERE id = ? AND version = ?. If the row still has the version you read, exactly one row changes. If someone committed in between, the version moved on, the WHERE matches nothing, and the row count is 0: the signal to reread and try again, or to tell the user. No lock is held between read and write, so the two can even be separate HTTP requests from a form the user filled in ten minutes ago, with the version carried in a hidden field or an ETag. The price is paid only when conflicts happen: a retry means repeating the read and the computation, and under heavy contention on one row most attempts can fail.

Idempotency keys solve a different problem. Neither strategy helps when the same request is sent twice because a client retried after a timeout; both would happily apply it twice. A unique key per request, stored with the result, makes the second attempt return the first answer. Payment systems use all three, as in design a ledger.

The choice follows contention and transaction shape. Many writers on few rows, short transactions: lock, because retries would mostly fail anyway. Few conflicts, long gaps or stateless APIs between read and write: version column, because a lock cannot be held across requests. Row-level locks are the subject of the manual's locking chapter; the anomalies each isolation level allows are in SQL isolation levels explained.

Watch it run

The animation is the lesson's museum loan. BEGIN, and the engine notes the state it would have to return to. Hallam goes from 3 to 9, applied but invisible to other connections. Verrey would go to -1, the CHECK fires, and the rollback restores both rows. That is the guarantee a single transaction gets: all or nothing. What it does not show is a second connection reading Hallam at the same moment, and that is the gap both locking strategies close.

The code

The two strategies in PostgreSQL syntax:

-- illustrative: pessimistic, the lock is held until COMMIT
BEGIN;
SELECT objects FROM loan WHERE museum = 'Hallam' FOR UPDATE;   -- others wait here
UPDATE loan SET objects = objects + 1 WHERE museum = 'Hallam';
COMMIT;

-- illustrative: optimistic, no lock between the read and the write
SELECT objects, version FROM loan WHERE museum = 'Hallam';      -- (3, 0)
UPDATE loan SET objects = 4, version = version + 1
 WHERE museum = 'Hallam' AND version = 0;                      -- UPDATE 1, or UPDATE 0: retry

Now the lost update for real, in SQLite. Two connections share one database file; the connection is in manual transaction mode and has a short lock timeout so the pessimistic demonstration below fails fast instead of hanging. Both curators read 3 and both write 4:

import os, sqlite3, tempfile

path = os.path.join(tempfile.mkdtemp(), "loan.db")       # a file: two connections can share it
def connect():
    return sqlite3.connect(path, isolation_level=None, timeout=0.2)   # we write BEGIN ourselves

setup = connect()
setup.executescript("""
  CREATE TABLE loan (museum TEXT PRIMARY KEY, objects INTEGER CHECK (objects >= 0),
                     version INTEGER NOT NULL DEFAULT 0);
  INSERT INTO loan (museum, objects) VALUES ('Hallam', 3), ('Verrey', 0);
""")
setup.close()
objects = lambda: connect().execute("SELECT objects FROM loan WHERE museum = 'Hallam'").fetchone()[0]

# two curators, two connections, both lending Hallam one more object
alice, bob = connect(), connect()
a_seen = alice.execute("SELECT objects FROM loan WHERE museum = 'Hallam'").fetchone()[0]   # 3
b_seen = bob.execute("SELECT objects FROM loan WHERE museum = 'Hallam'").fetchone()[0]     # 3
alice.execute("UPDATE loan SET objects = ? WHERE museum = 'Hallam'", (a_seen + 1,))        # writes 4
bob.execute("UPDATE loan SET objects = ? WHERE museum = 'Hallam'", (b_seen + 1,))          # writes 4
print("both read", a_seen, "and", b_seen, "-> stored", objects())   # both read 3 and 3 -> stored 4
print("expected", 3 + 2)                                             # expected 5   <- one update is lost

The optimistic version. Alice reads first, Bob completes a whole attempt, then Alice's write carries the version she saw and matches nothing. Her retry rereads and succeeds:

def lend_optimistic(conn, museum, attempts=3):
    """Read the version, update only if it is still that version, retry otherwise."""
    for attempt in range(1, attempts + 1):
        objects, version = conn.execute(
            "SELECT objects, version FROM loan WHERE museum = ?", (museum,)).fetchone()
        changed = conn.execute(
            "UPDATE loan SET objects = ?, version = version + 1 WHERE museum = ? AND version = ?",
            (objects + 1, museum, version)).rowcount
        print(f"  attempt {attempt}: saw version {version}, rowcount {changed}")
        if changed == 1:
            return objects + 1
    raise RuntimeError("gave up after retries")

connect().execute("UPDATE loan SET objects = 3, version = 0 WHERE museum = 'Hallam'")
# Alice reads first, then Bob commits a full attempt before Alice's UPDATE runs
a_objects, a_version = alice.execute(
    "SELECT objects, version FROM loan WHERE museum = 'Hallam'").fetchone()        # (3, 0)
print("bob:")
print("  ->", lend_optimistic(bob, "Hallam"))
alice_changed = alice.execute(
    "UPDATE loan SET objects = ?, version = version + 1 WHERE museum = 'Hallam' AND version = ?",
    (a_objects + 1, a_version)).rowcount
print("alice's stale write: rowcount", alice_changed)                              # 0: nothing lost
print("alice retries:")
print("  ->", lend_optimistic(alice, "Hallam"))
print("stored", objects(), "version", connect().execute(
    "SELECT version FROM loan WHERE museum = 'Hallam'").fetchone()[0])
# bob:
#   attempt 1: saw version 0, rowcount 1
#   -> 4
# alice's stale write: rowcount 0
# alice retries:
#   attempt 1: saw version 1, rowcount 1
#   -> 5
# stored 5 version 2

Two increments, stored 5, version 2, and the only cost was one repeated read on Alice's side.

The pessimistic version. SQLite has no SELECT ... FOR UPDATE; its locks are per database, not per row, and BEGIN IMMEDIATE takes the write lock up front, which plays the same role. While Alice holds it, Bob's attempt to start his own write transaction waits for the timeout and fails; after her commit he gets the lock, reads 4 and writes 5:

# SQLite has no SELECT ... FOR UPDATE; BEGIN IMMEDIATE takes the write lock up front instead
connect().execute("UPDATE loan SET objects = 3 WHERE museum = 'Hallam'")
alice.execute("BEGIN IMMEDIATE")                       # the pessimistic lock, held until COMMIT
seen = alice.execute("SELECT objects FROM loan WHERE museum = 'Hallam'").fetchone()[0]
try:
    bob.execute("BEGIN IMMEDIATE")                     # waits up to the 0.2 s timeout, then
except sqlite3.OperationalError as e:
    print("bob:", e)                                   # bob: database is locked
alice.execute("UPDATE loan SET objects = ? WHERE museum = 'Hallam'", (seen + 1,))
alice.execute("COMMIT")                                # the lock is released here
bob.execute("BEGIN IMMEDIATE")                         # now bob gets the lock ...
seen = bob.execute("SELECT objects FROM loan WHERE museum = 'Hallam'").fetchone()[0]   # ... and reads 4
bob.execute("UPDATE loan SET objects = ? WHERE museum = 'Hallam'", (seen + 1,))
bob.execute("COMMIT")
print("stored", objects())                             # stored 5   <- nothing lost, nobody retried

In PostgreSQL Bob would simply block on the row rather than time out, and other rows would stay writable; the shape of the guarantee is the same.

The complexity

• Pessimistic: one extra lock per row read, no extra round trips, but throughput on a hot row is bounded by the length of the transaction that holds it: everyone else queues behind the slowest writer.
• Optimistic: zero locks held across the gap, one extra integer column, and a retry that repeats the read and the computation. Expected attempts stay near 1 while conflicts are rare and climb quickly once many writers aim at one row.
• Idempotency key: one unique-index lookup per request and storage for the stored result; it costs the same whether or not anyone retries.

Where it goes wrong

• A FOR UPDATE across a user's think time. The row stays locked while a form is open; everyone else waits or times out. Use a version column there.
• Forgetting to check the row count. An optimistic UPDATE that matches 0 rows is not an error; the database says nothing. If the code does not check rowcount, the lost update is back.
• Locking rows in different orders. Transaction A locks Hallam then Verrey, B locks Verrey then Hallam: a deadlock the database resolves by killing one. Lock in a fixed order.
• Unbounded retries. Under heavy contention an optimistic loop can spin; cap the attempts and back off, or switch that row to a lock.
• Treating either as protection against duplicate requests. A retried request is a new transaction with a fresh read. Only an idempotency key stops it from applying twice.

When it shows up in interviews

As the follow-up to any balance, stock or seat problem: "two requests arrive at once, how do you keep the number right?" The expected answer names the lost update, gives both fixes with their trade-off, and knows that isolation level alone is not the answer. It also appears in the ledger and inventory design questions, where the interviewer listens for the version column on a hot account and an idempotency key on the request. The transaction machinery underneath is the Transactions and ACID lesson, and the with db: rollback it builds on is in ACID transactions explained.

How to say it in an interview

"Two read-modify-write transactions on one row lose an update unless the read and the write are tied together. Pessimistically, I SELECT ... FOR UPDATE so the second writer blocks until I commit; it is simple and right for short transactions on hot rows, but the lock is held for the whole transaction and ordering matters to avoid deadlocks. Optimistically, I read a version column and UPDATE ... WHERE version = ?, treating a zero row count as a conflict to retry; nothing is locked between read and write, so it works across requests, at the cost of retries under contention. For duplicate requests I add an idempotency key, because neither lock stops a replay."

Frequently asked questions

What is the difference between optimistic and pessimistic locking?

Pessimistic locking locks the row before reading it, with SELECT ... FOR UPDATE, so other writers wait until the transaction commits. Optimistic locking locks nothing: it reads a version, updates only WHERE version = ?, and retries when zero rows change because someone else committed first.

When should you use optimistic locking?

When conflicts are rare and the gap between reading and writing is long or spans requests: a form the user edits, a stateless API with an ETag, a document saved minutes after it was opened. A lock cannot be held across that gap; a version check at write time can.

Does SELECT FOR UPDATE prevent the lost update?

Yes. The first transaction's SELECT ... FOR UPDATE locks the row, so the second transaction's SELECT ... FOR UPDATE or UPDATE waits until the first commits and then reads the new value. Both reads must use FOR UPDATE; a plain SELECT does not wait and the lost update returns.

Sources

• Explicit Locking: Row-Level Locks — PostgreSQL Documentation
• SELECT: The Locking Clause — PostgreSQL Documentation
• Transaction Isolation — PostgreSQL Documentation
• Transactions in SQLite: BEGIN IMMEDIATE — SQLite Documentation
• sqlite3 — Connection.rowcount and timeout — Python Documentation