Two people edited the same record: last save wins and the other change disappears

Two people open the same order. One fixes the delivery address, the other writes a note for the warehouse. Both hit save, neither sees a warning, and the address change is gone. This is the lost update problem, and nearly every application has it, because the default behaviour is last write wins. The fix is not a longer database transaction. Minutes pass between opening a form and saving it, and no transaction should stay open that long. What you need instead is to carry the version of the record from the moment it was read, and to make the database verify that version is still current at the moment you write. This post covers how that mechanism is built at the SQL, ORM, API and interface layers.

The real constraint is user think time, not locking

Every argument in favour of locking stalls at the same point: the gap between reading the record and the user pressing save. It might be a minute, it might be half an hour. Hold the row locked for that window and one person who opens a form then walks into a meeting blocks the whole team.

The EF Core docs say this plainly on their concurrency page: relying on a higher isolation level is appropriate when all the operations in the transaction execute immediately, but not when the transaction depends on external input that can extend its duration. Querying a record and then waiting for a human to edit it is exactly that case. So the answer comes in two parts. Database transactions stay short, and the gap between them is covered by a version value that travels with the data.

The database default is not protecting you

PostgreSQL defaults to Read Committed. At that level, a second update to the same row waits for the first to finish and then proceeds. The documentation puts it this way: if the first updater commits, the second will attempt to apply its operation to the updated version of the row, and the search condition of the command (the WHERE clause) is re-evaluated to see if the updated version still matches.

The example on the same page shows how surprising that can get. One session runs UPDATE website SET hits = hits + 1 while another runs DELETE FROM website WHERE hits = 10. There is a row with hits = 10 both before and after the update, yet the DELETE removes nothing, because by the time it takes its lock the value is 11.

In your application the thing being overwritten is not a number, it is intent. Run UPDATE orders SET address = 'new address' WHERE id = 5 twice and both succeed, with the second winning. The database reports no error because there is none. This is precisely what you asked it to do.

Raising the isolation level is an option, but not a free one. Under Repeatable Read, PostgreSQL surfaces the conflict as could not serialize access due to concurrent update with SQLSTATE 40001, and the docs also say what to do about it: the application should abort the current transaction and retry the whole transaction from the beginning. Raising isolation therefore means writing retry logic. On MySQL with InnoDB the default is already Repeatable Read, but there locking reads see the most recent committed state while non-locking reads see the snapshot from the start of the transaction. The MySQL manual recommends not mixing the two inside one Repeatable Read transaction for exactly that reason.

Optimistic locking: one counter column and an affected row check

The approach that works most often in practice is optimistic locking. Add an integer version column, read it alongside the record and keep it in a hidden form field, then write like this:

UPDATE orders
   SET address = $1, version = version + 1
 WHERE id = $2 AND version = $3;

The important part is not the SQL, it is the next line of code: checking how many rows were affected. One means the write went through. Zero means somebody got there first and you owe the user a conflict message. Code that skips this check has set up optimistic locking and never used it, which is the most common version of this bug we run into.

Most ORMs give you the mechanism for free. In Rails, adding a lock_version column makes every update increment it, and the second writer gets an ActiveRecord::StaleObjectError. EF Core does the same job with a concurrency token: the token's condition is appended to the UPDATE, and when zero rows are affected it throws DbUpdateConcurrencyException. The docs also name three separate sets of values you need when resolving a conflict: the current values the application wanted to write, the original values it read, and the values now in the database. If you intend to show the user anything more useful than a shrug, you need all three.

Do not use updated_at as your version column

Using updated_at as the version value is tempting because the column is already there. It creates two problems. The first is precision: two updates inside the same second can produce identical timestamps depending on the column's precision, and the conflict goes unnoticed. The second is authorship. If the application server generates the value, a few milliseconds of clock drift between servers is enough to break the comparison.

An integer counter has neither problem and costs one column. Database-generated types such as SQL Server's rowversion are a different story, because there the increment is guaranteed by the database, and EF Core's [Timestamp] attribute maps straight onto that column. Whichever route you take, decide it while you are shaping the data model. Adding a version column to a table that already holds millions of rows also means deciding what the starting value is for all of them.

Where pessimistic locking is the right answer

Optimistic locking does not fit everywhere. When conflicts are likely and the losing side has to redo expensive work, locking the row is the better trade. PostgreSQL gives you SELECT ... FOR UPDATE, and two options make the decision easier: with NOWAIT the statement reports an error rather than waiting if a selected row cannot be locked immediately, and with SKIP LOCKED any rows that cannot be locked immediately are skipped. The second is the one that suits workers pulling jobs from a queue table.

Pessimistic locking has one rule: the lock must not span human think time. Take it, do the work, release it. Otherwise waiting transactions pile up and deadlock risk climbs. PostgreSQL's deadlock_timeout defaults to one second, and that value is how long the server waits on a lock before it bothers checking for a deadlock condition. So when a real deadlock happens, the earliest you hear about it is a second later. Meanwhile the requests queued behind the lock look like a database performance problem, even though the query itself is not slow, it is just waiting its turn.

Replace read, modify, write with a single statement

Sometimes you need no version column at all, because the database can do the arithmetic in one statement. The correct way to decrement stock looks like this:

UPDATE products
   SET stock = stock - 1
 WHERE id = $1 AND stock >= 1;

Again the decision lives in the affected row count: zero means there was not enough stock and the order is refused. Code that reads the stock level, subtracts one in memory and writes the new value cannot make that check, because another request may have decremented the same row in between. The same logic applies to balances, credits, points and every kind of usage counter. A good share of the overselling complaints that show up during a sale trace back to this three-line difference.

Conflicts at the API layer: ETag, If-Match, 412 and 428

The HTTP version of this problem was standardised years ago. The server returns an ETag with the resource, and the client sends that value back in an If-Match header when it updates. Section 13.1.1 of RFC 9110 defines the field for precisely the lost update problem, and when the precondition fails the server responds with 412 Precondition Failed.

What happens when a client forgets to send If-Match is a separate decision. RFC 6585 defines 428 Precondition Required for servers that require requests to be conditional, and describes the typical case as a client that GETs a resource's state, modifies it and PUTs it back while a third party has changed the state on the server in the meantime. The same RFC carries a warning in its security considerations: the 428 status code is optional, and clients cannot rely upon its use to prevent lost update conflicts. In other words 428 is a lever on the server side, not a guarantee on the client side.

Making these headers mandatory is a breaking change for existing integrations, so run it in warning mode for a release first and measure how many clients are not sending the header before you start rejecting them.

A conflict happened, now what does the user see?

Once the plumbing works, the real question remains: what appears on screen when zero rows were affected? Three approaches, and the choice depends on how much work is at stake.

The simplest is to warn the user and reload the page. Correct, and brutal if they spent ten minutes writing a note. The second is to keep what they typed, show both versions side by side and ask which one survives. The third is field-level merging: if two people touched different fields there is no conflict at all, and both writes land.

What makes field-level merging possible is sending only the changed fields rather than the whole form. With a single token covering the entire row, the person editing a shipping note blocks the person editing a billing address for no good reason. That is the rationale the EF Core docs give for application-managed tokens: when you control which column changes regenerate the token, you avoid needless conflicts. Every conflict screen is also an error message design problem. "This record was changed by another user" leaves the user guessing unless it says what changed.

When the data does not live in one database

If a record exists in two systems there is no shared transaction boundary. A stock update from the ERP and an order from the storefront touch the same record at different times, and the messages may not arrive in the order they were produced. Here the version has to travel inside the message rather than next to the record: the version number at the time the event was produced, or the change sequence from the source system. The receiving side drops anything older than what it already holds instead of applying it. Integrations without that rule let a single delayed message restore a price from hours ago.

You will also want an audit trail so conflicts can be investigated afterwards. Without an answer to "who changed this field, when, from what value to what value", you cannot prove a lost update happened, and the discussion comes down to what someone remembers typing.

How to actually test this

Concurrency bugs share one trait: they never appear in single-user tests. Test two separate things.

First, the conflict path itself. Read the same record over two connections, save one, save the other, and assert that the second one fails the way you expect. This test runs fast and fits comfortably in the integration layer of your test suite. Second, the race itself. Fire 100 concurrent requests at the same record and check the counter afterwards. After 100 concurrent decrements the stock should be exactly 100 lower. If it dropped by 97, you have a read, modify, write pattern somewhere, and now you know where to look.

A first step that fits in this week

Search your codebase for one pattern: anywhere that reads a record, changes it in memory and writes it back. It usually looks like a find followed by a save, or an ORM object being loaded and updated. Sort what you find by two questions: does more than one person or more than one service touch this record, and does writing the wrong value cost money.

Everything that answers yes to both gets a version column and an affected row check. Convert counter, balance and stock updates to single-statement SQL. Note which of your public API endpoints return an ETag today.

This usually takes a day or two, and the most valuable part is building the list. Teams often see for the first time which records are genuinely being edited concurrently. If your application already collects complaints along the lines of "I saved it but my change is gone", bring the read and write paths for that screen and the conversation moves a lot faster than an abstract architecture discussion.