Row Locks and Lost Updates

SELECT ... FOR UPDATE or atomic updates

A lost update happens when two sessions read the same value, compute a new one and write it back: the second write overwrites the first. Prevent it with an atomic statement (UPDATE stock SET qty = qty - 1 WHERE id = 1 AND qty > 0), with SELECT ... FOR UPDATE to lock the row until commit, or with optimistic concurrency (a version column checked on update). For job queues, FOR UPDATE SKIP LOCKED lets workers grab different rows without waiting.

Two concurrent buyers with and without a row lock, run

I ran this with Python 3, psycopg 3.3 and PostgreSQL 16.2, using separate connections to act as concurrent sessions. Two threads each buy one item from a stock of 10. Reading without a lock lets both read 10 and both write 9, losing one sale. With SELECT ... FOR UPDATE, the second buyer waits for the first to commit and the final quantity is correctly 8.

import psycopg, threading
URI = __import__("os").environ["DEMO_URI"]
admin = psycopg.connect(URI, autocommit=True)
admin.execute("CREATE TABLE stock (id int PRIMARY KEY, qty int)")

def run(lock):
    admin.execute("DELETE FROM stock"); admin.execute("INSERT INTO stock VALUES (1, 10)")
    barrier = threading.Barrier(2)
    def buyer():
        with psycopg.connect(URI) as c:
            q = "SELECT qty FROM stock WHERE id = 1" + (" FOR UPDATE" if lock else "")
            if not lock:
                qty = c.execute(q).fetchone()[0]; barrier.wait()      # both read 10 before either writes
            else:
                barrier.wait(); qty = c.execute(q).fetchone()[0]      # second buyer waits for the lock
            c.execute("UPDATE stock SET qty = %s WHERE id = 1", (qty - 1,))
    t = [threading.Thread(target=buyer) for _ in range(2)]
    [x.start() for x in t]; [x.join() for x in t]
    return admin.execute("SELECT qty FROM stock").fetchone()[0]
print("two buyers, read-then-write without locking -> qty", run(False), "(one sale lost)")
print("two buyers, SELECT ... FOR UPDATE            -> qty", run(True))

Output:

two buyers, read-then-write without locking -> qty 9 (one sale lost)
two buyers, SELECT ... FOR UPDATE            -> qty 8

Prefer atomic updates

qty = qty - 1 in a single UPDATE is simpler and safer than reading, computing and writing in application code.

Quick check: What does SELECT ... FOR UPDATE do?

• Makes the query run faster
• Updates the rows immediately
• Deletes the rows
• Locks the selected rows until the transaction ends