Optimistic vs Pessimistic Locking: Preventing Lost Updates with Practical Examples

Two requests read a product's stock level as ten. Each sells one item and saves a new stock level of nine. Both requests succeed, but the database now shows one more item than the business actually owns.

This is a lost update: one operation overwrites another operation's work using a value that was already stale.

Optimistic and pessimistic locking are two ways to coordinate concurrent changes. The right choice depends on the business rule, how often requests collide and what the application should do when they do.

Introduction

Concurrency problems happen when separate requests make decisions about shared state at overlapping times. Each request can look correct when run alone. The defect appears in the combined sequence, which is why ordinary single-user tests often miss it.

Consider two different intentions. A stock operation means "sell one unit if stock remains". A product editor means "replace this description with my revised text, provided I am still editing the version I saw". Both change a row, but preserving their meaning requires different conflict behaviour.

The stock operation can often be expressed as an atomic conditional update. The text edit usually needs to detect that somebody else changed the record and let the user decide what to keep. A transaction that calculates several related changes may instead benefit from locking the relevant state before making its decision.

This article uses PostgreSQL examples and discusses EF Core integration. SQL parameters such as @id represent values bound by the application's data provider; exact parameter syntax and exception codes depend on that provider. Example quantities and identifiers are illustrative rather than measurements from a production system.

The objective is to preserve a specific business invariant, then make conflicts visible and recoverable. "No lost updates" is a useful starting point, but it is not the whole requirement. A system can avoid overwriting one row and still oversell inventory, violate a rule spanning several rows or repeat an external payment during a retry.

Choose the smallest coordination boundary that protects the rule. Keeping unrelated customers or products independent improves concurrency, while trying to coordinate too little leaves a correctness gap. The application also needs a deliberate response when protection rejects an update, because a detected conflict is an expected outcome of the design.

Understand What a Transaction Protects

Wrapping a read and write in a transaction is useful, but the word "transaction" alone does not tell us whether this particular conflict is prevented.

The outcome depends on the database engine, isolation level and statements used. A calculation performed in application memory may rely on a value read before another transaction committed.

Start by writing down the invariant: stock must not become negative, an edit must not silently overwrite a newer edit, or a seat can have only one confirmed owner.

Different invariants can require different solutions. Preventing stale edits to one row does not automatically enforce a rule spanning several rows.

Reproduce the Lost-Update Sequence

The stock example becomes clearer as an interleaving:

Request A reads stock = 10
Request B reads stock = 10
Request A calculates 10 - 1 and writes stock = 9
Request A commits
Request B calculates 10 - 1 and writes stock = 9
Request B commits

The second assignment contains a value calculated from an earlier observation. Even if B waits for A's write lock before applying its own update, the assignment stock = 9 remains stale. Preventing simultaneous physical writes is not the same as detecting stale business intent.

This distinction explains why adding a transaction around the existing application code may be insufficient. The transaction groups work atomically, but its isolation level determines what reads observe and how conflicting changes are handled. The actual statements also matter: stock = 9 and stock = stock - 1 express different operations.

Treat isolation-level names carefully across engines. Implementations differ, and stronger isolation can reject transactions that an application must retry. Verify the behaviour using the selected database and connection settings rather than relying on a generic statement that transactions make code safe.

Define Success in Business Terms

For inventory, success might mean that one accepted order reserves exactly one unit and that available stock never becomes negative. That involves both the inventory change and the order or reservation record. Decrementing stock safely but losing the associated reservation is still incorrect.

For an administrative edit, success might mean applying the user's changes only if the editable representation has not changed since it was loaded. A user should not accidentally erase a colleague's work while believing the save succeeded normally.

For a seat, success might mean that at most one active reservation owns a particular performance and seat. A unique constraint on that identity can enforce an important part of the rule regardless of the application path used. The locking strategy should complement such constraints, not replace every rule with handwritten coordination.

Writing these statements first helps separate correctness from performance. Once the safe outcomes are defined, measure how much waiting, retrying or rejection the workload produces. A faster implementation that permits a forbidden outcome is not an alternative implementation of the same requirement.

Optimistic Locking Detects Conflicts

With optimistic concurrency, the application reads a record together with a version. It later writes only if that version has not changed.

For an illustrative integer version column:

UPDATE products
SET stock = 9, version = version + 1
WHERE id = @id AND version = @original_version;

If another writer changed the version, the statement updates zero rows. The application must interpret that result rather than reporting success.

Every relevant writer needs to participate in the versioning scheme. A maintenance job that updates the data without changing the version can undermine the protection.

This approach avoids holding a database lock while a person edits a form. The database still uses its normal internal concurrency mechanisms during the update.

Use the Version as a Compare-and-Change Condition

Suppose A and B both load version 7. A's update includes version = 7 in its WHERE clause and advances the row to version 8. B's later update still requires version 7, so it cannot silently overwrite the new row. The affected-row count becomes part of the operation's result.

The comparison belongs in the same database statement as the change. Checking the version with a SELECT and then issuing an unconditional UPDATE creates another gap in which a writer can intervene. The database must evaluate the expected version when deciding whether to apply the update.

A version should change whenever the protected representation changes. An incrementing integer is easy to illustrate. SQL Server applications often use a database-generated rowversion, while other providers have different options. A wall-clock timestamp is a poor improvised version if limited precision or inconsistent update behaviour allows two changes to share a value.

Decide the scope of the token. One token for the entire row means an address edit conflicts with an unrelated status change. That can be an appropriate conservative rule. Separate tokens for independent sections can reduce false conflicts, but only if the sections really have independent invariants and every writer respects the distinction.

An application-managed token requires discipline across all writers. Bulk imports, scripts, background jobs and maintenance operations must advance it when they change protected data. A system that protects only its main web form can still lose updates through a less visible path.

Carry the Original Version Through the API

The client needs to return the version it originally read, not a version the server reloads immediately before saving. Reloading the current token and attaching it to stale submitted values removes the evidence that the client edited an older representation.

An API can expose an opaque version field or use an entity tag with a conditional request. For example, a response might identify the representation with ETag: "product-42-v7", and a later update can require If-Match: "product-42-v7". The server maps that precondition to the database concurrency check rather than trusting the string as authorisation.

The transport contract should state what happens if the token is missing or stale. A failed If-Match precondition can produce 412 Precondition Failed, while a body-token API might use a documented conflict result. The HTTP specification's If-Match definition describes this conditional-request mechanism. Whichever approach is chosen, the client needs a machine-readable way to distinguish a conflict from a temporary database outage.

Authorisation remains separate. A caller who knows version 7 is not automatically entitled to update the product. Apply normal ownership and permission checks, and ensure the update predicate cannot affect another tenant's row merely because an identifier was supplied.

Resolve the Conflict Deliberately

After an optimistic conflict, the application has several choices:

• Reject the update and ask the user to reload.
• Show the original, proposed and current values for review.
• Recalculate a safe operation against the latest state.
• Retry a bounded number of times when business semantics allow it.

Blindly repeating a stale assignment is not a meaningful retry. Replacing the current value with the old proposed value can recreate the overwrite we wanted to prevent.

In Entity Framework Core, configured concurrency tokens are included in update conditions, and a conflicting save can raise DbUpdateConcurrencyException. Microsoft's concurrency guidance explains both token configuration and resolution strategies.

Choose behaviour based on intent: editing a delivery address is different from incrementing a counter.

Preserve User Intent During a Merge

A useful edit-conflict screen can compare three representations: the original values the user loaded, their proposed changes and the current database values. That allows the interface to explain which fields changed and preserve the user's work while they decide what to submit again.

For example, Alice changes a product description while Ben changes its category. If the fields are independent and the application has retained the original values, it may be possible to merge them. If both change the description, automatically choosing one text risks silently discarding deliberate work.

Even non-overlapping fields can have a shared rule. Changing currency and changing price independently can create an invalid combination if merged without checking their relationship. A merge is a new business decision evaluated against current state, not simply a mechanical union of changed properties.

Keep rejected input available to the user. Asking them to reload a form and lose several paragraphs of writing technically avoids the lost database update but creates an unnecessary product failure. The conflict response should help the user resolve the situation rather than punish them for normal concurrent activity.

Recalculate Operations That Are Safe to Retry

A stock decrement expresses an operation on the latest state. After a conflict, it may be valid to reread the current stock, revalidate availability and attempt the decrement again. A submitted replacement stock value from an inventory reconciliation screen expresses a different intent and should not automatically be reinterpreted as a decrement.

Bound retries by both count and time. During a hot-product burst, every rejected request can reread and retry together, creating repeated collisions. A small delay with jitter may reduce synchronisation, but sustained contention may call for a conditional update, serialised processing or admission control instead.

Do not retry after every exception. A concurrency conflict, deadlock, permission failure and unavailable database have different meanings. Classify known retryable cases, rerun the complete safe operation and return an explicit result when the budget is exhausted.

Handle EF Core State Explicitly

An application-managed token can be configured as a concurrency token:

modelBuilder.Entity<Product>()
    .Property(product => product.Version)
    .IsConcurrencyToken();

For this integer-token example, application code must also advance Version on relevant changes. Configuration alone does not generate a new integer. A provider-specific automatically generated token has different configuration and should be tested using that provider.

When DbUpdateConcurrencyException occurs, do not immediately call SaveChangesAsync again on unchanged tracked state. The original version is still stale. Either return the conflict, reload and reapply a safe command, or perform an explicit merge with current values and an updated expected version.

The row may also have been deleted. A missing current representation is not an ordinary edit conflict that can always be merged. Decide whether the operation should return not-found, a conflict or another domain result, and avoid recreating deleted content merely because a stale form still exists.

Pessimistic Locking Coordinates Before the Change

Pessimistic locking acquires protection before reading the value used for a decision.

In PostgreSQL, an illustrative transaction is:

BEGIN;
SELECT stock FROM products WHERE id = @id FOR UPDATE;
-- Validate availability and calculate the change.
UPDATE products SET stock = stock - 1 WHERE id = @id;
COMMIT;

The application must reject insufficient stock and execute both statements on the same transaction. Conflicting writers wait or encounter a configured timeout.

This does not mean all readers are blocked. Exact behaviour depends on the lock mode and database's concurrency model; see PostgreSQL's row-lock documentation.

Keep transactions short. Calling a payment provider or waiting for user input while holding the lock turns a slow external operation into contention inside the database.

Make the Protected Decision After Acquiring the Lock

The useful sequence is acquire protection, observe the protected state, validate the rule and apply the change. Reading stock first, performing a calculation, then acquiring a lock only for the final assignment still leaves the calculation based on an old observation.

Under the intended PostgreSQL transaction behaviour, a competing writer cannot change the locked product before the first transaction finishes. The waiting request must then evaluate availability using the state it actually receives, and be prepared for the selected isolation level to report a conflict instead of simply continuing.

Keep the SELECT and UPDATE on the same connection and transaction. If a repository finishes a transaction after the SELECT, its protection is released before the caller performs the update. A method name such as GetForUpdate is not enough unless the surrounding unit of work preserves the lock lifetime.

Handle a missing row explicitly. A row lock protects an existing selected row; finding no product does not automatically reserve an absent identifier against concurrent insertion. Use a unique constraint, an appropriate parent-row lock or another engine-supported mechanism when the invariant concerns absence.

Pessimistic locking can be a good fit when a short database operation must inspect several values and collisions are common enough that repeatedly discarding work would be expensive. It is a poor fit for human editing sessions because the person may leave the page open indefinitely.

Bound Waiting and Keep External Work Outside

Set lock and request deadlines appropriate to the feature. A request should not wait indefinitely behind a stalled transaction. A timeout means the operation did not obtain the required progress within its budget; translate it into a documented retryable or busy response instead of claiming that the requested business change succeeded.

Some queue-processing queries can skip locked rows and do other work. That is useful when any eligible item is acceptable. It is generally unsuitable for a customer trying to buy a specific product if skipping its locked row would be interpreted as proof that the product does not exist or has no stock.

For a payment workflow, reserve inventory through a short local transaction, commit, then contact the provider using a stable operation identity. Follow with confirmation, release or reconciliation according to the provider outcome. A reservation expiry is a business state transition, not a substitute for keeping a database transaction open across the network.

If the client disconnects while a transaction is running, ensure the application disposes or rolls back the transaction appropriately. Still distinguish that cancellation from an uncertain commit outcome. The client may have lost its response after the database committed, which requires idempotency at the command boundary rather than another lock.

Sometimes One Atomic Statement Is Better

For a straightforward stock decrement, express the condition directly:

UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = @id AND stock >= 1;

One affected row means the decrement succeeded. Zero means no eligible row was found, which the application should translate appropriately.

This avoids calculating a replacement value from a stale read. It still participates in database locking and isolation; it is not a lock-free operation.

If creating an order accompanies the decrement, keep both changes in the same transaction. If the rule spans several records, consider constraints, consistent locking or stronger isolation rather than assuming one version column covers everything.

Express the Condition and Change Together

For a positive requested quantity, a PostgreSQL variation can return the accepted result:

UPDATE products
SET stock = stock - @quantity,
    version = version + 1
WHERE id = @id
  AND stock >= @quantity
RETURNING id, stock, version;

Validate that quantity is positive before execution; otherwise a negative quantity would increase stock. A database constraint such as CHECK (stock >= 0) can provide another guard against invalid writes from other paths, though it does not by itself connect a stock change to the correct order.

Two requests selling the final unit can both attempt this statement. The database coordinates their conflicting modifications and evaluates eligibility under its transaction semantics. The accepted update changes current state directly, rather than writing an application-calculated replacement based on a previous SELECT.

Zero returned rows means no row matched the complete condition. That can mean insufficient stock, a missing product or another predicate failure if tenant and status conditions are added. Avoid exposing information the caller is not authorised to know when translating that result into an API response.

Keep order creation in the same local transaction and roll back if it fails. An outbox event can record follow-up fulfilment work in that transaction as well. Atomicity should cover the complete local business transition, not only the arithmetic that first attracted attention.

Protect Rules That Span Several Rows

Imagine a team that must retain at least one active approver. Alice and Ben are both active. Two transactions each count two active approvers, then independently deactivate a different person. Each updates a different row, so per-row version checks can both succeed while leaving no active approver.

This is a different anomaly from two writers overwriting one value. The decision depended on a set of rows, and protecting only the modified row did not protect the shared rule. A version token is useful only for the state included in its coordination boundary.

One design locks a shared team record before evaluating and changing its approvers. All operations affecting that invariant must acquire the same team lock first. This provides a stable coordination point without requiring every reader of ordinary team details to participate in the workflow.

Another design uses serializable transactions and handles the database's serialization failures by retrying the full operation. A constraint may be preferable where the rule can be expressed directly in supported database features. The right choice depends on the invariant, engine and expected contention.

Locking existing child rows alone may not protect a rule against newly inserted qualifying rows. Queries about the absence or count of records need a mechanism that covers those possible changes. Make the scope explicit rather than assuming a SELECT has locked an abstract business predicate in every database.

For a cart containing several products, reserve all required quantities in one transaction when partial success is not allowed. Acquire protection in a consistent product order, validate all quantities and roll back the whole reservation if any product is unavailable. If partial fulfilment is supported, model it as an explicit business outcome.

Account for Contention and Deadlocks

Optimistic conflicts can become expensive when many writers repeatedly target the same record. Pessimistic locking can instead produce long waits.

Where multiple rows are locked, acquire them in a consistent order and handle deadlock failures with bounded retries of the complete transaction.

Stronger isolation may also require retries. PostgreSQL's isolation documentation explains why applications using serializable transactions must be prepared for serialization failures.

Measure conflict rate, transaction duration and lock waits before deciding that one approach is universally faster.

Follow a Deadlock Through Two Transactions

Suppose transaction A locks product 10 and then requests product 20. Transaction B locks product 20 and then requests product 10. Each holds something the other needs, so ordinary waiting cannot make progress.

The database can detect this cycle and abort a participant. The application should recognise the provider's deadlock result, discard the failed transaction and retry the complete operation when it is safe. Retrying only the final UPDATE ignores earlier reads and changes whose transaction was rolled back.

Acquire related resources in a consistent order across code paths, such as ascending product identifier. This reduces an important source of deadlocks, but does not prove they are impossible: indexes, foreign keys and other statements can acquire additional locks. Retain correct failure handling even after establishing an ordering convention.

Keep external effects out of a transaction retry delegate. If an email or payment was sent before a deadlock forced rollback, rerunning the delegate can send it again. Durable outbox processing or the external system's idempotency contract is needed at that separate boundary.

Measure Where the Time Goes

Optimistic concurrency can provide good throughput when conflicts are rare and work is cheap to repeat. As contention rises, discarded reads, calculations and retries consume capacity. Pessimistic locking can avoid some repeated work but produces waiting and can tie up connections if transactions are slow.

Record conflict rate, retries per operation, lock-wait duration, transaction age and final business success rate. Separate expected sold-out responses from infrastructure failures. A protected final unit should produce one success and clear rejections, not an alert merely because every customer could not buy it.

Look for long transactions holding a small but popular resource. A single slow network call inside the transaction can create a queue behind an otherwise inexpensive row update. Removing that external dependency from the lock lifetime can be more effective than changing every update to another concurrency strategy.

For a persistently hot key, consider reducing the number of competing operations through a queue, batching or admission control. Some business decisions necessarily serialize; the architecture should make that limitation predictable instead of allowing unlimited retries to compete for the same row.

Test the Interleavings and Recovery Paths

Use separate database connections and explicit barriers in an integration test so two operations both read the same initial version before either writes. This reliably exercises optimistic conflict behaviour instead of hoping that ordinary parallel execution happens to produce the race.

For pessimistic locking, verify that the second transaction waits or receives the configured failure while the first holds protection. Then confirm it makes its decision against the correct state after the first commits. Run these tests against the actual database engine; an in-memory substitute cannot reproduce its lock and isolation semantics.

Test the final-unit case, missing rows, concurrent deletion, multi-product rollback and a deliberately forced deadlock. Assert final business state, not only exception types. There should be the expected number of reservations, the correct stock balance and no unprotected duplicate follow-up work.

Include maintenance writers and bulk updates in the concurrency review. Confirm they advance tokens or use the required shared coordination rule. Protection that works only in the main endpoint is incomplete when another authorised process can bypass it.

Finally, test uncertain HTTP outcomes separately from concurrent writes. A correctly locked order can still be created twice when the client retries after losing the response. Concurrency control protects simultaneous state transitions; a durable idempotency identity protects repeated attempts at the same intended command. Many practical workflows need both.

Summary

Optimistic locking detects that a record changed; pessimistic locking coordinates access before making a decision. Atomic conditional updates can simplify narrow operations such as decrementing stock.

Choose around the invariant you need to preserve. Then define the conflict response, transaction boundary and retry policy. Concurrency handling is complete only when the application knows what to do after protection prevents an unsafe update.