Why Two Tabs Silently Overwrote Each Other: Field Notes on Lost Updates, Version Columns, and ETags

Headline: A lost update happens when two clients read the same record, both edit it, and the second save silently overwrites the first. Database transactions do not prevent it when the read and the write happen in separate HTTP requests; optimistic concurrency control does — an integer version column checked in the UPDATE's WHERE clause, exposed to HTTP clients as an ETag and If-Match header, and shown to the user as an explicit conflict instead of silent data loss. I found this one while testing an admin panel I built. I opened the same product in two browser tabs, changed the price in one and saved, then changed the description in the other and saved. The price change was gone — no error, no failed request, no log line. The second form had posted every field back, including the stale price, through the ORM's default last-write-wins update. These are my notes from making concurrent edits fail loudly instead. Key takeaways A lost update is a read-modify-write race across requests. Two clients load version 7 of a record, both save, and the later write silently erases the earlier one. Transactions do not help across HTTP requests. The form's read and the later save are two separate, individually valid transactions — even at SERIALIZABLE isolation. Optimistic concurrency control is one extra predicate. Write with WHERE id = $1 AND version = $2, set version = version + 1, and treat zero affected rows as a conflict. HTTP already has the vocabulary. Send the version as an ETag, require If-Match on writes, and answer 412 Precondition Failed or 428 Precondition Required. A conflict is a UX decision, not a retry. Show the user what changed and let them reload or overwrite deliberately. What is a lost update, and why doesn't a database transaction prevent it? A lost update is a concurrency bug in which two writers base their changes on the same original value and the second write discards the first writer's change. In web apps, the "modify" step of this read-modify-write race usually happens in a person's head, between two HTTP requests. A database transaction cannot prevent a lost update that spans HTTP requests, because no single transaction contains both the read and the write. The edit page runs a SELECT in one short transaction; minutes later the save handler runs an UPDATE in another. To PostgreSQL, two sequential transactions each updated a row, which is not an anomaly. Raising the isolation level to REPEATABLE READ or SERIALIZABLE changes how overlapping transactions behave, and these two never overlapped. Not every write needs protection. When the change can be expressed in SQL, I let the database do the arithmetic atomically and skip the read entirely: -- Atomic update: the database does the arithmetic, no read in application code UPDATE products SET stock = stock - 1 WHERE id = $1 AND stock > 0; Counters, balances, and inventory fit that shape. Human-edited forms do not, because the new value is whatever the person typed based on what they saw. That is where optimistic concurrency control belongs. How do I add a version check with Drizzle or Prisma? Optimistic concurrency control (OCC) is a strategy that lets writers proceed without locks and detects conflicts at write time by checking that the row still has the version the writer originally read. The implementation is an integer version column, defaulting to 1, and one extra condition in the WHERE clause of every update. Here is my Drizzle ORM helper: import { and, eq, sql } from 'drizzle-orm'; import { db } from '@/db'; import { products } from '@/db/schema'; export class ConflictError extends Error {} export async function updateProduct( id: string, expectedVersion: number, input: { name: string; price: number; description: string }, ) { const rows = await db .update(products) .set({ ...input, version: sql`${products.version} + 1` }) .where(and(eq(products.id, id), eq(products.version, expectedVersion))) .returning({ version: products.version }); if (rows.length === 0) { throw new ConflictError(`product ${id} is no longer at version ${expectedVersion}`); } return rows[0].version; } If another write bumped the version first, the WHERE clause matches zero rows and nothing is written. In Prisma, the equivalent is updateMany({ where: { id, version }, data: { ...input, version: { increment: 1 } } }); a returned count of 0 is the conflict. Because zero rows could also mean the record was deleted, I run one follow-up SELECT on a miss to tell a 404 from a conflict. I use an integer rather than updatedAt as the version. PostgreSQL timestamptz stores microseconds while a JavaScript Date holds milliseconds, so a timestamp that round-trips through JSON can stop matching the stored value and produce false conflicts. How do ETag and If-Match map onto a version column? An ETag is an HTTP response header that identifies a specific version of a resource, and If-Match is the request header that makes a write conditional on the resource still having that ETag; both are defined in RFC 9110, HTTP Semantics. The GET handler sends the version as an ETag, the client echoes it in If-Match, and the server answers 412 Precondition Failed when it no longer matches. 428 Precondition Required, defined in RFC 6585, rejects writes that omit If-Match, so no client falls back to last-write-wins by accident. // app/api/products/[id]/route.ts (auth checks omitted for brevity) import { getProduct, updateProduct, productSchema, ConflictError } from '@/lib/products'; type Ctx = { params: Promise }; export async function GET(_req: Request, { params }: Ctx) { const { id } = await params; const product = await getProduct(id); if (!product) return new Response(null, { status: 404 }); return Response.json(product, { headers: { ETag: `"${product.version}"` } }); } export async function PATCH(req: Request, { params }: Ctx) { const { id } = await params; const ifMatch = req.headers.get('if-match'); if (!ifMatch) return new Response('If-Match header required', { status: 428 }); const expected = Number(ifMatch.replace(/^"|"$/g, '')); if (!Number.isInteger(expected)) return new Response(null, { status: 412 }); const input = productSchema.parse(await req.json()); try { const version = await updateProduct(id, expected, input); return new Response(null, { status: 204, headers: { ETag: `"${version}"` } }); } catch (err) { if (err instanceof ConflictError) return new Response(null, { status: 412 }); throw err; } } Two details bit me here. If-Match uses strong comparison, so a weak ETag such as W/"7" never matches — I send a strong "7". And the successful response returns the new ETag, so a client that keeps editing sends the right version next time. How do I pass the version through a Server Action form? A Next.js Server Action invoked from a form cannot set custom request headers, so the version travels as a hidden form field instead of in If-Match. The action runs the same guarded update and returns a typed conflict result to useActionState instead of throwing: 'use server'; import { revalidatePath } from 'next/cache'; import { getProduct, updateProduct, productSchema, ConflictError, type Product } from '@/lib/products'; export type SaveState = { status: 'idle' | 'saved' | 'conflict'; version?: number; current?: Product; }; export async function saveProduct(_prev: SaveState, formData: FormData): Promise { // authentication and authorization checks omitted for brevity const id = String(formData.get('id')); const version = Number(formData.get('version')); const input = productSchema.parse(Object.fromEntries(formData)); try { const next = await updateProduct(id, version, input); revalidatePath(`/products/${id}`); return { status: 'saved', version: next }; } catch (err) { if (err instanceof ConflictError) { return { status: 'conflict', current: (await getProduct(id)) ?? undefined }; } throw err; } } The first bug I hit was a self-conflict: after a successful save, the form still held the version it had loaded, so the next save from the same tab conflicted with itself. Rendering the returned version into the hidden field fixed it: const [state, formAction, pending] = useActionState(saveProduct, { status: 'idle' }); {/* visible fields */} The version field is not a security control: any client can send the current version, which is exactly what an "Overwrite anyway" button does. Authorization still decides who may write the record. What should the user see when a conflict happens? When a save hits a version conflict, the user should see that someone else changed the record, what changed, and a choice between reloading and overwriting on purpose. My conflict panel shows each field whose server value differs from what the user loaded, next to the user's unsaved value; "Overwrite anyway" resubmits with the current version, which turns an accident into a decision. A version conflict must never be retried automatically — a loop that refetches the version and resubmits is last-write-wins with extra round-trips. The one automatic resolution I allow is a field-level merge when the two writers changed disjoint fields, which requires keeping the originally loaded snapshot as a base to diff against. Network retries interact with this too. If a save succeeds but the response is lost, a retry with the same version gets a 412 against its own write. An idempotency key on the save request lets the server replay the original success instead. When should I use optimistic locking instead of SELECT ... FOR UPDATE? Use optimistic locking when user think-time separates the read from the write, and pessimistic locking only when both happen inside one short server-side transaction. SELECT ... FOR UPDATE holds a row lock until the transaction ends, so it cannot span HTTP requests — and holding a transaction open while someone edits a form would block other writers and tie up a pooled connection. Strategy How it works Good fit Trade-off Last-write-wins (ORM default) Blind UPDATE of every submitted field Data one person owns, such as their own settings Silent data loss under concurrent edits Atomic update SET stock = stock - 1 WHERE stock > 0 Counters, balances, inventory Only works when the change is expressible in SQL Optimistic concurrency (version column) WHERE version = $2, then version + 1 Human-edited records across HTTP requests Conflicts reach the user and need a UI Pessimistic lock (SELECT ... FOR UPDATE) Row lock held until the transaction ends Short read-modify-write inside one request or job Cannot span requests; lock waits and deadlocks Edit lease ("someone is editing") Application-level lock with an expiry Long documents with one editor at a time Stale leases and extra state to manage Most CRUD apps end up with a mix: atomic updates for counters, optimistic concurrency for every human-edited form, row locks for a handful of server-side read-modify-write jobs, and last-write-wins only where one person owns the data. FAQ Q: Does a database transaction prevent lost updates? A: Only when the read and the write share one transaction. In web apps the read happens when the form loads and the write in a later request, so the database sees two valid transactions; a version check in the UPDATE's WHERE clause is what detects the race. Q: What HTTP status code should a version conflict return? A: Return 412 Precondition Failed when an If-Match header does not match the current ETag, and 428 Precondition Required when a write omits If-Match. Return 409 Conflict when the version travels in the request body instead of a header, as with a Server Action form. Q: Does optimistic concurrency apply to DELETE requests? A: Yes. Require If-Match on DELETE and delete with WHERE id = $1 AND version = $2, so nobody deletes a record based on a stale view of it. Q: When do I need CRDTs instead of optimistic locking? A: When several people edit the same document simultaneously and expect to see each other's changes live. CRDT libraries such as Yjs and Automerge merge concurrent edits instead of rejecting them; optimistic locking suits forms where simultaneous edits are rare. Originally published on devya.dev. Also on eng-ahmed.com. Built by Devya Solutions.