Choose a protection

Start with the rule and the operations that can break it. An isolation-level name alone does not choose an application protocol. These five cases form a reading guide; stock and stale edits are worked below, while the other cases lead to their existing lessons.

Business rule	Start with	Evidence and next decision
Stock must not become negative	A checked single-row change	Nonnegative stock
One request identity must not create two database-local operations	A unique key and a transaction	PostgreSQL, MySQL
A newer edit must not be silently replaced	A checked revision	Stale edits
At least one doctor must remain on call	Coordinate all writers of the cross-row rule	PostgreSQL, MySQL
An order and its effect intent must commit together	A database-local outbox transaction, with separate delivery responsibilities	PostgreSQL, MySQL

Nonnegative stock

Rule: a single product's stored quantity must remain at least zero. A buyer requests a positive integral quantity; success means that quantity was removed, not that a previous SELECT appeared sufficient.

Assumptions and writers: the demonstrations use PostgreSQL 18.6 at READ COMMITTED and MySQL 8.4.11 InnoDB at REPEATABLE READ. Stock starts nonnegative, quantities fit the integer column, and every decrement writer uses the checked protocol. Reject zero, negative, or invalid requests before this operation. Restocking, administrative replacement, deletion, and other writers must preserve the same rule. This is a single-row rule, not a reservation across multiple products.

Protection and transaction boundary: express the decrement and its condition in the same UPDATE. In the demonstrated example two buyers each request four of five units:

UPDATE stock
SET quantity = quantity - 4
WHERE id = 1 AND quantity >= 4;

Each buyer uses a short explicit transaction. If an application also records an order in that transaction, it must commit that record only when the stock change succeeds; the linked stock Scenarios do not execute order creation.

Demonstrated behavior: A changes one row. B's UPDATE waits until A commits, then affects zero rows. B rolls back, and the asserted quantity is 1. See PostgreSQL evidence and MySQL evidence. No separate application balance calculation is written back.

Conflict response: one changed row means this decrement succeeded. Zero rows means the condition was unmet or the product was absent; do not report a purchase as successful. Roll back related work, then inspect current state if the UI must distinguish missing stock from a missing product. Return a controlled unavailable result or ask for a smaller quantity. A deadlock or serialization error is a separate transaction failure, not a zero-row result; follow the engine's error guidance. This fixed schedule does not assert every stronger isolation level or failure path.

Tempting failed repair: read quantity, check it in application code, then send an unconditional decrement or saved replacement. The gap between that check and the write admits a changed row. The two-writer practice and MySQL counterpart show why a saved literal is not a protected decision. An atomic decrement without the quantity condition expresses arithmetic but does not itself express the nonnegative rule.

Entailed guarantee†: with initially nonnegative stock, positive requests, representable arithmetic, and only rule-preserving writers, each successful checked decrement leaves this row nonnegative: it subtracts only when the current target has enough stock. † This is a derivation from the predicate discipline and the single-row update mechanisms, with the InnoDB operation scope. No Transcript proves all schedules or those application assumptions.

Stale edits

Rule: a replacement based on an old document revision must not silently erase a newer committed edit. A user edit is not necessarily an additive operation that the application can recompute automatically.

Assumptions and writers: the demonstrations use PostgreSQL 18.6 at READ COMMITTED and MySQL 8.4.11 InnoDB at REPEATABLE READ. Both editors read version 1 before their save transactions. Every replacement writer checks the loaded version and advances it on success; versions are not reused or reset, including across delete/recreate operations. Administrative writers and imports must participate too. Version overflow and document identity reuse require application policy beyond this schedule.

Protection and transaction boundary: do not keep a database lock open during user think-time. Save in a short transaction, conditioned on the revision originally loaded:

UPDATE documents
SET body = 'B edit', version = version + 1
WHERE id = 1 AND version = 1;

Demonstrated behavior: A first saves A edit as version 2. B's version-1 save affects zero rows. B rolls back and a fresh read still shows A's text/version. PostgreSQL evidence and MySQL evidence assert that database boundary, then demonstrate the failed repair below. They do not implement an editor or automatic merge. On MySQL the successful version increment changes the row, so either matched-row or changed-row reporting distinguishes it from no match.

Conflict response: keep the unsaved draft, roll back related work, and fetch current state. A missing row requires a deletion decision. A newer revision requires the user or application to reconsider, merge, or explicitly authorize replacement before a new checked save. Do not turn zero rows into a successful save. Handle waits and transaction errors separately.

Tempting failed repair: fetch the latest version and automatically resend the old replacement. The failed-repair branches write B's old draft against version 2 and assert B edit, version 3: A's text has been replaced. Raising isolation only around the save does not establish protection for an earlier standalone read. Compare the PostgreSQL practice and MySQL practice.

Limits: a nonparticipating writer defeats the revision protocol. The predicate detects an unmatched revision or missing row, not whether two texts can be safely merged. The additive-deposit retry deliberately rereads and recomputes an increment; it is not a general instruction to retry human edits blindly. External publication and cross-row rules need their own boundary.