Optimistic vs Pessimistic Locking: Version Numbers vs Locks

Two sales that read the same stock count at the same moment each write back their own answer, so one sale vanishes from the count. Optimistic locking and pessimistic locking are the two classic fixes for this.

Picture a common setup. A chain of neighbourhood pharmacies keeps one stock count per item per store in a table in PostgreSQL, an open-source database. Each store sells at the counter and through home-delivery orders placed in an app. The setup is made up, but the bug is real.

One evening a store has 3 boxes of insulin pens. The counter sells one box, and in the same second a delivery order takes another. Here is what each request does.

• The counter request reads the stock: 3.
• The delivery request reads the stock: also 3, because the counter has not saved yet.
• The counter writes 3 minus 1, so 2.
• The delivery request writes 3 minus 1, also 2.

Two boxes left the store, but the count dropped by only one. The app now shows 2 boxes when the shelf has 1, and nobody sees an error. Later it may accept a delivery order for a box that is gone.

On a busy row it is not a rare corner case. We ran this plain read-then-write sale 1,600 times from 8 workers against one row in PostgreSQL 16.15. The stock fell by only 229 to 269 boxes across three runs.

A lost update happens when two transactions read the same row, both change it, and the later write replaces the earlier one without any error. The data looks fine but is wrong.

A few plain words first. A transaction is a group of database steps that succeed or fail together. Commit saves them for good, and rollback cancels them. A race condition is a bug whose result depends on which request finishes first.

The pharmacy bug is a read, modify, write race. Each request reads a value, does the maths in the app, and writes the result back. In between, the row changed, and the request never noticed.

On PostgreSQL's default settings, it does not. We ran both sales as transactions on PostgreSQL 16.15. The delivery request's UPDATE waited until the counter committed, then wrote 2 anyway.

The wait came from the row lock that every UPDATE takes. It did not help, because the delivery request had already read 3 and worked out 2 in the app. The database protected the write, not the read before it.

Concurrency control is the name for any method that stops these races. Pessimistic methods assume a clash will happen and block it early. Optimistic methods assume a clash is rare and check for it at the end.

Pessimistic locking means you lock a row before you change it, so nobody else can change it until your transaction ends. Other requests for the same row wait in line.

Think of a neighbourhood lending library. You phone ahead and put a book on hold. The librarian keeps it behind the desk with your name on it. Anyone else who asks must wait until you collect it or the hold ends.

SQL is the language you use to talk to these databases, and SELECT is its command for reading rows. You take the lock with SELECT ... FOR UPDATE, which PostgreSQL and MySQL both support. The PostgreSQL 18 documentation says it stops the rows being locked, modified or deleted by other transactions until the current transaction ends.

Plain reads are not blocked. In PostgreSQL, row locks block only other writers and lockers of the same row, so a stock report can still read the count. In our test it read 3 while the row was locked.

Here is how the evening race plays out with a lock. Notice that the delivery request waits, so it never sees the old value of 3.

Here is the sale with a lock. It uses psycopg 3, a PostgreSQL driver for Python, with a connection in autocommit mode. In that mode each statement saves on its own, unless the code opens a transaction itself, as this function does.

def sell_with_lock(conn, store_id, sku, qty):
    # conn: a psycopg 3 connection opened with autocommit=True
    with conn.transaction():                       # BEGIN ... COMMIT
        conn.execute("SET LOCAL lock_timeout = '2s'")
        row = conn.execute(
            "SELECT stock FROM inventory "
            "WHERE store_id = %s AND sku = %s FOR UPDATE",
            (store_id, sku)).fetchone()            # waits here if the row is held
        if row is None or row[0] < qty:
            return "out_of_stock"
        conn.execute(
            "UPDATE inventory SET stock = stock - %s "
            "WHERE store_id = %s AND sku = %s",
            (qty, store_id, sku))
    return "sold"                                  # COMMIT released the lock

Two lines matter most. FOR UPDATE takes the row lock, so a second sale of the same item waits on that line. SET LOCAL lock_timeout makes a waiting request give up after 2 seconds, for this transaction only. Giving up raises psycopg's LockNotAvailable error, which the caller should turn into a busy message.

Why the timeout? PostgreSQL's lock_timeout is 0 by default, which means no limit. The documentation says a transaction waiting for a lock waits indefinitely, so long as no deadlock is detected.

You can check it with psql, PostgreSQL's command-line client. In one session, run BEGIN, then the FOR UPDATE query. In a second session, run BEGIN, then SET LOCAL lock_timeout = '2s', then the same query.

The second query should fail after about 2 seconds with SQLSTATE 55P03, the standard error code for a lock that is not available. The message reads: canceling statement due to lock timeout. Ours failed after 2.0 seconds.

Optimistic locking means you read without locking, then save only if the row has not changed since you read it. A version number on the row tells you whether it changed.

Back to the library. This time you do not phone ahead. You take the book off the shelf and note the last stamp on its date slip. At the desk, the librarian checks for a newer stamp. A new one means someone borrowed it meanwhile, so you look again.

In the database, that stamp is a version column. Each successful update adds 1 to it. Each update also says: change this row only if its version is still the one I read.

The check and the write sit in one UPDATE statement, so the database does both as one step. The WHERE clause is the part that picks which rows to change. If the row has moved on, it matches nothing and zero rows change.

Where the picture breaks: a librarian serves one person at a time, so the stamp check can never race. A database serves many requests at once, so the check must sit inside the same statement as the write.

Here is the optimistic sale, using the same kind of connection. It reads the stock and version, then tries a conditional update. If no row changed, it waits a short random time and tries again, up to 3 times.

import random
import time


def sell(conn, store_id, sku, qty, tries=3):
    # conn: autocommit=True, so each statement commits on its own
    for attempt in range(tries):
        row = conn.execute(
            "SELECT stock, version FROM inventory "
            "WHERE store_id = %s AND sku = %s", (store_id, sku)).fetchone()
        if row is None or row[0] < qty:
            return "out_of_stock"
        stock, version = row
        cur = conn.execute(
            "UPDATE inventory SET stock = %s, version = version + 1 "
            "WHERE store_id = %s AND sku = %s AND version = %s",
            (stock - qty, store_id, sku, version))
        if cur.rowcount == 1:                      # our version still matched
            return "sold"
        if attempt < tries - 1:                    # lost the race: wait, retry
            time.sleep(random.uniform(0, 0.005 * 2 ** attempt))
    return "busy_try_again"

The rowcount tells the app which way it went: 1 means saved, 0 means someone got there first. After 3 failed tries the function returns busy_try_again instead of retrying forever, and the app asks the user to tap again.

You can watch the check work in psql in under a minute. Run this in a scratch database. Both saves expect version 7, just like the two sales.

CREATE TABLE inventory (store_id int, sku text, stock int, version int,
                        PRIMARY KEY (store_id, sku));
INSERT INTO inventory VALUES (3, 'INS-PEN', 3, 7);

-- the counter's save: expects version 7
UPDATE inventory SET stock = 2, version = version + 1
WHERE store_id = 3 AND sku = 'INS-PEN' AND version = 7;

-- the delivery app's save: also read version 7
UPDATE inventory SET stock = 2, version = version + 1
WHERE store_id = 3 AND sku = 'INS-PEN' AND version = 7;

SELECT stock, version FROM inventory;

Expected output on PostgreSQL: the first save reports UPDATE 1 and the second reports UPDATE 0. The row ends at stock 2, version 8.

CREATE TABLE
INSERT 0 1
UPDATE 1
UPDATE 0
 stock | version
-------+---------
     2 |       8
(1 row)

The idea is old. In 1979, H. T. Kung and John T. Robinson presented two families of non-locking concurrency controls at the VLDB (Very Large Data Bases) conference.

Their abstract calls the methods optimistic because they rely mainly on transaction backup, meaning undoing a transaction, while hoping that conflicts will not occur.

Pessimistic locking prevents a clash by making others wait, while optimistic locking lets the clash happen and catches it at save time. One pays with waiting, the other with retries.

Notice the row about HTTP calls. Many real edits have a long gap between reading and saving, such as a person editing a form. A lock cannot stay open that long, but a version number can wait for hours.

Use optimistic locking when two writers rarely touch the same row and a retry is cheap. Use pessimistic locking when many writers fight over the same few rows, or a failed save would be costly to redo.

The question to ask is how often conflicts really happen. Measure it: check your write logs for two writes to the same row within a second. Once versions are in place, count failed checks per row. In the pharmacy app, the rows split clearly (the rates are made up).

• Product details such as name, price and pack size: edited a few times a month. Conflicts almost never happen, so optimistic fits.
• Stock for slow movers, like a rare eye drop: a few sales a day. Optimistic fits.
• Stock for fast movers, like paracetamol strips on a fever-season evening: dozens of sales a minute across counter and app. Conflicts are frequent.

These signs point to pessimistic locking:

• Many requests update the same few rows at the same moment, like a flash sale on one item.
• Each attempt does slow or costly work, so redoing it after a failed check hurts.
• The work cannot be safely repeated, such as calling a supplier's ordering system.
• The whole change fits inside one short database transaction.

These signs point to optimistic locking:

• Reads far outnumber writes, and writers rarely collide.
• The gap between reading and saving is long, like a person editing a product form.
• The data travels through an API, a mobile app or a cache before it is saved.
• The data store offers conditional writes but no row lock you can hold across steps.

Follow this decision path from the top. Notice the first question, which people often skip: can one atomic statement do the whole job? Atomic means it runs as one step that cannot be split.

That first box saves the most work. A plain sale only needs to subtract 1 and refuse to go below zero. One statement does that.

def sell_atomic(conn, store_id, sku, qty):
    cur = conn.execute(
        "UPDATE inventory SET stock = stock - %s "
        "WHERE store_id = %s AND sku = %s AND stock >= %s",
        (qty, store_id, sku, qty))
    return "sold" if cur.rowcount == 1 else "out_of_stock"

We ran two of these on the last box at the same time on PostgreSQL 16.15. The second waited for the first to commit, checked stock >= 1 again on the new value, and changed 0 rows.

The PostgreSQL documentation describes that re-check: at the default level, a waiting UPDATE re-tests its WHERE clause on the updated row. So two sales cannot both take the last box.

So why keep the other two at all? Some changes do not fit in one statement. Editing a product's price, pack size and supplier together suits a version check. Moving stock between two stores in one transaction suits locks.

Every safe fix kept the count right, but they paid in different ways. The lock made sales queue, the version check made about a fifth of sales give up, and the atomic update was fastest in all three runs.

Method: PostgreSQL 16.15, psycopg 3.3.6 and Python 3.13 on one machine. 8 threads each made 200 one-box sales of the same item, using the three functions shown in this guide plus the plain read-then-write version. A row that many requests change at once is called a hot row. We ran it three times.

Read the first row slowly. The app recorded 1,600 sales, but stock fell by about 250. That gap is the lost update at scale, and no error was raised.

The two optimistic rows show why the random wait matters. Retrying at once made more than half the sales give up. A short random wait before each retry cut that to about a fifth.

When each thread sold from its own row, every approach recorded and removed all 1,600 sales and nobody gave up. Contention, meaning many requests fighting over one row, decides the cost more than the method does.

Pessimistic locking can cause deadlocks, long queues and stuck requests. Each comes from holding a lock too long, or from taking locks in different orders.

A deadlock is when two transactions each hold a lock the other needs, so neither can move. Stock transfers between two stores are the classic way to hit one.

1. 1

   Transfer A starts

   It moves 5 boxes from store 3 to store 7. It locks the store 3 row first.

2. 2

   Transfer B starts

   At the same moment it moves 3 boxes from store 7 to store 3. It locks the store 7 row first.

3. 3

   Both reach for the second row

   Transfer A wants store 7, which B holds. Transfer B wants store 3, which A holds. Neither can continue.

4. 4

   The database steps in

   After waiting deadlock_timeout, 1 second by default, PostgreSQL checks for a deadlock. It aborts one transaction with SQLSTATE 40P01, and the other carries on.

5. 5

   The fix

   Every transfer locks its two rows in the same order, lowest store id first. Then the second transfer simply waits for the first, as it did in our rerun.

We ran both versions. With opposite orders, one transfer failed after 1.0 second with deadlock detected. With the same order, the second transfer waited and then finished.

The PostgreSQL documentation gives the same advice: take locks in a consistent order across the app. It adds that which transaction gets aborted is hard to predict, and that aborted transactions can be retried.

Long queues are the other risk. If one slow request holds a busy row for a second, every sale of that item waits a second. PostgreSQL offers two ways out of the queue.

• NOWAIT makes the statement fail at once instead of waiting. Ours failed in under 0.01 seconds with SQLSTATE 55P03: could not obtain lock on row.
• SKIP LOCKED skips rows another transaction holds. The documentation warns this gives an inconsistent view, but suits many workers taking jobs from a queue-like table.

The packing screen is a good fit for SKIP LOCKED. Several helpers ask for the next unpacked order. In our test, two helpers asking at once got orders 101 and 102, not the same one twice.

Why an index matters more for MySQL locks

An index is a lookup structure, like the index at the back of a book, that finds rows without reading the whole table. In PostgreSQL, a locking read locks the rows it returns. A few documented edge cases exist, such as rows skipped by OFFSET, the clause that skips the first rows of a result. Even with no index on the table, our test locked one store's row while another session locked a different store's row freely.

MySQL's InnoDB storage engine works differently. Its 8.4 manual says a locking read locks every index record, or index entry, it scans, and that without a suitable index every row of the table becomes locked.

We tested on MySQL 8.0.46, not 8.4, and saw the same rule. With no index, a FOR UPDATE NOWAIT on store 7 failed at once with error 3572 while store 3 was locked. With a unique index on store and item, which allows each pair only once, it worked.

Optimistic locking can waste work through retry storms, starve an unlucky request, and silently fail if any writer forgets the version. It also needs a clear message when a save is rejected.

A retry storm happens when many requests fail the version check together and all retry at once. They collide again, fail again, and the load grows. Our no-wait runs showed it: more than half the sales gave up.

• Cap the retries, like the 3 tries in the code above, then return a clear busy message.
• Wait a short random time before each retry, so retries spread out instead of colliding again.
• Watch the failed-check rate per row. A high rate means that row is a poor fit for optimistic locking.
• Move hot rows to an atomic update or a lock, and keep optimistic locking for the rest.

Starvation is the quieter problem. One slow request, like a big order editing many lines, can keep losing to quick small sales. It retries and loses every time.

Then there is the human side. When a person edits a product form and their save is rejected, a bare error feels broken. Show what changed, and offer to reload with the new values.

That connects to idempotency, which means an action gives the same result when repeated. Retrying a sale is only safe if the whole attempt can run twice without harm.

A version check still waits for a moment

Optimistic locking does not mean the database never waits. The version check runs inside an UPDATE, and an UPDATE takes a row lock. If another transaction has updated the row but not committed, the check waits.

In our PostgreSQL 16.15 test at the default level, the waiting check resumed when the winner committed, saw version 8 and changed 0 rows. When the winner rolled back instead, the check changed 1 row.

The wait lasts only as long as the winner's transaction. That is one more reason to keep transactions short, or to run each statement in autocommit mode as the code above does.

At the stricter Repeatable Read isolation level, explained in the next section, the loser does not get 0 rows. It fails with SQLSTATE 40001, could not serialize access due to concurrent update. Retry code at that level must catch this error too.

In PostgreSQL, yes: Repeatable Read and Serializable abort the losing transaction. In MySQL's default Repeatable Read, no: our test still lost the update. Either way, it only works inside one transaction.

An isolation level is a database setting for how much one transaction sees of others running at the same time. In PostgreSQL's default, Read Committed, each read sees only data committed before that read began. Repeatable Read keeps one view for the whole transaction, and Serializable acts as if transactions ran one after another. The same level name can behave differently in different databases.

The MySQL manual explains the last row. Its Repeatable Read snapshot applies to SELECT statements, not necessarily to writes. For the freshest state it points you to Read Committed or a locking read.

So in MySQL, use FOR UPDATE, a version check or an atomic update. And in any database, a read and a save in separate HTTP requests are separate transactions, so you still need a version.

Across APIs, optimistic locking usually travels as a version or ETag that the client sends back with its update. The server saves only if that value still matches the current one.

HTTP has this built in. An ETag is a short tag the server attaches to a response to name one version of a resource. The client returns it in an If-Match header, a named field sent with the request, when it saves.

RFC 9110, the HTTP standard, says If-Match is most often used to prevent accidental overwrites, the lost update problem. If the condition is false, the server must not perform the request.

The server may then answer with status 412, Precondition Failed. A server can go further with status 428, Precondition Required, from RFC 6585, to reject saves that carry no condition at all.

1. 1

   Load the product

   A store manager's tablet fetches the insulin pen product. The server returns it with ETag v12.

2. 2

   Edit on the tablet

   The manager changes the reorder level from 10 to 15, taking a few minutes to check with a supplier.

3. 3

   Someone else saves first

   Meanwhile another manager edits the same product. Its version becomes v13.

4. 4

   The tablet saves

   It sends the change with If-Match v12. The server compares it with v13.

5. 5

   The server refuses

   It returns 412. The tablet reloads the latest values, shows what changed, and lets the manager decide again.

Other databases document the same idea. Amazon DynamoDB's guide for DynamoDBMapper, part of the AWS SDK for Java, describes a version attribute. A save succeeds only if the version in the table still matches the client's copy.

A mismatch throws a ConditionalCheckFailedException. Global tables are copies of a table in several AWS Regions that all accept writes. As of October 2026, the same page warns that global tables settle concurrent updates by last writer wins, so this locking does not work as expected there.

Why a version number beats a timestamp

A timestamp column looks like a free version, but it has a trap a counter does not. In PostgreSQL, now() returns the time the current transaction started, not the time it saves.

We tested this on PostgreSQL 16.15. Transaction A started first, then B updated the row and committed. When A saved later, its timestamp was earlier than B's. Any check that asks whether a row is newer than a time you saw would be fooled.

A counter that adds 1 on every write cannot go backwards. Locks shared across many servers are a different tool with their own failure cases, covered in the related guide on distributed locks.

Explain it as one problem, the lost update, with answers that trade waiting for retries. Then pick one for each kind of data and justify it with how often conflicts happen.

1. 1

   Name the race

   Describe two requests that read the same value and both write. Draw the lost update on the board in four lines.

2. 2

   Offer the simplest fix

   Ask whether one atomic conditional update can do it. For a stock count, it usually can.

3. 3

   Explain both locking styles

   Pessimistic locks first and makes others wait. Optimistic checks a version at save time and makes the loser retry.

4. 4

   Choose by conflict rate

   Rare conflicts and long edits suit optimistic. Busy rows and costly retries suit pessimistic.

5. 5

   Cover the failure cases

   Lock ordering and timeouts for pessimistic. Retry limits, random waits and safe-to-repeat code for optimistic.

6. 6

   Show it at the API edge

   Mention ETags with If-Match and a 412 response for edits that span a person's think time.