ZeeshanAli-0704

Posted on Oct 21

System Design: Optimistic vs Pessimistic Locking

#architecture #backend #database #systemdesign

Optimistic vs Pessimistic Locking

A practical guide to concurrent updates: version checks, database row locks, conflict aware forms, inventory reservations, deadlocks, and choosing the right strategy.

The problem in one minute

Alice and Bob open the same product editing page. Both see the price as USD 50.

Alice changes it to USD 55 and saves.
Bob still sees the old page, changes it to USD 48, and saves.
A normal "replace the price" request can silently overwrite Alice's change.

Both requests were valid. The problem is that Bob made his decision using an old copy of the data.

Optimistic locking says:

"Save my change only if the data has not changed since I read it."

The server checks a version as part of the write. If someone already changed the record, it rejects the stale write instead of silently replacing newer data.

Think of editing a shared paper form. You photocopy revision 7, make changes, and return it. The clerk accepts it only if revision 7 is still current.

Now consider two shoppers trying to buy the last book. A short transaction can instead lock the inventory row before checking and reserving it. That is pessimistic locking.

Think of one key to a storeroom. The next person waits for the key, then inspects the room as it is now.

Strategy	Simple rule	Typical example
Optimistic	"Save only if my version is still current"	A person editing a product price
Pessimistic	"Coordinate access before making this database decision"	Reserving the last item in a short transaction

Both protect decisions made from shared data. They differ in when they coordinate competing work, not in whether correctness matters.

This guide explains optimistic locking first, pessimistic locking second, then compares them using the same shopping app scenarios.

Why Concurrent Updates Lose Data
What Optimistic Locking Actually Means
The Version Check Flow
The Atomic Database Write
Try It With Two Database Sessions
Carry the Version Through HTTP
Frontend Conflict Handling
Retry, Reload, or Merge
Inventory and Atomic Business Conditions
What a Row Version Does Not Protect
Optimistic Locking vs Optimistic UI
What Pessimistic Locking Means
The Last Item Checkout Flow
Transaction and Lock Lifetimes
Try It With Two Locking Sessions
A Short Reservation Transaction
What Readers and Writers Experience
Wait, Fail Fast, or Skip
Deadlocks and Consistent Lock Order
Do Not Hold Locks During Payment
Database Locks vs Other Locks
Comparison and Decision Guide
Production Rules and Common Mistakes
Testing and Observability
Interview Questions and Answers
Summary and Further Reading

The Mermaid diagrams render on GitHub and in Mermaid enabled Markdown previews. SQL examples use PostgreSQL, with Read Committed isolation unless stated otherwise. They are learning examples, not a complete API implementation. Other engines have different lock modes, defaults, and range lock behavior.

Reading shortcuts: start with version checks for stale edit screens, row locks for short database decisions, or the comparison for choosing a design.

1. Why Concurrent Updates Lose Data

What is a lost update?

A lost update happens when one successful write overwrites another person's change without detecting that it used stale data.

Consider an inventory count:

Step	Request A	Request B	Stored stock
1	Reads 10		10
2		Reads 10	10
3	Computes 10 - 1		10
4		Computes 10 - 1	10
5	Writes 9		9
6		Writes 9	9

Two purchases happened, but stock fell by only one. The second write replaced the first result.

The risky pattern is read, calculate in application memory, then write an absolute value without checking what changed.

-- Unsafe when this value was calculated from an earlier, unchecked read.
UPDATE inventory
SET available = 9
WHERE sku = 'BOOK-100';

Why doesn't a transaction automatically fix this?

A transaction provides an all or nothing boundary. It does not automatically encode the rule "the value I read must still be current."

At PostgreSQL Read Committed isolation, placing a normal SELECT and a later absolute value UPDATE inside a transaction can still allow this stale read modify write pattern.

Higher isolation levels can detect additional anomalies, but they have their own behavior and retry requirements. You must choose the concurrency rule explicitly.

2. What Optimistic Locking Actually Means

What is optimistic about it?

The approach assumes conflicts are uncommon enough that it is better to let people read and work independently, then check at save time.

It does not assume conflicts are impossible.

Three steps define the pattern:

Read the data and its version.
Prepare a change without holding a database lock during that preparation.
Write only if the stored version still matches, then advance the version.

Term	Plain language meaning	Product example
Version	Which saved revision you read	Revision 7
Expected version	The revision your edit is based on	"I edited revision 7"
Conditional write	Save only if a condition still holds	`WHERE version = 7`
Conflict	Another change invalidated your expectation	Database is now revision 8
Compare and swap	Compare the current value, then replace atomically if it matches	Check version and save together

Despite the name, optimistic locking is usually validation, not a long lived lock placed when the user opens the page.

The database can still take short lived row locks while executing the write. "Optimistic" does not mean "the database never locks anything."

Why is this useful for web forms?

A person may keep an edit screen open for ten minutes. Holding a database transaction open for those ten minutes would waste resources and block competing writes.

With a version, the browser can keep its draft without holding database resources. The server validates freshness when the user finally saves.

3. The Version Check Flow

Initial product:

{
  "id": "P-100",
  "priceCents": 5000,
  "version": "7"
}

The version is shown as a string because large database counters may exceed JavaScript's safe integer range. Clients can treat it as an opaque token.

sequenceDiagram
    participant A as Alice
    participant API as Product API
    participant DB as Database
    participant B as Bob
    A->>API: Read product
    API-->>A: Price 5000, version 7
    B->>API: Read product
    API-->>B: Price 5000, version 7
    A->>API: Set price 5500, expected version 7
    API->>DB: Update if version equals 7
    DB-->>API: One row updated, version 8
    API-->>A: Saved
    B->>API: Set price 4800, expected version 7
    API->>DB: Update if version equals 7
    DB-->>API: Zero rows updated
    API-->>B: Conflict - current data must be reviewed

The database does not decide whose price is better. It only prevents Bob from accidentally pretending he edited the latest version.

Bob can deliberately apply USD 48 after reviewing revision 8, but that is a new decision based on current data, not a blind retry.

4. The Atomic Database Write

Example table

CREATE TABLE products (
  id text PRIMARY KEY,
  name text NOT NULL,
  price_cents integer NOT NULL CHECK (price_cents >= 0),
  version bigint NOT NULL DEFAULT 1 CHECK (version > 0)
);

INSERT INTO products (id, name, price_cents, version)
VALUES ('P-100', 'System Design Book', 5000, 7);

Safe conditional update

UPDATE products
SET price_cents = 5500,
    version = version + 1
WHERE id = 'P-100'
  AND version = 7
RETURNING id, price_cents, version;

The version comparison, field update, and version increment happen in one database statement.

Result	Meaning
One returned row	This write matched the expected version and succeeded
No returned rows	No row matched both the identifier and expected version
Database error	The write did not succeed normally - handle the actual error

No returned rows does not universally mean a version conflict. The row may have been deleted, may not exist, or may be excluded by authorization predicates. The API must apply its resource visibility policy before exposing details.

Why must the check and write stay together?

This is unsafe:

SELECT version
If version is 7:
    UPDATE products SET price_cents = 5500 WHERE id = 'P-100'

Another writer can change the row between the check and the write. This is a time of check/time of use race.

Put the condition inside the write, not only in application code.

At Read Committed, if two conditional updates target version 7 concurrently, one may wait for the other writer. If the first commits version 8, PostgreSQL rechecks the waiting update's condition against the updated row, and version 7 no longer matches.

What about deletes?

Deletes can carry the same precondition:

DELETE FROM products
WHERE id = 'P-100'
  AND version = 8
RETURNING id;

Otherwise, a stale screen might delete a product that someone else has substantially changed.

5. Try It With Two Database Sessions

Use a disposable local PostgreSQL database. Run the table setup from Section 4 once.

Open two database sessions and read the product in both:

SELECT id, price_cents, version
FROM products
WHERE id = 'P-100';

Both should see price 5000, version 7.

Session A:

UPDATE products
SET price_cents = 5500,
    version = version + 1
WHERE id = 'P-100' AND version = 7
RETURNING price_cents, version;

Expected: one row, price 5500, version 8.

Session B, still using its earlier version:

UPDATE products
SET price_cents = 4800,
    version = version + 1
WHERE id = 'P-100' AND version = 7
RETURNING price_cents, version;

Expected: zero rows. The price remains 5500, version 8.

This first experiment demonstrates a stale edit. To observe actual contention, begin a transaction in A, run its update without committing, and run B's update. B waits. After A commits, B returns zero rows.

If A rolls back instead, version 7 remains current and B can succeed. The deciding fact is the committed state, not which browser clicked first.

6. Carry the Version Through HTTP

The browser needs a way to say which representation it edited.

Two common API contracts are:

Contract	Read response	Write request	Typical conflict response
Explicit application version	JSON includes `version`	JSON includes `expectedVersion`	`409 Conflict`
HTTP precondition	Response includes a strong `ETag`	Request includes `If-Match`	`412 Precondition Failed`

Do not mix these meanings accidentally. A failed If-Match precondition normally produces 412, not an arbitrary success response.

Example with ETag and If-Match

GET /products/P-100

HTTP/1.1 200 OK
ETag: "product-P-100-v7"
Content-Type: application/json

{"id":"P-100","priceCents":5000}

The browser preserves the ETag exactly, including the quotes:

PATCH /products/P-100
If-Match: "product-P-100-v7"
Content-Type: application/json

{"priceCents":5500}

The server maps that precondition to an atomic conditional database update. Merely checking the ETag before an unconditional update has the same race as checking a version separately.

If this API requires a precondition and none is supplied, it can return 428 Precondition Required rather than permit an unprotected write.

ETag details that matter

If-Match uses strong comparison. A weak ETag such as W/"v7" is not suitable for this write precondition pattern.
A strong ETag must change when the selected representation changes. A row version is suitable only when it tracks every field/dependency represented by that ETag.
A client treats the ETag as opaque. The server must correctly handle the HTTP field syntax rather than blindly trusting a string as a database version.
If-Match: * checks that a current representation exists. It does not protect a particular revision from stale replacement.
Cross origin browser clients need appropriate CORS configuration for the If-Match request header and access to the ETag response header.
Authentication, authorization, and payload validation are still required. A version is not permission to edit.

7. Frontend Conflict Handling

Keep three things separate

An edit screen should know:

Base: the data originally loaded.
Draft: the user's unsaved changes.
Latest: current server data fetched after a conflict.

Do not overwrite the draft as soon as a conflict occurs. That would prevent database data loss by causing user input loss instead.

flowchart TD
    A["Load data and ETag"] --> B["User edits a local draft"]
    B --> C["Save with If-Match"]
    C --> D{"Response"}
    D -->|"Saved"| E["Show confirmed result"]
    D -->|"412"| F["Keep draft and load latest data"]
    F --> G["Compare base, draft and latest"]
    G --> H["User resolves overlapping changes"]
    H --> C
    D -->|"Network failure"| I["Outcome unknown - reconcile before retry"]

A small browser request helper

This helper implements an API contract where a successful save returns JSON. It does not decide how your UI displays or merges a conflict.

async function saveProduct(productId, draft, etag) {
  if (typeof etag !== 'string' || etag.length === 0) {
    throw new Error('Load the product version before saving.');
  }

  const response = await fetch(
    `/products/${encodeURIComponent(productId)}`,
    {
      method: 'PATCH',
      headers: {
        'Content-Type': 'application/json',
        'If-Match': etag,
      },
      body: JSON.stringify({ priceCents: draft.priceCents }),
    }
  );

  if (response.status === 412) {
    return { status: 'conflict' };
  }

  if (!response.ok) {
    throw new Error(`Product save failed with HTTP ${response.status}.`);
  }

  return {
    status: 'saved',
    product: await response.json(),
    etag: response.headers.get('ETag'),
  };
}

The caller must preserve the draft on conflict, load the latest authorized representation, and show a comparison. A network rejection is propagated so the UI can show an unknown outcome, not silently label it a conflict.

An absent ETag on success means this helper has no validator for the next edit. Re fetch before another protected save, or enforce an API response contract that always supplies a fresh validator.

For successful PATCH responses, return a strong ETag only when it correctly identifies the resulting representation. For a transforming PUT, HTTP imposes additional restrictions on returning validators; a subsequent GET can obtain the current representation and validator.

8. Retry, Reload, or Merge

Why not automatically retry with the newest version?

Because that can turn a detected conflict back into a silent overwrite.

Bob's draft says USD 48 based on revision 7. Replacing its token with revision 8 and resending without review erases the meaning of the check.

Choose based on the operation:

Operation	Sensible response
Human edits a price or legal document	Preserve draft and ask for review
User sets a preference	Explicitly define whether latest user intent should win
System increments a counter	Prefer an atomic increment when that is the business intent
Background recomputation	Re read, recompute, and retry within a bounded budget
Collaborative text editing	Consider OT/CRDT rather than repeated whole document conflicts

A three way merge example

Field	Base revision 7	Alice's saved revision 8	Bob's draft
Name	Book	Advanced Book	Book
Price	5000	5000	4800

Alice changed the name; Bob changed the price. A three way comparison can propose combining both changes.

If both changed the price differently, that is an overlapping conflict requiring an explicit rule or user choice.

After a merge, submit against the latest version. Another writer might win before that save too, so even a merged write still needs a precondition.

A timeout is not a version conflict

The server might have committed a save whose response was lost. A retry with the original version can then fail because your own first request advanced the version.

Reconcile with current state or an operation status endpoint. For commands with side effects, use an idempotency key in addition to concurrency control.

9. Inventory and Atomic Business Conditions

Not every safe write needs a client read version.

If the business operation is "reserve one item if stock exists," express that condition directly:

UPDATE inventory
SET available = available - 1
WHERE sku = 'BOOK-100'
  AND available >= 1
RETURNING sku, available;

The database checks stock and decrements it in one statement. If two requests compete for the last item, only one can consume it under these semantics.

This is a conditional atomic update, not necessarily a version based stale edit check. It protects a stock invariant rather than detecting whether a person saw an old screen.

flowchart TD
    A["What is the operation?"] --> B{"Replace data based on an earlier read?"}
    B -->|"Yes"| C["Require the expected version"]
    B -->|"No"| D{"Simple atomic business condition?"}
    D -->|"Yes"| E["Put the condition in the UPDATE"]
    D -->|"No"| F["Consider a transaction and appropriate coordination"]

If the inventory row also participates in a versioning protocol, advance its version on this path too. All writers must agree on how changes become visible to version based clients.

Creating an order and consuming inventory may require a transaction. A guarded decrement alone does not make the entire checkout workflow atomic or idempotent.

10. What a Row Version Does Not Protect

Multi row invariants

Suppose two doctors are on call. The rule is at least one doctor must remain on call.

Transaction A reads that Bob is on call, then takes Alice off call.
Transaction B reads that Alice is on call, then takes Bob off call.
Each updates a different row with the correct version.

Both row version checks can succeed while the combined rule fails. This is write skew.

A version check on a row protects that row's expected revision, not arbitrary facts read from other rows.

Possible designs include:

Serializable transactions with whole transaction retries.
A shared coordination row that every relevant transaction updates or locks.
Locking the complete relevant set in a consistent order.
A schema or constraint that directly enforces the invariant where possible.

Other boundaries

A version check is not a uniqueness constraint.
It does not reserve stock for the next fifteen minutes.
It does not deduplicate repeated payments.
A cache only version check does not protect the authoritative database write.
A check against a stale read replica cannot replace the primary's atomic condition.

Missing rows and predicates need their own protection

In PostgreSQL, a SELECT ... FOR UPDATE that returns no rows does not row lock a future record.

Checking that username "alice" does not exist and then inserting it is not sufficient. Use a unique constraint to enforce uniqueness.

For more complex absence or range rules, consider an appropriate constraint, a stable coordination row, or Serializable isolation with retries. Locking existing rows alone does not necessarily protect a predicate from concurrent inserts.

Do not generalize this to every engine: gap and range lock behavior varies.

11. Optimistic Locking vs Optimistic UI

These names describe different layers:

Concept	What it does	Example
Optimistic locking	Protects server writes from stale assumptions	Reject saving version 7 after version 8 exists
Optimistic UI	Shows a predicted result before server confirmation	Show a new cart item immediately

You can use both. The UI predicts success, but if the server rejects the write, it reconciles or rolls back that prediction while preserving user intent.

Updating React state optimistically does not provide database concurrency control.

12. What Pessimistic Locking Means

What is a lock?

A lock is a database managed coordination rule. It determines which operations may access a protected resource concurrently.

For a read decide write transaction, SELECT ... FOR UPDATE locks the selected rows so competing updates, deletes, and conflicting locking reads must wait or follow the chosen failure policy.

SELECT sku, available
FROM inventory
WHERE sku = 'BOOK-100'
FOR UPDATE;

The lock must stay held while you check the value and perform related changes inside the same transaction. The keyword alone does not protect statements that run after that transaction ends.

Why "pessimistic"?

The design assumes conflicts are important enough to coordinate access before doing the protected work:

Optimistic:
Read -> prepare change -> conditionally write against the expected version

Pessimistic:
Acquire lock -> read protected state -> decide -> write -> end transaction

Term	Plain language meaning
Transaction	A group of database operations that commit or roll back together
Row lock	Coordination over a particular database row
Contention	Multiple operations competing for the same protected resource
Lock wait	Time spent waiting for a conflicting holder to finish
Deadlock	Transactions wait on each other in a cycle
Lock timeout	A limit on how long an operation may wait to acquire a lock

Use it for short, database local decisions where several operations depend on protected current values.

Do not hold a database lock while a person keeps an edit screen open. Human editing is usually a better fit for version checks or an explicit application level editing lease.

13. The Last Item Checkout Flow

A shop has one book left. Alice and Bob click Buy at almost the same time.

If both read "one available" before either updates stock, both can believe they may buy it.

With a protected read and decision:

sequenceDiagram
    participant A as Alice request
    participant DB as Database
    participant B as Bob request
    A->>DB: BEGIN
    A->>DB: SELECT inventory FOR UPDATE
    DB-->>A: Lock acquired, available 1
    B->>DB: BEGIN
    B->>DB: SELECT same inventory FOR UPDATE
    Note over B,DB: Bob waits for the conflicting row lock
    A->>DB: Reserve item and set available to 0
    A->>DB: COMMIT
    DB-->>B: Lock acquired, available 0
    B->>B: Reject purchase as out of stock
    B->>DB: ROLLBACK

Why does Bob need to check again?

His screen may have shown "one left." That screen is not the authority.

The decision must use the row returned after acquiring the lock, not an earlier cache or browser value.

At PostgreSQL Read Committed isolation, a waiting locking read can return the updated row after the competing transaction commits. At Repeatable Read or Serializable, a conflicting change can instead produce a serialization error; handle that isolation level's retry contract.

If Alice rolls back, Bob can see the still available item. A lock itself does not consume inventory; committed data changes do.

14. Transaction and Lock Lifetimes

The essential boundary

flowchart LR
    A["BEGIN"] --> B["Lock row"]
    B --> C["Read and validate"]
    C --> D["Write related changes"]
    D --> E["COMMIT or ROLLBACK"]
    E --> F["Transaction locks released"]

In PostgreSQL, transaction level row locks are normally released when the transaction commits or rolls back. Locks acquired after a savepoint can also be released by rolling back to that savepoint.

Why autocommit can break your intended design

If the locking SELECT runs as an independent autocommit statement, its transaction ends as soon as that statement finishes. The lock is gone before your later statement makes the decision.

This is not enough:

Autocommit SELECT FOR UPDATE
Application calculates result
Another independent UPDATE

Use one explicit transaction and the same database connection throughout.

A connection pool's general query helper may run separate calls on different connections. Use the driver's transaction/client API instead of assuming consecutive calls share a transaction.

How long should a lock be held?

Only as long as the protected database work needs. Do not include:

User think time.
Payment provider requests or customer authentication.
Email delivery.
An unrelated HTTP call.
Slow CPU work that could happen before acquiring the lock.

Holding a lock longer increases queues for everyone targeting the same resource.

15. Try It With Two Locking Sessions

Use a disposable local PostgreSQL database.

Setup, once

The earlier inventory examples described the mechanism. Create their actual learning table here:

CREATE TABLE inventory (
  sku text PRIMARY KEY,
  available integer NOT NULL CHECK (available >= 0)
);

INSERT INTO inventory (sku, available)
VALUES ('BOOK-100', 1);

Session A: lock but do not commit yet

BEGIN;

SELECT sku, available
FROM inventory
WHERE sku = 'BOOK-100'
FOR UPDATE;

UPDATE inventory
SET available = available - 1
WHERE sku = 'BOOK-100';

Leave the transaction open temporarily for this experiment. Do not do this in production.

Session B: try to acquire the same lock

BEGIN;

SELECT sku, available
FROM inventory
WHERE sku = 'BOOK-100'
FOR UPDATE;

Expected: B waits. It does not immediately return "one available."

Session A: finish

COMMIT;

B should now return available = 0. Its transaction still holds the lock it just acquired, so finish B too:

ROLLBACK;

What did this prove?

A conflicting locking read waits while A holds the row.
After A commits, B sees the updated value under Read Committed.
B must use that value to reject the purchase.
Ending B's transaction releases its lock.

For a rollback variant, restore stock to one outside these transactions, repeat the experiment, and roll A back instead of committing. B should then see one available.

Always close experimental transactions. An abandoned session can keep locks and database resources occupied.

16. A Short Reservation Transaction

A real checkout needs more than a decrement. It needs a durable record explaining who owns the reserved item and when that reservation expires.

After closing both experimental transactions, restore one item and add a reservation table:

UPDATE inventory SET available = 1 WHERE sku = 'BOOK-100';

CREATE TABLE reservations (
  id text PRIMARY KEY,
  sku text NOT NULL REFERENCES inventory(sku),
  quantity integer NOT NULL CHECK (quantity > 0),
  status text NOT NULL CHECK (status IN ('pending', 'confirmed', 'expired')),
  expires_at timestamptz NOT NULL
);

The following is a transaction template. Application branching is explicit in the comments; PostgreSQL does not interpret those comments as control flow.

BEGIN;

SET LOCAL lock_timeout = '500ms';

SELECT sku, available
FROM inventory
WHERE sku = 'BOOK-100'
FOR UPDATE;

-- Application: if no row or available < 1, ROLLBACK and return a domain error.
-- Continue only after that check succeeds.

UPDATE inventory
SET available = available - 1
WHERE sku = 'BOOK-100'
  AND available >= 1
RETURNING available;

-- Application: require exactly one returned row, otherwise ROLLBACK.

INSERT INTO reservations (id, sku, quantity, status, expires_at)
VALUES ('R-100', 'BOOK-100', 1, 'pending', now() + interval '10 minutes');

COMMIT;

If the insert fails, roll back the transaction, restoring the decrement. Do not catch the error and commit only the stock change.

In production, generate identifiers, bind values as SQL parameters, authorize the operation, and deduplicate repeated reservation commands. A primary key prevents duplicate IDs, but does not by itself define a friendly retry protocol.

Why keep the guarded UPDATE if a lock is held?

It states the stock rule at the write boundary and adds defense against an incorrect application branch. The CHECK (available >= 0) is another safeguard.

If all you need is a conditional decrement, one guarded update can suffice without an earlier locking read. Use the explicit lock when additional decisions or dependent writes make the protected read useful.

What happens on errors?

The SQL shows the successful path. Use the driver's transaction mechanism to roll back on every error, release the connection in a finally-style cleanup, and classify failures before choosing a whole transaction retry.

Never return a pooled connection with an open or failed transaction.

17. What Readers and Writers Experience

Pessimistic row locking does not necessarily mean "nobody can read this row."

PostgreSQL uses multi version concurrency control (MVCC). An ordinary SELECT can generally read a committed version while another transaction holds a row lock.

For a row locked with FOR UPDATE:

Competing operation	Typical PostgreSQL behavior
Ordinary `SELECT`	Reads a visible committed version without waiting for that row lock
`UPDATE` of the same row	Waits for the conflicting writer
`DELETE` of the same row	Waits
Another `SELECT ... FOR UPDATE`	Waits unless a non waiting policy is used
Operation on a different row	Not blocked by this row lock alone

"Not blocked by this row lock" does not promise a query can never wait. Table/DDL locks, other resources, and query execution can still cause waits.

PostgreSQL also has weaker row lock modes such as FOR NO KEY UPDATE, FOR SHARE, and FOR KEY SHARE. Choose the mode appropriate to your invariant rather than copying FOR UPDATE everywhere.

Do not assume PostgreSQL row lock rules exactly match another engine's gap, range, or escalation rules.

18. Wait, Fail Fast, or Skip

Option A: wait within a lock budget

BEGIN;
SET LOCAL lock_timeout = '500ms';

SELECT sku, available
FROM inventory
WHERE sku = 'BOOK-100'
FOR UPDATE;

-- Perform the short protected work, then commit.
COMMIT;

lock_timeout limits lock acquisition waiting, not total request duration. Use suitable statement, transaction, and HTTP budgets too.

If it times out, PostgreSQL reports an error and the failed transaction must be rolled back. Do not continue to the update as if the lock were acquired.

Option B: fail immediately with NOWAIT

SELECT sku, available
FROM inventory
WHERE sku = 'BOOK-100'
FOR UPDATE NOWAIT;

If the row cannot be locked immediately, the statement fails. This can support "busy, try shortly" instead of a long spinner.

NOWAIT does not report "out of stock." Lock unavailability and business unavailability are different outcomes.

Option C: skip busy rows with SKIP LOCKED

Useful for a queue, where another worker can handle a different job:

WITH candidate AS (
  SELECT id
  FROM jobs
  WHERE status = 'ready'
  ORDER BY created_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET status = 'running'
FROM candidate
WHERE jobs.id = candidate.id
RETURNING jobs.id;

This example assumes a separate jobs table with id, status, and created_at. The claim is one atomic statement; it is not part of the inventory setup.

SKIP LOCKED deliberately omits busy rows. It is useful for competing queue consumers, not a consistent "show all inventory" query. It does not guarantee strict FIFO ordering or freedom from starvation.

After the claim commits, the lock is released. A crashed worker can leave a job marked running. Real queues need a claim owner, lease/timeout recovery, idempotent processing, and protection against a stale worker completing reassigned work.

19. Deadlocks and Consistent Lock Order

How does a deadlock happen?

Transaction A locks item 1, then requests item 2.
Transaction B locks item 2, then requests item 1.
Each holds the resource the other needs.

flowchart LR
    A["Transaction A holds item 1"] -->|"Waits for item 2"| B["Transaction B holds item 2"]
    B -->|"Waits for item 1"| A

Neither can finish without the other releasing its lock.

PostgreSQL detects the cycle and aborts one transaction. Do not depend on which transaction it chooses.

Use a common acquisition order

For a multi item order:

Collect all item identifiers.
Sort them consistently.
Acquire each lock in that order on the same transaction connection.
Validate the complete order, then write.

Every relevant path must follow the convention, including restocking, reservation expiry, and administrative operations.

This reduces a class of deadlocks; it does not prove the application is deadlock free. Foreign keys, triggers, other tables, and automatic locks can add dependencies.

Retry the whole transaction

After a deadlock or serialization failure, roll back and retry the whole transaction, including reads and decisions, within a bounded budget.

Do not retry just the last SQL statement with assumptions from an aborted transaction.

Retry only when replay is safe. An email or payment already sent outside the database cannot be undone by rolling back.

20. Do Not Hold Locks During Payment

The tempting but dangerous flow

BEGIN
Lock inventory
Call payment provider
Wait for customer authentication
Update order
COMMIT

A payment can take seconds or minutes. Other requests queue behind the lock, connections stay occupied, and a provider timeout creates an ambiguous result.

A local transaction cannot roll back money already charged externally.

Better: persist a reservation, then release the lock

sequenceDiagram
    participant API as Checkout API
    participant DB as Database
    participant PSP as Payment provider
    API->>DB: Begin short transaction
    API->>DB: Lock stock and create expiring reservation
    API->>DB: Commit and release locks
    API->>PSP: Charge using an idempotency key
    PSP-->>API: Payment result
    API->>DB: Begin finalization transaction
    API->>DB: Validate reservation state and record result
    API->>DB: Commit final state

Mechanism	Lifetime	Purpose
Database row lock	Until the short transaction ends	Prevent conflicting changes during a database decision
Business reservation	Until confirmed or expired by application rules	Hold inventory across a longer workflow

Expiry and payment completion can race. Both paths must coordinate on the same reservation state, with conditional transitions and transactional stock changes so inventory is released at most once.

If a late payment succeeds after expiry, use an explicit policy such as re reserving stock or refunding. Do not confirm an expired reservation unconditionally.

Use a transactional outbox or another deliberate recovery mechanism for external effects that must follow a database commit.

What should the frontend show?

The browser sends an authenticated command. It does not own a database row lock.

API outcome	Meaning	UI behavior
Reservation confirmed	Short transaction committed	Continue with the reservation
Out of stock	Protected business check failed	Refresh availability and explain
Busy/lock timeout	Could not obtain access within the budget	Preserve input and offer a bounded retry
Deadlock retry exhausted	Allowed retries did not complete	Show a retryable failure, not purchase success
HTTP/network timeout	Outcome may be unknown	Check status or reuse the same operation identity

An API might map a domain conflict to 409 and temporary service pressure to 503 with Retry-After. Define the contract explicitly; SQL lock errors do not have one universal HTTP status.

Disabling the Buy button prevents duplicate clicks in one screen. Only backend concurrency and idempotency rules protect against another tab, a phone, or a retried request.

21. Database Locks vs Other Locks

Mechanism	Scope	What it does not automatically protect
JavaScript mutex in one server process	Cooperating code in that process	Another server process or direct database writer
Browser Web Lock	Cooperating eligible browser contexts	Another browser, device, or backend instance
Database row lock	Conflicting access to rows in that database	External payments or arbitrary resources outside the transaction
Database advisory lock	Cooperating callers using the same advisory key	Writers that ignore that convention
Distributed lease	Cooperating clients of an external coordinator	Late writes from an expired owner without fencing

A database row lock coordinates requests from many backend instances because the database centrally manages conflicting row operations.

You do not need a separate distributed lock service merely because the API has multiple replicas. Coordinate at the resource that actually owns the invariant.

Conversely, a row lock does not make a multi database or external service workflow atomic.

22. Comparison and Decision Guide

Question	Optimistic	Pessimistic
Main idea	Check freshness at write time	Acquire a lock before the protected read/decision
While preparing an edit	No long lived database lock	Competing protected operations may wait
Typical conflict outcome	Conditional write fails	Wait, fail fast, or time out
Good starting point	Long lived forms, relatively rare conflicts	Short transactions needing exclusive access
Main cost	Conflict handling and repeated work	Waiting, lock management, deadlocks
Can database writes still lock?	Yes	Yes

Neither strategy is universally correct. High contention can make optimistic retries expensive, while a hot pessimistic lock can turn into a throughput bottleneck.

A practical decision guide

flowchart TD
    A["What needs protection?"] --> B{"Long lived human edit?"}
    B -->|"Yes"| C["Version check and conflict aware UI"]
    B -->|"No"| D{"Can one conditional write enforce the rule?"}
    D -->|"Yes"| E["Use an atomic guarded write"]
    D -->|"No"| F{"Short database local read and dependent writes?"}
    F -->|"Yes"| G["Consider row locks and a transaction"]
    F -->|"No"| H["Design the wider invariant and recovery protocol"]

The same application can use both:

Product editing forms use versions to reject stale edits.
Checkout uses short row locking transactions for reservations.
Simple counters use atomic updates.
Payment retries use idempotency keys.

These mechanisms solve different parts of correctness. They are not mutually exclusive application wide settings.

23. Production Rules and Common Mistakes

Optimistic rules

Every participating write must advance the version, including admin jobs, imports, and background updates.
Compare and change in one authoritative database operation.
Return explicit conflict results rather than success shaped fallbacks.
Preserve drafts and provide a recovery path.
Bound automated retries and use backoff/jitter when appropriate.
Keep database constraints as the final defense for representable invariants.
Include authorization predicates and enforce permissions independently of versions.
Publish cache invalidations only after the write commits.

Pessimistic rules

Start the transaction before the intended lock.
Keep protected work on one connection and inside that transaction.
Read decision values after locking.
Keep the critical section short and lock only the relevant rows.
Use suitable indexes to find those rows efficiently.
Acquire multiple resources in a consistent order.
Set lock/request budgets and classify failures explicitly.
Roll back on errors before releasing pooled connections.

Why use a counter instead of a timestamp?

A monotonic row counter is simple to reason about. Timestamp precision, repeated values, or application clocks can make a naive timestamp comparison unsafe.

A timestamp or opaque token can work if its generation and comparison guarantee that protected changes get distinct validators. Do not assume any updated_at field automatically has that guarantee.

Common mistakes

Mistake	Why it fails
Check the version, then write without it	Another write can slip between the two operations
Forget to increment the version	Later clients cannot detect the change
Retry a human draft with a fresh token silently	Reintroduces lost updates
Use browser cross tab messages as the authority	Another browser or missed notification bypasses them
Treat every zero row update as an exposed conflict	Can misclassify deletion or leak resource existence
Assume the version prevents duplicate commands	A retry and a stale edit are different problems
Reset versions when reusing an identifier	An old token can accidentally match a new record
Use `FOR UPDATE` in autocommit, then write later	The intended lock is already released
Split transaction steps across pooled connections	They do not share the intended transaction
Hold a lock during payment or user think time	Long queues and ambiguous external effects
Treat `SKIP LOCKED` as a complete result set	Busy rows disappear from the query's view
Assume a row lock protects missing rows	Concurrent inserts can bypass the assumption

Use non reused identifiers or include a generation identifier in the validator to avoid the "old state looks current again" problem.

An HTTP disconnect does not guarantee the backend database transaction was cancelled. A database connection failure also may take time for the server to detect. Define cancellation, timeouts, and cleanup explicitly instead of assuming locks disappear immediately.

24. Testing and Observability

Test the guarantee, not just the happy path

Test	Required outcome
Two clients read version 7 and save different values	One succeeds, the stale one cannot overwrite it
Two truly concurrent writes use version 7	At most one commits against that version
First competing writer rolls back	A later valid writer can still succeed
A background job edits the row	Its change advances the same version protocol
Row is deleted after a client read	No accidental resurrection through the update
A conflict occurs in the browser	Draft remains available for review
Save commits but response is lost	Client reconciles rather than claiming a definite failure
Two rows enforce a shared invariant	Invariant holds under the chosen multi row design
Two purchases target one remaining item	At most one reservation consumes it
A lock holder rolls back	A waiting transaction can see available stock again
A request exceeds the lock budget	Explicit timeout handling and full rollback
Two orders acquire multiple rows in opposite orders	Deadlock handling leaves no partial commits
A worker crashes after claiming a job	Recovery can reassign it safely
Reservation expiry races with payment completion	No double release or invalid confirmation
A duplicate checkout request arrives	One operation does not reserve twice
A locking query matches no rows	Constraints or invariant design still handle inserts

For concurrency tests, use separate database connections and synchronization barriers. Two requests executed one after the other only test stale data, not lock waiting or interleaving.

Useful metrics include conditional write conflict rate, successful saves, bounded retry exhaustion, save latency, and user visible conflict resolution rate. Avoid logging sensitive draft contents.

A high conflict rate may indicate a hot record, an overly broad document version, or an operation that should be expressed as an atomic command rather than whole record replacement.

For row locking workflows, also measure lock wait time, lock timeouts, transaction duration, deadlocks, idle in transaction sessions, pool queue time, and retry exhaustion.

In PostgreSQL, pg_stat_activity, pg_locks, and pg_blocking_pids() help identify holders and waiters. Visibility depends on database privileges; avoid exposing sensitive query text.

For last item concurrency tests, assert final stock and durable reservation count, not only HTTP status codes.

Why does lock duration matter?

If every request needs the same exclusive row for roughly 100 ms, that protected portion is limited to roughly ten completions per second before other overhead.

This is a capacity illustration, not a benchmark. Making that critical section ten times slower can make its queue grow dramatically even if the rest of the service is fast.

25. Interview Questions and Answers

What is optimistic locking in one sentence?

Save only if the record still has the version you read, and advance that version atomically when the save succeeds.

Does it avoid all locks?

No. It avoids holding a long lived lock while preparing a change, but the database can still lock rows during writes.

Why not just check the version in JavaScript?

Another writer can change the record after the JavaScript check. The authoritative write must include the expected version condition.

Should every conflict be retried automatically?

No. Recompute safe machine operations when appropriate, but do not silently overwrite someone else's edit with a stale human draft.

Can two different row updates still violate a rule?

Yes. Row versions do not automatically protect cross row invariants. Write skew needs a broader transaction or coordination design.

Is this the same as an idempotency key?

No. A version detects stale assumptions. An idempotency key identifies repeated attempts of the same command.

What is pessimistic locking in one sentence?

Acquire a database lock before the protected read and decision, keep it through related writes, and release it when the transaction ends.

Does FOR UPDATE block every reader?

Not in PostgreSQL. Ordinary reads generally see a visible committed version; competing writers and conflicting locking reads wait on that row lock.

Is SELECT FOR UPDATE enough by itself?

No. The decision and write must happen inside the same transaction while the lock is still held.

Can row locks deadlock?

Yes. Two transactions can hold different rows and wait for each other's row. Use consistent acquisition order and safe bounded whole transaction retries.

Should checkout hold a stock lock until payment finishes?

No. Commit a durable reservation in a short transaction, then perform payment and finalization using an explicit state/recovery protocol.

Which approach is better?

It depends on the invariant, conflict rate, transaction length, and recovery needs. Prefer a single atomic guarded update when it already expresses the complete rule.

26. Summary and Further Reading

Explain optimistic locking in plain English

I let people edit without holding database locks. Each read carries a version. The server saves only if that version is still current, and advances it in the same operation. If the data changed, I preserve the draft and help the user reconcile instead of silently overwriting newer work.

Explain pessimistic locking in plain English

I use a short transaction to lock rows involved in a database decision. Competing changes wait or follow an explicit non waiting policy. I inspect current protected values, make related changes atomically, and commit quickly. I never keep those locks while a user thinks or a payment provider responds.

Remember:

The problem is stale read modify write decisions.
The core mechanism is an atomic expected version check.
Conflicts are a normal outcome, not an excuse to discard user input.
Atomic business updates, constraints, idempotency, and transaction isolation solve related but different problems.
Row locks protect short database critical sections, not the whole user journey.
Long lived reservations are stored business state, not long held database locks.