A Cloudflare D1 notes API reads a note with first(), then awaits an UPDATE, and concurrent edits silently overwrite each other; why, and how do you fix it with D1's API?
answer
- two awaits, two commits
- nothing runs inside a batch
- the condition belongs in WHERE
- meta.changes says who won
basics
~20 sEach awaited D1 query commits on its own, so another request can write between the read and the UPDATE. Make the write conditional, WHERE id = ? AND version = ?, and treat meta.changes of 0 as a conflict.
solid answer
~50 sD1 gives a Worker no transaction it can hold across `await`s: the `first()` read and the later `run()` are two separately committed statements, and other requests' writes can commit between them, so the second save overwrites the first without an error. Putting both in `env.DB.batch()` does not help, because the decision is made in JavaScript and a batch runs no code between its statements. The fix is a conditional write: `UPDATE notes SET body = ?1, version = version + 1 WHERE id = ?2 AND version = ?3`, bound with the version the client last read. That one statement is atomic, and the result's `meta.changes` reports how many rows it changed: 1 means the save won, 0 means someone else changed or deleted the note first, so the Worker answers 409 and the client reloads.
code
javascript · 16 linesexport default {
async fetch(request, env) {
const id = Number(new URL(request.url).searchParams.get("id"));
const { body, version } = await request.json();
const { meta } = await env.DB
.prepare("UPDATE notes SET body = ?1, version = version + 1 WHERE id = ?2 AND version = ?3")
.bind(body, id, version)
.run();
if (meta.changes === 0) {
return new Response("Note changed or deleted since you loaded it", { status: 409 });
}
return Response.json({ id, version: version + 1 });
},
};go deeper
Know that each awaited D1 query commits by itself and that an UPDATE reports the number of rows it changed in meta.changes.
Explain why two requests can interleave between a read and a write, and why batch() cannot run the check in JavaScript.
Diagnose the lost update from its symptoms, fix it with a version-conditioned UPDATE and a 409 when meta.changes is 0, and spot the pattern in code review.
Decide where conflict rules live, in SQL conditions and constraints or in a coordinating layer, and make conflict responses part of the API contract.
## Two calls, two commits A typical edit handler in a notes API reads, decides and writes: 1. `SELECT body, version FROM notes WHERE id = ?` with `first()`. 2. Check permissions and build the new body in JavaScript. 3. `UPDATE notes SET body = ? WHERE id = ?` with `run()`. Each awaited call is a separate statement that D1 commits on its own. D1 gives a Worker no transaction it can hold open across `await`s, and an explicit `BEGIN TRANSACTION` from a Worker fails. Between step 1 and step 3, other Worker invocations, in the same location or across the world, run their own queries against the same database, and their writes commit. Two users who both loaded version 1 both pass step 2 and both run step 3, and the second `UPDATE` replaces the first user's edit without any error. This is the **lost update**; in production it shows up as edits that disappear with nothing in the logs. Local testing rarely reveals it. One developer clicking through `wrangler dev` never sends two saves for the same note at the same moment, and every individual query succeeds, so the handler looks correct until real users share notes. The fault is not in any single statement; it is in the gap between two of them. ## Why batch() is not the fix `env.DB.batch()` is atomic, so it is tempting to put the read and the write in one batch. It does not help: - every statement is bound before the call and **no JavaScript runs between them**, so the decision in step 2 cannot use the value read in step 1; - the read's rows come back only after the whole batch, write included, has committed. A batch protects statements that must succeed or fail together. It does not let code inspect data and react inside the transaction. ## The fix: a conditional write Move the check into the write itself, a pattern usually called **optimistic concurrency**: 1. Add an integer `version` column with a default of 1, through a migration. 2. Return the version with every read, and have the client send back the version it edited. 3. Write with `UPDATE notes SET body = ?1, version = version + 1 WHERE id = ?2 AND version = ?3`. 4. Read `meta.changes` from the `D1Result` that `run()` returns. D1 runs each statement inside its own implicit transaction, so the condition and the change happen together. `meta.changes` is the number of rows the statement modified: | `meta.changes` | Meaning | Response | |---|---|---| | 1 | The version still matched; the edit is saved and the version bumped | 200 with the new version | | 0 | Someone else saved first, or the note was deleted | 409, and the client reloads and merges | On a 409 the client has a short, well-defined job: 1. Fetch the note again to get the current body and version. 2. Show the user what changed, or merge automatically when the edits do not overlap. 3. Save again with the new version, which can itself meet a 409 if a third edit landed meanwhile. A zero-row `UPDATE` is not an error: no exception is raised, and `results` is an empty array whether or not the row matched. The outcome is in `meta.changes`, nowhere else. If the client needs the saved row, add `RETURNING` and call `first()`, which then returns `null` on a conflict. ## Other ways to keep the rule in the database - **Constraints** such as `UNIQUE` or `CHECK` turn an invariant into a failing statement, which also rolls back any batch the statement belongs to. - **Conditional inserts** of the form `INSERT ... SELECT ... WHERE NOT EXISTS (...)` let a quota or uniqueness rule run inside one statement. - **Arithmetic in SQL**, such as `SET views = views + 1`, avoids reading a counter into the Worker and writing it back. ## What to check in a review - A handler that reads with `first()` or `all()` and then writes back what it read is a candidate for a lost update. - Every conditional write must look at `meta.changes`; one that ignores it reports success for a save that never happened. - A conflict goes back to the client. D1's automatic retries cover read-only queries, and a blind retry of the stale `UPDATE` in the Worker would overwrite the other edit, which is the bug being fixed.
- Why not resolve the conflict by retrying the UPDATE inside the Worker?Retrying the same stale body overwrites the other user's edit, which is the bug being fixed. A retry is safe only after re-reading the row and re-applying the change, a merge the client usually owns. D1's automatic retries cover read-only queries, so the conflict is the application's to report.
- How do you count note views in D1 without losing increments?Do the arithmetic in SQL: `UPDATE notes SET views = views + 1 WHERE id = ?`. One statement reads and writes the value atomically, so concurrent requests each add one. Reading the count with `first()`, adding in JavaScript and writing it back loses increments for the same reason edits are lost.
Two colleagues each print the shared plan, mark it up and hand it back; whoever returns last silently wipes out the other's changes. Stamping each printout with its revision number, and accepting only a copy that carries the current revision, turns the late one into a visible rejection instead of a silent loss.
- D1 locks the row read by first() until the Worker returns its response.
- Putting the read and the UPDATE in one batch() prevents the lost update.
- An UPDATE that matches no row throws, so the catch block handles conflicts.
- Check results.length after the UPDATE to see whether it matched.
- D1 retries a conflicting write automatically, so conflicts resolve themselves.