OOmnistu

Blog / Web & Ecommerce

Web & Ecommerce

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.

OOmnistu
Reviewed 2026-11-18 · 5 min read
Both editors read version one; one save wins, one conflicts, leaving version two and one audit row.
Original Omnistu diagram of the article fixture. Scroll horizontally on a small screen. These are synthetic test outcomes, not a live-system certification.

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:

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

Written with AI assistance. The synthetic storage fixture was executed locally; no production editing system or complete HTTP protocol implementation was tested.