Optimistic vs Pessimistic Locking in SQL

Optimistic locking and pessimistic locking in SQL are two ways to decide where a concurrent update should pay its cost: at conflict detection time, or before the conflicting work starts.

The problem usually begins with code that looks safe in a single request. One API request reads a row, checks a business rule, and writes an update. Another request does the same thing a few milliseconds later. Both requests used transactions. Both queries were valid. The bug appears because the two requests made decisions from overlapping views of the same data.

For the broader database cluster, start with SQL And Data Correctness. If the confusing part is what transactions can see from each other, read SQL Isolation Levels Explained first. This article focuses on the next decision: when to detect a write conflict with a version check, when to guard the write in one SQL statement, and when to take a row lock with SELECT ... FOR UPDATE.

The Short Decision

Use optimistic locking when conflicts are possible but uncommon, and the application can safely retry or return a clear conflict to the user.

Use pessimistic locking when conflicts are expected, the invariant is strict, and one transaction must own the row for a short critical section.

Use a single guarded UPDATE when the invariant can be expressed directly in the WHERE clause. This is often simpler than reading first and deciding later.

The choice is not "which locking style is more advanced?"

The real question is:

Where should contention become visible: as a failed write, as a guarded no-op, or as waiting on a lock?

The Lost Update Shape

Consider an order API that reserves the last unit of a product.

The table is simple:

CREATE TABLE product_inventory (
  product_id BIGINT PRIMARY KEY,
  available_quantity INTEGER NOT NULL,
  version INTEGER NOT NULL DEFAULT 1,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

The unsafe application flow often looks like this:

SELECT available_quantity
FROM product_inventory
WHERE product_id = 42;

Both requests see:

available_quantity = 1

Both requests decide that reserving one unit is allowed. Then both write:

UPDATE product_inventory
SET available_quantity = 0
WHERE product_id = 42;

The final value may still be 0, which makes the bug harder to see. The database did not show -1. The business problem is that two orders were allowed to believe they reserved the same last unit.

That is the important mental model: concurrency bugs are not always visible as obviously impossible column values. They often appear as two valid-looking decisions that should not both have been allowed.

This is the same class of problem as many backend race conditions. The broader invariant-first framing is covered in How to Prevent Race Conditions in Backend Systems.

Optimistic Locking Detects A Changed Row

Optimistic locking assumes another transaction might change the row, but that conflicts are uncommon enough that it is better not to block every reader up front.

The usual pattern is:

1. read the row with a version or timestamp
2. make the application decision
3. update only if the version is still the one you read
4. treat 0 rows updated as a real conflict

For the inventory table, the read includes the version:

SELECT product_id, available_quantity, version
FROM product_inventory
WHERE product_id = 42;

Suppose the application reads:

available_quantity = 1
version = 7

The update carries both the business change and the conflict check:

UPDATE product_inventory
SET available_quantity = available_quantity - 1,
    version = version + 1,
    updated_at = now()
WHERE product_id = 42
  AND version = 7
  AND available_quantity >= 1;

If the update affects one row, this request won. If it affects zero rows, the row changed or the quantity is no longer sufficient.

That zero-row result is not an edge case. It is the whole point of the pattern.

Application code should handle it deliberately:

const result = await db.query(
  `
  UPDATE product_inventory
  SET available_quantity = available_quantity - $1,
      version = version + 1,
      updated_at = now()
  WHERE product_id = $2
    AND version = $3
    AND available_quantity >= $1
  `,
  [quantity, productId, version]
)

if (result.rowCount === 0) {
  throw new ConflictError('Inventory changed while the order was being created')
}

The application can then choose the right behavior:

• re-read the row and retry the decision
• return 409 Conflict
• ask the user to refresh
• fail the order with "not enough inventory"
• move the operation into a stricter transaction pattern if conflicts are common

Optimistic locking is useful because it turns a silent overwrite into an explicit conflict.

Optimistic Locking Is Not Just A Version Column

A version column helps only when the application checks it on every write that depends on the previous state.

This update is not optimistic locking:

UPDATE product_inventory
SET available_quantity = available_quantity - 1
WHERE product_id = 42;

It modifies the row, but it does not prove that the request made its decision from a still-valid version.

This is closer:

UPDATE product_inventory
SET available_quantity = available_quantity - 1,
    version = version + 1
WHERE product_id = 42
  AND version = 7
  AND available_quantity >= 1;

There are two checks:

• version = 7 detects whether another write happened since the request read the row
• available_quantity >= 1 protects the local business condition at the write boundary

That second predicate matters. A version check alone detects change, but the business invariant still belongs in the SQL when possible.

For many workflows, a guarded update is enough even without a separate read:

UPDATE product_inventory
SET available_quantity = available_quantity - 1,
    updated_at = now()
WHERE product_id = 42
  AND available_quantity >= 1
RETURNING available_quantity;

If the statement returns a row, the reservation succeeded. If it returns no rows, there was not enough inventory at the moment of the write.

This shape is often better than:

1. read quantity
2. decide in application code
3. write later

The guarded update moves the invariant to the database statement that actually changes the row.

When Optimistic Locking Works Well

Optimistic locking usually fits low-to-moderate contention workflows.

Good examples:

• profile edits
• account settings
• admin changes to business records
• draft document metadata
• order transitions where conflicts are rare
• API requests where the client can safely retry

It works especially well when reads are common and writes to the same row are uncommon.

The operational behavior is pleasant under light contention:

• no transaction waits while another user is looking at a form
• connection time stays short
• most writes succeed on the first attempt
• conflicts are explicit and easy to report

The pattern becomes painful when the same row is hot.

Imagine many requests all trying to reserve the last unit of the same product. If each request reads version 7, only one can update it. The rest will fail. If all of them immediately re-read and retry, the system can create application-level retry pressure around a row that is already under contention.

At that point the database did the right thing, but the workflow may still be noisy.

Pessimistic Locking Claims The Row First

Pessimistic locking assumes conflict is likely enough that the transaction should prevent competing writes before making the decision.

In PostgreSQL this often uses SELECT ... FOR UPDATE:

BEGIN;

SELECT product_id, available_quantity
FROM product_inventory
WHERE product_id = 42
FOR UPDATE;

-- validate the invariant while this transaction owns the row lock

UPDATE product_inventory
SET available_quantity = available_quantity - 1,
    updated_at = now()
WHERE product_id = 42
  AND available_quantity >= 1;

COMMIT;

The important part is not the syntax by itself. The important part is the critical section:

1. lock the row
2. make the decision
3. write the state
4. commit quickly

PostgreSQL's explicit locking documentation explains that row-level locks do not block ordinary reads, but they do block writers and lockers for the same row until the transaction ends. The FOR UPDATE mode locks selected rows against concurrent update, delete, and locking operations.

Official references:

• PostgreSQL explicit locking
• PostgreSQL SELECT locking clause

This makes pessimistic locking useful when the workflow needs one request to own a row temporarily.

Pessimistic Locking Is A Latency Trade-Off

Pessimistic locking can make correctness easier to reason about, but it moves the cost into waiting.

If one transaction holds the row lock, another transaction trying to lock or update the same row must wait, fail fast, or skip the row depending on the SQL you choose.

That creates new operational questions:

• How long can the first transaction stay open?
• What happens to connection-pool usage while requests wait?
• What timeout should the application use?
• Can the transaction deadlock with another transaction?
• Does every code path lock rows in the same order?

PostgreSQL documents that explicit locking can increase deadlock likelihood and that transactions seeking conflicting locks can wait indefinitely if no deadlock is detected. It also recommends consistent lock ordering as a defense against deadlocks.

This is why pessimistic locking should protect short database work, not whole request workflows.

Bad shape:

await db.transaction(async (tx) => {
  const inventory = await tx.query(
    `
    SELECT product_id, available_quantity
    FROM product_inventory
    WHERE product_id = $1
    FOR UPDATE
  `,
    [productId]
  )

  await paymentProvider.charge(customerId, amount)

  await tx.query(
    `
    UPDATE product_inventory
    SET available_quantity = available_quantity - $1
    WHERE product_id = $2
  `,
    [quantity, productId]
  )
})

The row lock now waits on a payment provider. If the provider slows down, the database lock queue grows. A correctness fix can become a latency incident.

The better shape is to choose a transaction boundary that protects the local invariant and records durable intent for work that happens later. That boundary is the subject of Database Transaction Boundaries in Backend APIs.

NOWAIT And SKIP LOCKED Are Different Policies

Pessimistic locking does not always have to wait.

PostgreSQL's SELECT syntax supports locking clauses such as FOR UPDATE, and options such as NOWAIT and SKIP LOCKED.

NOWAIT says: if the row cannot be locked immediately, fail instead of waiting.

SELECT product_id, available_quantity
FROM product_inventory
WHERE product_id = 42
FOR UPDATE NOWAIT;

That can be useful when the API would rather return a clear "try again" response than tie up a request waiting behind another transaction.

SKIP LOCKED says: skip rows that are currently locked and return other eligible rows.

SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 1;

That is useful for worker queues because workers do not need to fight over the same job. They need to find different claimable jobs.

For a full production queue design around that primitive, use PostgreSQL Job Queues with SKIP LOCKED. In an inventory API, SKIP LOCKED would usually be the wrong instinct because skipping the locked product row does not answer whether the user can buy that product.

The lock option is a business policy, not just a database trick:

How Isolation Levels Fit In

Locking and isolation are connected, but they are not the same decision.

An isolation level describes what a transaction can observe while other transactions are running. A locking strategy describes how this workflow prevents or detects conflicting changes.

PostgreSQL's transaction isolation documentation explains that Read Committed is the default and that two successive SELECT statements in one transaction can see different committed data if another transaction commits between them. PostgreSQL's application-level consistency docs also warn that business-rule checks in Read Committed can be difficult because the view of data shifts between statements.

Official references:

• PostgreSQL transaction isolation
• PostgreSQL data consistency checks at the application level

That matters because this transaction boundary is not automatically safe:

BEGIN;

SELECT available_quantity
FROM product_inventory
WHERE product_id = 42;

-- application decides the order is allowed

UPDATE product_inventory
SET available_quantity = available_quantity - 1
WHERE product_id = 42;

COMMIT;

The transaction groups the statements atomically, but the plain SELECT did not lock the row, and the UPDATE did not include the business condition.

Safer options include:

• put the invariant in the UPDATE ... WHERE clause
• use a version check and handle 0 rows updated
• lock the row with SELECT ... FOR UPDATE before checking
• raise the isolation level and handle serialization failures where appropriate
• use a unique constraint when the invariant is really uniqueness

The right answer depends on the invariant.

Choosing By Invariant

A useful locking decision starts with the thing that must stay true.

Notice that the table does not say "optimistic for everything" or "pessimistic for important things."

Some strict invariants are best protected by a single guarded statement. Some user-facing workflows need optimistic conflicts. Some worker workflows need row claiming. Some cross-row rules need constraints, serializable transactions, or explicit lock ordering.

The implementation follows the invariant, not the label.

Testing The Conflict Path

Do not treat locking behavior as something the ORM will prove for you.

At minimum, test the path where two operations overlap.

A simplified optimistic-locking test might do this:

const original = await inventory.get(productId)

await Promise.allSettled([
  inventory.reserveWithVersion(productId, 1, original.version),
  inventory.reserveWithVersion(productId, 1, original.version),
])

const after = await inventory.get(productId)

expect(after.availableQuantity).toBe(0)
expect(await orders.countSuccessfulReservations(productId)).toBe(1)

The exact test shape depends on your stack, but the assertion should be about the invariant, not only about an exception type.

For pessimistic locking, test the blocked or failed path too:

• one transaction locks the row
• a second request attempts the same operation
• the second request waits, times out, fails fast, or retries according to policy
• the final business state is still valid

Production monitoring should then watch the same failure modes:

This is the difference between "we added locking" and "we know the contention model works."

Common Mistakes

Reading First, Then Writing Without A Predicate

The most common bug is a separate read followed by an unconditional write.

SELECT available_quantity
FROM product_inventory
WHERE product_id = 42;

UPDATE product_inventory
SET available_quantity = available_quantity - 1
WHERE product_id = 42;

The first query helped the application decide, but the second query did not prove that the decision was still valid.

Holding Locks Around External Calls

Database locks should not wait for payment providers, email services, webhooks, or message brokers.

Record durable local state inside the transaction. Let external work happen after commit through a reliable workflow.

Retrying Conflicts Without Rerunning The Decision

An optimistic conflict means the original decision was made against old data.

The retry should re-read the current state and rerun business rules. It should not blindly resubmit the same write with a new version.

Assuming SELECT FOR UPDATE Solves Every Consistency Problem

Row locks protect selected rows. They do not automatically protect the absence of rows, cross-row counts, or downstream side effects.

If the rule is "there can be only one active subscription per customer," a unique partial index may be a stronger primitive than application locking.

If the rule spans multiple rows or tables, think carefully about constraints, lock ordering, serializable transactions, and retry behavior.

Ignoring The Failure Mode

Every strategy has a failure mode:

• optimistic locking returns a conflict
• guarded updates affect zero rows
• pessimistic locking waits, fails, skips, or deadlocks
• serializable transactions can abort and require retry
• constraints raise errors

The application should treat those outcomes as designed behavior, not strange database surprises.

Practical Checklist

Before choosing optimistic or pessimistic locking, answer these questions:

• What invariant must remain true after concurrent requests?
• Can the invariant be expressed directly in a single UPDATE ... WHERE predicate?
• Is conflict rare enough that a version-check failure is acceptable?
• If a conflict happens, can the operation safely retry after re-reading current state?
• If a row is locked, how long can the transaction wait before user experience or connection capacity suffers?
• Does the transaction call any external service while locks are held?
• Do all code paths acquire multiple locks in the same order?
• Is there a database constraint that can enforce the rule more reliably than application code?
• Which metric will show that contention is increasing?

The practical default is:

1. prefer a guarded single-statement write when it expresses the invariant cleanly
2. use optimistic locking when conflicts are rare and recoverable
3. use pessimistic locking for short critical sections under expected contention
4. keep external side effects outside the locked transaction
5. test the overlapping-request path, not only the happy path

Closing Thought

Optimistic locking and pessimistic locking are not competing slogans.

They are two ways of deciding how a system should experience contention.

Optimistic locking says: let requests proceed, then reject the write if the row changed. Pessimistic locking says: make one transaction own the row before it decides. Guarded updates say: let the write itself prove the business condition is still true.

The best SQL locking strategy is the one that makes the invariant explicit, keeps transactions short, and gives the application a clear outcome when concurrency happens.