Two support agents open the same customer ticket at 2:14 PM. Ananya changes the priority to High. Rohit, who loaded the ticket a few seconds earlier, adds a note and changes the status to Waiting on Customer. Ananya saves first. Rohit saves second, and because his screen still held the old priority, his save quietly sets it back to Medium. Nobody sees an error. The ticket is now wrong, and nobody knows.
This is the lost update problem, and it shows up anywhere two actors read the same data, change it, and write it back: seat booking, inventory, wallet balances, shared documents, configuration screens. There are two broad strategies for preventing it, and they are built on opposite assumptions about how often conflicts happen.
Pessimistic locking: assume conflict, block early
The pessimistic approach says: before I change this, I will make sure nobody else can. Inside a database transaction, you read the row with a locking read, written as FOR UPDATE in PostgreSQL, MySQL and Oracle. Any other transaction trying to lock or update the same row waits until you commit or roll back. You then make your change and commit, releasing the lock.
Think of a cinema booking system where seat C7 for the 7 PM show is being claimed. The booking transaction locks the seat row, checks that it is free, marks it booked and commits, all within a few milliseconds. A second customer clicking C7 at the same moment waits those few milliseconds, then sees it is taken. Simple and correct.
Its strengths are certainty and simplicity: once you hold the lock, your write will succeed. Its costs appear when locks are held long or taken widely.
- Every waiting transaction holds a database connection and often an application thread. Under heavy contention, a pool of 50 connections can be exhausted by requests queuing for one popular row.
- Transactions that lock rows in different orders can deadlock, and the database will kill one of them.
- Locks cannot sensibly be held while a human thinks. You cannot open a transaction when Rohit opens the ticket and commit when he clicks Save three minutes later. Database locks are tied to a connection and a transaction, and holding them for minutes would block everyone else.
Most databases offer variants that help. NOWAIT fails immediately instead of waiting, which suits “is anyone else editing this?” checks. SKIP LOCKED, available in PostgreSQL and MySQL 8.0 among others, skips rows already locked, which is ideal for job queues where many workers each want any unclaimed job rather than a specific one.
Optimistic locking: assume no conflict, check at the end
The optimistic approach says: conflicts are rare, so let everyone work freely and detect a conflict only when someone tries to save. The common implementation is a version number on each row. When Rohit loads the ticket, he also receives its version, say 12. When he saves, the update is conditional: change these fields and set version to 13, but only where the id matches and the version is still 12.
Replay the story. Ananya and Rohit both load version 12. Ananya saves; her conditional update matches, and the row becomes version 13. Rohit saves; his update looks for version 12, finds none, and affects zero rows. The application sees zero rows updated and knows a conflict happened. Instead of silently overwriting, it can show Rohit the current ticket and ask him to reapply his change, or merge automatically if the fields touched do not overlap.
Frameworks build this in. In JPA, marking a field with @Version makes Hibernate add the version check to every update and throw an optimistic lock exception when no row matches. Entity Framework has concurrency tokens, and many ORMs have equivalents. The same idea works over HTTP: a server returns an ETag with a resource, the client sends it back in an If-Match header on update, and the server replies 412 Precondition Failed if the resource has changed since.
Optimistic locking holds no database locks while users think, works naturally across stateless web requests, and costs almost nothing when conflicts are rare. Its cost is that conflicts become errors the application must handle. When conflicts are frequent, many transactions do their work only to be rejected and retried, which wastes effort and frustrates users.
Choosing between them
| Situation | Better fit | Reason |
|---|---|---|
| User edits a record over several minutes | Optimistic | Locks cannot span human think time |
| Short server-side transaction on a heavily contended row | Pessimistic | Retries would waste more than brief waiting |
| Workers picking jobs from a queue table | Pessimistic with SKIP LOCKED | Each worker takes a different row without waiting |
| Mostly independent edits, occasional overlap | Optimistic | Near-zero overhead in the common case |
| Flash sale on 100 units with thousands of buyers | Neither alone; atomic conditional decrement | One statement that decrements only if stock remains avoids read-then-write entirely |
The last row is worth noticing. The best solution to a lost update is often to remove the read-then-write pattern altogether. “Set stock to stock minus one where stock is above zero” is a single atomic statement; the database handles concurrency internally, and the affected-row count tells you whether the purchase succeeded.
Details that trip people up
Checking the version in code is not enough. Reading the row, comparing versions in Java or Python, and then writing still leaves a gap between the check and the write. The comparison must be part of the update statement itself.
Timestamps make weak versions. Using a last-modified timestamp as the version can fail if two updates land within the clock’s resolution, or if clocks differ across servers. An integer that increments on every write is safer.
Bulk updates can bypass the version. An ORM usually adds the version check only for entity saves. A bulk update query written by hand must increment the version itself, or optimistic checks elsewhere will miss its changes.
Retry with care. Automatically retrying an optimistic failure is fine for server-side logic that recomputes from fresh data. Automatically retrying a user’s edit means reapplying their stale view, which brings back the very lost update you were preventing.
Application-level “soft locks” are a separate tool. A banner saying “Rohit is editing this ticket” is a product feature, usually a row with an owner and an expiry time. It improves the experience but is not a correctness guarantee; it should sit on top of optimistic checks, not replace them.
Written by the RP Strativa Team. The agents, tickets and booking scenarios are illustrative.
Leave a Reply