Stop Two Users Overwriting Each Other's Edits

Two people open the same customer record. One changes the phone number, the other changes the address. Both press save. One change survives, the other vanishes without an error, and nobody finds out until the delivery goes to the wrong place three weeks later.

To stop two users overwriting each other you need the database to refuse the second save, not accept it quietly. This is the lost update problem, and apps built with AI get it wrong by default, because the obvious implementation is correct in every test you would think to write. One user, one save, works perfectly. The bug needs two people and a few seconds of overlap, which no manual test session produces.

Why your app almost certainly has this

Ask an AI to build an edit form and you get this shape:

load record  ->  user edits  ->  UPDATE customers
                                 SET name=?, phone=?, address=?
                                 WHERE id=?

The update writes every field from the form, including the fields the user did not touch. Those fields hold the values loaded when the page opened, which may be minutes old. Saving writes stale data over fresh data, and the database does exactly as instructed.

The second user's save is not rejected. It succeeds. That is what makes this hard to notice: there is no error anywhere, and both users see their own change reflected.

Three fixes to stop two users overwriting each other

1. Update only what changed

The cheapest improvement, and it eliminates most real-world collisions.

Track which fields the user actually edited and include only those in the UPDATE. Now one person changing the phone and another changing the address both succeed, because they touch different columns.

UPDATE customers SET phone = ? WHERE id = ?

This does not fix two people editing the same field, and it does not detect anything. It just stops the common case where edits collide only because the form submits everything. If you do one thing, do this.

2. Optimistic locking

The proper fix for most applications. Add a version column that increments on every write, and make the update conditional on the version you loaded.

ALTER TABLE customers ADD COLUMN version integer NOT NULL DEFAULT 1;

UPDATE customers
SET phone = $1, version = version + 1
WHERE id = $2 AND version = $3;

If another save landed first, the version no longer matches, zero rows update, and your code knows. The critical part is checking the row count:

result = db.execute(update, [phone, id, loaded_version])
if result.rowcount == 0:
    # somebody else saved since you loaded
    raise ConflictError()

An AI-generated version of this will often write the SQL correctly and then not check rowcount, which leaves you exactly where you started with extra columns. Check the row count. It is the whole mechanism.

It is called optimistic because it assumes conflicts are rare and only pays a cost when one happens, which matches how most business applications actually behave.

3. Pessimistic locking

Lock the row when someone starts editing, release it when they finish. Nobody else can edit meanwhile.

This is the right answer when a conflict is genuinely unacceptable, such as money movement or stock allocation, and the wrong answer most other times. Someone opens a record, locks it, then goes to lunch, and you need lock timeouts, lock stealing, and a story for what happens when the browser closes. You have traded a rare silent bug for a common visible annoyance.

What to show the user

Detecting the conflict is half the job. What you do next decides whether the feature is good or merely correct.

The worst option, and the common one, is an error saying "this record was modified, please try again" that discards their work. They retype it, and if the other person is still editing, it happens again.

The minimum acceptable version keeps their input on the page and tells them what happened. Nothing they typed is lost; they just have not saved yet.

The good version shows them what changed underneath them:

Someone else updated this record while you were editing.

  Phone:    you entered 0771 234 567
            now in system 0771 234 999

Keep mine  |  Keep theirs  |  Cancel

Field-level, with both values, and a choice. This is straightforward once you have the version check, because you already have to re-read the row to know it changed. Compare the fields and show the differences.

For fields where two people's edits do not actually conflict, merge them and save without asking. The dialogue should only appear for genuine collisions, which makes it rare enough that people read it.

The parts an AI will skip

If you ask an AI coding agent to add this, three things go missing with some regularity.

The row count check, as above. The SQL is right and nothing reads the result.

The version on the way out. The version has to travel to the client and come back with the save. If the form does not carry it, there is nothing to compare.

Every other write path. The edit form gets the version check. The bulk importer, the API endpoint and the admin screen all keep writing unconditionally, and they are where the damaging overwrites come from, because they write many rows at once. Grep for every UPDATE against the table and make sure each either takes the version or has a stated reason not to.

Related: the fix needs to cover anything that modifies the record, which is also the argument for an audit log. When an overwrite does slip through, the log is how you reconstruct what was lost, and without it the answer is that the old value is simply gone.

Testing it

You cannot find this by clicking around, so write the test explicitly:

1. Load the record as user A, keep the version.

2. Load it as user B, keep the version.

3. Save as B. Should succeed.

4. Save as A with A's original version. Should fail with a conflict.

Four steps, and it is the only reliable way to know the mechanism works. Run it against every write path, not just the form.

Fitting it with everything else

Optimistic locking pairs naturally with the controls you probably want anyway. Soft delete and undo means a bad merge is recoverable rather than final. Idempotency keys solve the adjacent problem of the same write arriving twice, which is a different failure with a similar smell. And role-based permissions reduce how many people can edit a given record at all, which is the cheapest conflict reduction available.

For the broader picture of taking an AI-built app from working to production-ready, the guide to building an app with AI covers the rest of the list.

FAQ

Do I need this if my app has five users?

If two of them ever edit the same records, yes. Five users is enough. The probability is low per edit and approaches certainty over a year.

Can I use a timestamp instead of a version number?

You can, with care. Timestamp precision has to exceed how fast two saves can land, and clock handling needs to be consistent. An integer that increments has neither problem and is less code.

What about a database that handles this automatically?

Some do offer row versioning or conflict detection. Check what yours provides before building it, and check what it does on conflict, since detecting and resolving are different features.

Does this apply to document databases too?

Yes, and the mechanism is usually built in as a document revision or etag. The failure is the same if your code does not check it.

How do I retrofit this to an app already in production?

Add the version column with a default, start writing it on every update, then add the condition once every write path is setting it. Doing it in that order means nothing breaks while you are partway through.