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

Back to Top

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:

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

Back to Top

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.

Back to Top

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:

1. Collect all item identifiers.
2. Sort them consistently.
3. Acquire each lock in that order on the same transaction connection.
4. 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.

Back to Top

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

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.

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.

Back to Top

21. Database Locks vs Other Locks

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.

Back to Top

22. Comparison and Decision Guide

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.

Back to Top

23. Production Rules and Common Mistakes

Optimistic rules

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

Pessimistic rules

1. Start the transaction before the intended lock.
2. Keep protected work on one connection and inside that transaction.
3. Read decision values after locking.
4. Keep the critical section short and lock only the relevant rows.
5. Use suitable indexes to find those rows efficiently.
6. Acquire multiple resources in a consistent order.
7. Set lock/request budgets and classify failures explicitly.
8. 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

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.

Back to Top

24. Testing and Observability

Test the guarantee, not just the happy path

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.

Back to Top

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.

Back to Top

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:

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

Further reading

• PostgreSQL: Transaction Isolation - Read Committed rechecks, snapshot behavior, and serialization failures.
• MDN: If-Match - conditional requests and strong comparison.
• HTTP Semantics: If-Match - normative precondition semantics.
• PostgreSQL: Explicit Locking - row lock modes, conflicts, and deadlocks.
• PostgreSQL: SELECT - locking clauses, NOWAIT, and SKIP LOCKED.
• PostgreSQL: Client Connection Defaults - lock and statement timeout settings.
• Idempotency Keys - safe retries after ambiguous outcomes.
• Cross Tab Communication - UI invalidation is not a database concurrency guarantee.

Back to Top

More Details:

Get all articles related to system design
Hashtag: SystemDesignWithZeeshanAli

systemdesignwithzeeshanali

Git: https://github.com/ZeeshanAli-0704/front-end-system-design

⬆ Back to Top