Why Last-Write-Wins Sync Silently Destroys User Data (and What to Use Instead)

Your offline-first app works perfectly. You tested it on the train, on aeroplane mode, on a flaky café connection. Edits made offline sync up cleanly when the device reconnects. Conflicts resolve with last-write-wins, the newest edit wins, which is obviously correct. You ship it.

Three weeks later a user emails: "I spent an hour updating my inventory on my phone and when I opened the laptop it was all back to yesterday's numbers." There is no error in your logs. No crash. No failed request. The sync succeeded. It just silently threw away an hour of their work, and there is no record of it having happened.

Here is the thesis, and it is harsh: last-write-wins keyed on the client's wall clock is not conflict resolution, it is a data-loss mechanism wearing a conflict-resolution costume. It never failed in your tests because your development devices all had accurate clocks. It fails in production the instant a real user's phone clock is wrong, and phone clocks are wrong all the time, by minutes, from a dead battery, a manual timezone fiddle, or a device that just drifted. The "latest" edit is not the one made most recently. It is the one from the device whose clock happened to read the highest number. Those are not the same thing, and the gap between them is where user data goes to die.

Why last-write-wins on a client clock destroys data

Last-write-wins needs to decide, for two edits to the same field, which one to keep. Naive LWW answers "the one with the larger timestamp." The fatal assumption is that the timestamp reflects when the edit happened. It does not. It reflects what the editing device's clock said at that moment, and clocks lie.

Walk through the exact failure. Two devices, one account, editing the same record while offline:

Device A edited at 10:00 real time but its clock reads 40 minutes fast. Device B edited at 10:25 with a correct clock. The genuinely newer edit is B's. But LWW compares 10:40 against 10:25 and keeps A. B's work vanishes. Nothing errored, because from the server's view this is a normal write with a normal timestamp. The loss is undetectable after the fact: you cannot even tell it happened, because the overwritten value is simply gone.

This is why it survives every test you run. Your dev phone's clock is accurate, so timestamp order matches real order, so LWW looks correct. Production is a fleet of strangers' devices with clocks you do not control. The bug is not rare in production; it is guaranteed the moment clock skew and concurrent edits coincide, and at scale they always eventually coincide.

The framework: never let the client's clock decide order

Every durable fix shares one principle: the authority on ordering must be something the server controls, not a value the client supplies. Here are the three real strategies against what they cost and buy you.

Strategy	How ordering is decided	Cost	Use when
Version-number optimistic concurrency	Server increments a per-record version on every write; a write carrying a stale version is rejected.	Low. A column and a conditional update.	Most apps. One record is usually edited by one user at a time, and you want the loser to be told, not silently overwritten.
Per-field LWW with a monotonic server counter	Server assigns a strictly increasing sequence number; highest sequence wins, per field.	Medium. Field-level tracking, but no merge logic.	Field-level data where concurrent edits to different fields should both survive, and true merge is overkill.
CRDTs (conflict-free replicated data types)	Data types mathematically defined so concurrent edits merge and converge regardless of order.	High. Real library, real storage overhead, real learning curve.	Genuinely concurrent multi-device editing (collaborative docs, shared canvases) where no edit may be lost.

Notice what is not on the list: "use the server's clock instead of the client's." That is better than the client clock, but it still loses data when two edits arrive close together, and it cannot order edits that were made offline hours apart. Timestamps are the wrong primitive for ordering. Sequence is the right one. This is the same lesson that shows up when people trust a machine's clock for webhook signatures: wall-clock time is not a reliable coordination primitive across machines.

Worked example: version-based optimistic concurrency

For most CRUD-shaped offline apps this is the right answer and it is not hard. Every record carries an integer version. The client sends the version it based its edit on. The server updates only if that version still matches, and bumps it.

-- The conditional write is the whole trick.
UPDATE items
   SET quantity = :new_quantity,
       version  = version + 1
 WHERE id = :id
   AND version = :base_version;   -- the version the client last saw

If the row's version has moved on since the client read it, WHERE version = :base_version matches nothing, zero rows are updated, and the server knows this edit was based on stale data. Now you have a genuine conflict you can act on, instead of a silent overwrite:

rows = db.execute(update_stmt, params).rowcount
if rows == 0:
    # Someone else wrote first. Do NOT overwrite blindly.
    current = db.execute(select_item, {"id": item_id}).fetchone()
    log.info("sync_conflict", item=item_id,
             base_version=base_version, current_version=current.version)
    return Conflict(server_state=current)   # 409: let the client reconcile

The client, on receiving the conflict, either merges the fields itself, shows the user both versions, or applies a field-level rule. The point is that a human or an explicit rule decides, and the decision is logged. Nothing disappears without a trace. This is the same shape as idempotent write handling, and if your sync also retries, read why background jobs need idempotency, not exactly-once, because a retried "successful" write is its own duplicate-edit hazard.

When different fields are edited concurrently: per-field counters

Version-per-record treats any concurrent edit as a conflict, even when two devices changed unrelated fields. If a user editing the name on their phone should not conflict with editing the price on their laptop, drop the granularity to the field and attach a server-assigned monotonic sequence per field:

item 42
  name  = "Widget"   name_seq  = 1007   (set by server)
  price = 500         price_seq = 1012   (set by server)

An incoming field write wins only if its sequence is higher than the stored one, and each field is resolved independently. Concurrent edits to different fields both survive. The sequence must come from the server, never the client, or you are back to trusting clocks with extra steps.

When no edit may be lost: CRDTs

If two people genuinely type into the same document at the same time and both keystrokes must survive, version numbers are not enough, because there is no single winner that is correct. CRDTs solve this by defining the data so that concurrent operations commute: apply them in any order on any replica and every replica converges to the same state, with no edit dropped. The cost is real. You adopt a library (there are mature ones for text and structured data), you store more metadata, and you think harder about your data model. Do not reach for CRDTs for a settings screen. Reach for them for a collaborative editor.

What people get wrong

"We use UTC, so clocks are fine." UTC fixes timezone confusion, not clock accuracy. A phone set to UTC can still be 40 minutes off real time. The bug is skew from true time, and a timezone is not that.

"We will just use the server timestamp on receipt." Better, but it orders edits by when they reached the server, which for offline-first is arbitrary: the device that reconnected first wins, not the device that edited last. And two writes in the same instant still need a tiebreaker. Server time narrows the window; it does not close it.

"Conflicts are rare, LWW is good enough." Rare and silent is the worst combination, not an acceptable one. A loud, rare failure gets reported and fixed. A silent, rare failure erodes trust invisibly: users slowly learn your app "loses things" and stop relying on it, and you never see a bug report because there is nothing to report. The absence of errors is not the absence of the problem here; it is the symptom.

Resolving conflicts without logging them. Even a correct strategy needs an audit trail. When a user swears they lost data, "no conflicts were logged" versus "here is the conflict, here is what we kept and why" is the difference between a five-minute answer and an unfalsifiable mystery. Log every resolution: record id, both versions, which won, and the rule that decided.

When it is still losing data

• You switched to version numbers but the client ignores the 409. Rejecting the stale write on the server does nothing if the client treats a conflict as success and moves on. The client must catch the conflict, reconcile, and re-submit against the current version. The server-side check is only half the contract.
• The version is generated on the client. If clients assign version numbers, two offline devices generate the same next version and you are back to a clock problem with different clothes. The counter must be the server's.
• Deletes race edits. A delete and an edit are also a conflict. Naive sync often lets a delete win silently, resurfacing later as "my edit vanished." Model deletes as tombstones with their own version, not as the absence of a row.
• Batch sync applies edits in received order. If you sync a queue of offline edits, applying them in the order they were uploaded, not the order they should resolve, re-creates ordering bugs inside a single device's own history. Attach per-edit sequence, not batch position.
• You cannot tell whether it is still happening. If you have no conflict log, add it before anything else. You cannot fix data loss you cannot see, and the entire reason this bug is dangerous is that it hides.

The one sentence to carry away: ordering edits by a clock any client controls is a way of quietly deleting user data. Make the server the authority on order, tell the loser it lost, and write down every decision. The extra column and the conflict log are cheap. The trust you lose when an app silently eats an hour of someone's work is not.