Stop stale edits from silently replacing newer work
Prevent silent lost updates with an atomic version condition. Executed SQLite races check one winner, audit consistency and rollback after failure.
Two people open the same product description. One corrects the delivery instructions and saves. The other, still looking at the original version, changes the title and saves the entire form. If the second save succeeds without a conflict, the first person's correction can disappear even though both users saw a success message.
Optimistic concurrency control makes the saved version part of the write condition. This guide tests that storage boundary with two SQLite connections, then explains how an HTTP conditional-request contract relates to it. It does not claim to implement a complete HTTP endpoint or test a live Omnistu product.
Include the version that the editor actually read
The example document has an ID, a body and an integer version. Two editors read version 1. Each save supplies its intended new body and expected version 1.
The database compares and advances the version in one statement:
UPDATE documents
SET body = ?, version = version + 1
WHERE id = 1 AND version = ?;
One affected row means the condition matched. Zero means this particular update did not happen. In a general repository, zero might mean a missing resource or a version conflict; distinguish those only as far as the API promises, without revealing a private resource to an unauthorized caller.
Reading the version in application code and later issuing an unconditional update would reintroduce a gap. The test needs to exercise the actual conditional write, not merely compare two integers in a mock repository.
Keep successful side effects inside the same boundary
The fixture adds an audit row only after the conditional update succeeds, in the same transaction. A losing edit must not record an audit event claiming that its contents were saved.
def save(path, expected, body, gate=None, fail=False):
with closing(sqlite3.connect(path, timeout=10)) as db:
if gate:
gate.wait()
with db:
changed = db.execute(
"UPDATE documents SET body=?,version=version+1 "
"WHERE id=1 AND version=?", (body, expected)
).rowcount
if not changed:
return False
db.execute("INSERT INTO audit(body) VALUES (?)", (body,))
if fail:
raise RuntimeError("synthetic audit-boundary failure")
return True
The complete script imports sqlite3 and closing from contextlib, creates the tables and runs the checks. The inner connection context manages commit or rollback; the outer closing explicitly closes each connection. The barrier is a test mechanism used to release both writers after they are ready, not production synchronization.
The audit table is only an example of a local transactional effect. Sending email or calling another service is not made atomic by placing that call between these SQL statements.
Race two stale views and inspect the winner
Each test round starts with body original and version 1. Two independent connections try edit-a and edit-b, both expecting version 1. The script requires exactly one success and one conflict.
It then reads committed state rather than trusting return values:
- The document is at version 2.
- Its body is the winning edit.
- Exactly one audit row exists, with that same winning body.
- Replaying version 1 is refused and does not overwrite the winner.
Next, a save against version 2 intentionally raises an exception after the audit insert. The test requires the body, version and audit count to remain unchanged. Finally, a separate deliberate edit against the freshly read version 2 succeeds and advances the document to version 3.
Twenty rounds passed on Python 3.10.12 and SQLite 3.37.2. They used temporary databases and no HTTP requests. SQLite's single-writer transaction behavior is part of this result. Repeat the same invariant against the actual production database engine and application's transaction settings; this is not a portable concurrency certification.
Connect it to an HTTP contract without confusing the layers
HTTP provides conditional requests for this problem. MDN describes returning an ETag for a resource representation and sending that value in If-Match when a client submits a change. A nonmatching condition normally produces 412 Precondition Failed, rather than applying the stale replacement. If-Match uses strong comparison; a weak tag with a W/ prefix does not match under that rule.
A database version and an HTTP entity tag are related design tools, not automatically interchangeable. The server must generate a validator that correctly identifies the selected representation, validate the request, and connect the precondition to the atomic write. Do not compare an ETag at the start of a handler and later save unconditionally.
An If-Match: * condition checks that a current representation exists; it does not prove that the client edited the latest version. Use the actual validator when the purpose is to detect changes since the editor's read.
This fixture tests the database comparison and transaction, not header parsing, HTTP status handling, content negotiation or ETag generation. Those need endpoint-level tests under the chosen API contract. MDN's header guidance is an entry point; a production HTTP implementation should follow the full conditional-request specification, including its evaluation rules and exceptions.
A conflict needs a usable editing experience
Preserve the user's unsaved text when a conflict occurs. Explain that a newer version exists and offer refresh, compare or a deliberate merge workflow. Do not automatically attach the latest version number and resend the stale full form: that can turn a correctly detected conflict into the same silent overwrite.
Authorization remains independent. A current version number does not grant edit permission. Validate the user's right to read and modify the resource before returning private conflict details or performing the write.
For acceptance, open two real editor sessions in a controlled test environment. Save from one, then submit the stale second form. Verify the conflict response, preserved local draft, stored winner and audit evidence. The outcome should make the rejected edit visible without pretending it was saved.
Sources and method
- MDN If-Match reference: conditional writes, strong comparison, wildcard behavior and precondition failure.
- MDN ETag reference: representation validators and edit collisions.
- Python 3.10 sqlite3 documentation: transaction context management, affected-row counts and connection handling.
- SQLite transaction documentation: concurrent readers, single-writer behavior and transaction boundaries.
Written with AI assistance. The synthetic storage fixture was executed locally; no production editing system or complete HTTP protocol implementation was tested.