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.

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.

Table of Contents

1. Why Concurrent Updates Lose Data
2. What Optimistic Locking Actually Means
3. The Version Check Flow
4. The Atomic Database Write
5. Try It With Two Database Sessions
6. Carry the Version Through HTTP
7. Frontend Conflict Handling
8. Retry, Reload, or Merge
9. Inventory and Atomic Business Conditions
10. What a Row Version Does Not Protect
11. Optimistic Locking vs Optimistic UI
12. What Pessimistic Locking Means
13. The Last Item Checkout Flow
14. Transaction and Lock Lifetimes
15. Try It With Two Locking Sessions
16. A Short Reservation Transaction
17. What Readers and Writers Experience
18. Wait, Fail Fast, or Skip
19. Deadlocks and Consistent Lock Order
20. Do Not Hold Locks During Payment
21. Database Locks vs Other Locks
22. Comparison and Decision Guide
23. Production Rules and Common Mistakes
24. Testing and Observability
25. Interview Questions and Answers
26. 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:

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.

Back to Top

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:

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

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.

Back to Top

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.

Back to Top

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.

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.

Back to Top

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.

Back to Top

6. Carry the Version Through HTTP

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

Two common API contracts are:

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.

Back to Top

7. Frontend Conflict Handling

Keep three things separate

An edit screen should know:

1. Base: the data originally loaded.
2. Draft: the user's unsaved changes.
3. 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.

Back to Top

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:

A three way merge example

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.

Back to Top

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.

Back to Top

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.

Back to Top

11. Optimistic Locking vs Optimistic UI

These names describe different layers:

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.

Back to Top

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 

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.

Back to Top

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.

Back to Top

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.

Back to Top

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?

1. A conflicting locking read waits while A holds the row.
2. After A commits, B sees the updated value under Read Committed.
3. B must use that value to reject the purchase.
4. 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.

Back to Top

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 ...