Multiple Servers, One Table: Order-Layer Conflict Resolution

At a 1200-cover resort banquet in Antalya Belek, four servers simultaneously add items to the same 20-person round table. Classic "last-write-wins" SQL means the last commit overwrites the others — three servers' 14 combined items silently vanish. Here is how we wired optimistic locking, 409 Conflict, and a retry pattern into production to make this concurrency safe.

Why Last-Write-Wins Fails

The naive UPDATE orders SET items = ? WHERE id = ? commits whatever arrives last. When four POSTs land within 200ms, the database happily overwrites three of them. The guest sees a bill of 6 items when 20 were actually ordered — and the kitchen sees mismatched tickets.

The fix is row-level versioning: every order row has an updated_at or integer version column. Each editor reads the version when opening the order, then sends it back with the write so the server can verify nothing changed underneath.

Optimistic Locking with Versioning

Add two columns to the orders table: version INTEGER DEFAULT 1 and updated_at INTEGER. Your PATCH endpoint then looks like:

• Client sends { items: [...], expected_version: 7 }
• Server SQL: UPDATE orders SET items = ?, version = version + 1, updated_at = ? WHERE id = ? AND version = 7
• Affected rows = 0 means another server got there first — respond 409 Conflict with the fresh state.

409 Conflict + Client-Side Merge

The client never auto-retries. Instead, it shows the latest authoritative order list, highlights what the current server's draft would add or remove, and asks for confirmation. Once approved, it POSTs again with the new version number. At the Belek banquet, this UX cut the perceived conflict rate from 0.4% to 0%, and the team trusted the system after one shift.

Pair this with an append-only order_events log: every add/remove with author, item, timestamp. Conflicts become trivially auditable, and you can run post-mortems without finger-pointing.

FAQ

Why not a pessimistic SELECT FOR UPDATE lock? It serialises servers, hurting throughput at banquet scale. Optimistic locking is far cheaper when actual conflicts are rare.

Does this work on Cloudflare D1? Yes — single-row atomic UPDATE on an INTEGER column does not trigger the JOIN + GROUP BY anti-pattern. It is safe and fast.

What about offline mobile clients? Combine local-first queueing with the expected_version cache. On reconnect, replay the queue; if you hit 409 anywhere, surface the merge UI.

Found this helpful? Share it.