Two People Change the Same Record

22.1.1 PLAIN — in simple words#

1. Two people do not need to press a button at exactly the same instant to interfere with one another. It is enough for one person’s work to happen between another person’s reading and writing.
2. A request that appears to be one action on a screen may contain several separate steps: read a value, decide what to do, calculate a result, write it, and confirm completion.
3. The important question is which of those steps may overlap and what the database promises about the overlap. Counting buttons or adding a loading spinner does not answer it.

22.1.2 PLAIN — a picture in your head#

1. Mira reads the number on a stock card, walks away to calculate, and comes back to write her answer. While she is away, Dev reads the same card and begins his own calculation.
2. Each person remembers a different moment, even though both later write on the same card. The card cannot reconstruct the missing conversation by itself.
3. Where the comparison breaks: a database may block, reject or reorder operations according to its concurrency controls. A handwritten schedule describes a proposed sequence; it is not evidence that every database permits that sequence.

22.1.3 PLAIN — a worked example#

1. Name the requests A and B and split each into read, calculate and write. One possible schedule is A-read, B-read, A-calculate, A-write, B-calculate, B-write.
2. Another is A-read, A-calculate, A-write, B-read, B-calculate, B-write. The second completes A before B reads; it may therefore give B different input.
3. Before claiming an anomaly, state the initial records, each statement, the transaction boundaries, the isolation level, the connection arrangement and the observed results. A drawing with none of these details is a hypothesis to investigate.
4. In a pure model we can deliberately enumerate schedules. In an engine experiment we must record which actions actually succeeded, waited or failed.

22.1.4 PLAIN — what is really happening inside#

1. The server and operating system schedule work independently of the order in which people remember clicking. Network delays can also change arrival and response order.
2. A connection may hold one transaction while another connection executes its own. A pool is not a guarantee that related statements use the same connection or transaction unless the application arranges that explicitly.
3. Correctness reasoning follows dependencies: this write used that earlier observation; this decision required that predicate to remain protected. Wall-clock timestamps alone often cannot establish all such relationships.

22.1.5 TECHNICAL — the engineer’s version#

1. An interleaving is an ordering of elementary operations consistent with the order required inside each participating process or transaction. A schedule model should state which operations are atomic in that model.
2. Engine isolation constrains the legal schedules and visible versions. PostgreSQL 17 and SQLite do not offer identical execution models; a PostgreSQL snapshot example should not be advertised as a reproduced SQLite anomaly. [S66] [S54]
3. Use deterministic barriers or explicit two-connection coordination for a controlled experiment where feasible. A sleep can encourage overlap but does not prove the intended ordering. Retain a step log and transaction outcome for every participant.

22.1.6 WORDS — remember these#

1. Interleaving: the steps of different requests mixed together — an operation ordering that preserves each participant’s required local order. Race condition: an answer that depends on an unsafe overlap — behaviour whose correctness changes with the relative execution order of competing operations. Schedule evidence: the recorded sequence that actually ran — observations of operations, waits, errors and outcomes under a stated concurrency configuration.

22.2.1 PLAIN — in simple words#

1. A lost update happens when a later write accidentally replaces the effect of another change. Both people may receive a success message even though the final record represents only part of their work.
2. A common cause is calculating a replacement value from an old read. The replacement says “set stock to six,” not “subtract the four units this request reserved.”
3. The database cannot always infer the intention behind a supplied number. Six might be a deliberate correction, an obsolete calculation or an entirely wrong input. The application must express the intended operation and its conditions.

22.2.2 PLAIN — a picture in your head#

1. Two editors download the same paragraph. One corrects a spelling error; the other adds a sentence. Uploading the second whole paragraph can restore the spelling error by replacing the first editor’s version.
2. The second upload is not necessarily larger or later in meaning. It is merely the last replacement accepted under that editing protocol.
3. Where the comparison breaks: text-merging tools sometimes combine edits automatically. A stock counter needs a precisely defined numeric rule; an automatic text-style merge is not an inventory policy.

22.2.3 PLAIN — a worked example#

1. A new model begins with RACE-STOCK equal to 10. Request A wants 3 units and request B wants 4. Both read 10 before either writes.
2. A calculates 10 − 3 = 7. B independently calculates 10 − 4 = 6. A writes 7, then B writes 6.
3. The final value is 6, but accepting both reservations should have left 10 − 3 − 4 = 3. A’s three-unit subtraction was overwritten. The error is not that subtraction was calculated incorrectly; it was calculated from an observation that was no longer safe to replace.
4. This deliberately unsafe read-then-replace model does not establish that a particular transaction configuration permits both writes. Some engines or isolation levels would reject or serialize relevant operations instead.

22.2.4 PLAIN — what is really happening inside#

1. An UPDATE can faithfully store the literal value supplied to it. Atomic execution of that one UPDATE does not retroactively protect a separate earlier SELECT.
2. Beginning a transaction around the SELECT and UPDATE is important but does not, by itself, specify sufficient isolation for every read-dependent rule. The chosen engine and isolation level determine the remaining risks.
3. Different intentions require different controls. An increment can often be expressed as a relative update. Replacing a person’s edited document may require a version check and a conflict response rather than silently adding or merging values.

22.2.5 TECHNICAL — the engineer’s version#

1. Distinguish SET available = :old_value - :quantity from SET available = available - :quantity. The first binds a client-derived replacement; the second asks the engine to derive the new value from the row it updates.
2. In PostgreSQL 17 Read Committed, a competing updater may wait and then re-evaluate its WHERE condition against an updated row. This supports useful single-row guarded-update patterns, not an unrestricted proof for arbitrarily complex cross-row rules. [S66]
3. A version-based compare-and-set is another strategy: require the expected revision and advance it with the write. Chapter 25 develops that approach and its limits, including deletion and recreation of an identity.

22.2.6 WORDS — remember these#

1. Lost update: one accepted change is overwritten by another — a write anomaly in which a later write fails to preserve an effect that should remain represented. Relative update: describe the change rather than an old replacement — an expression such as value = value - amount evaluated by the database. Stale read: an observation that no longer describes the relevant current state — a previously read version that may be unsafe as an unconditional write basis.

22.3.1 PLAIN — in simple words#

1. A conditional write asks the database to make a change only while a stated condition is satisfied. For stock, the condition may be “this product has at least the requested quantity.”
2. The condition and the change must belong to the protected operation. Checking stock in one request and changing it later without protection leaves the gap open.
3. A statement affecting no rows needs interpretation. It may mean insufficient stock, a missing product, a stale version or a forbidden scope, depending on the statement and application contract. It is not automatically a database malfunction.

22.3.2 PLAIN — a picture in your head#

1. A ticket dispenser checks whether tickets remain and releases one through the same controlled mechanism. It does not hand you a note saying that a ticket existed several seconds ago.
2. Combining the check with the action prevents another buyer from treating the same old observation as a separate promise.
3. Where the comparison breaks: a database condition concerns stored records. The record may still disagree with damaged, missing or uncounted physical goods. Concurrency control does not replace stocktaking.

22.3.3 PLAIN — a worked example#

1. In a separate RES-PEN exercise there are 5 available units. A asks for 3 and B asks for 3. Both cannot be accepted while available stock stays non-negative.
2. The guarded operation subtracts 3 only when at least 3 remain. One successful update leaves 2. A later evaluated condition sees that 2 is not enough and affects zero rows.

UPDATE reservation_stock
SET available = available - ?
WHERE product_id = ? AND available >= ?;

3. Bind (3, 'RES-PEN', 3) after validating that quantity is a positive bounded integer. Accept the stock step only if exactly one row was affected under the helper’s stated schema and driver behaviour.
4. This statement alone does not create the request record, protect an external payment or distinguish a replay. Those responsibilities require the surrounding transaction and request protocol.

22.3.4 PLAIN — what is really happening inside#

1. The engine coordinates the qualifying row and its update. Other relevant operations may wait or receive a conflict instead of both acting on the same old value.
2. Keep the product’s complete identity in the predicate. Once branches or tenants exist, a product code alone may match the wrong scope or multiple rows.
3. Separate input validation from database enforcement. Reject zero, negative, boolean-like or excessively large quantities according to the API’s explicit type contract; retain database constraints as another boundary.

22.3.5 TECHNICAL — the engineer’s version#

1. A guarded update protects only the predicate and resources covered by the engine’s concurrency semantics. It does not automatically enforce a total across other rows or across databases. [S66]
2. With SQLite, serialize the owned write transaction deliberately, for example using BEGIN IMMEDIATE in these bounded exercises. Handle contention and inspect transaction state rather than assuming simultaneous independent writers. [S25] [S54]
3. Record the row-count contract for the driver and statement. For the supplied SQLite UPDATE examples, the code checks cursor.rowcount; more complex statements, triggers and different drivers require their own documented interpretation. [S86]

22.3.6 WORDS — remember these#

1. Conditional write: change a record only when its requirement still holds — a write guarded by a predicate evaluated under the database’s concurrency rules. Affected-row check: verify how many target records changed — inspection of the statement result against an expected cardinality. Complete scope: all identity dimensions needed to select the intended record — the tenant, branch and local key components required by the data model.

22.4.1 PLAIN — in simple words#

1. Asking whether a key exists and receiving “no” is an observation, not a reservation of that key. Another request may receive the same answer before either inserts.
2. A uniqueness constraint gives the database an enforceable rule about which keys may coexist. The application must still decide how to handle the losing request.
3. The meaning of the key matters. One key per event, one key per customer and one key per retryable request describe different things. A constraint cannot repair a confused definition of identity.

22.4.2 PLAIN — a picture in your head#

1. Two visitors see an empty seat and each announce that it is theirs. Looking at the seat did not allocate it.
2. A controlled reservation desk can accept one seat assignment and reject the conflicting assignment. The desk must also know whether two messages came from one visitor retrying or from two different visitors.
3. Where the comparison breaks: some database uniqueness rules treat missing values differently from ordinary values. A nullable field is not automatically the same as a required reservation identifier.

22.4.3 PLAIN — a worked example#

1. A and B both execute a lookup for (branch='BR-A', receipt_no=17) and see no row. Both then try to insert that pair.
2. With the required composite unique key, both cannot commit conflicting rows with that key. One may succeed while the other waits, fails or takes an explicitly programmed conflict path.
3. (BR-B,17) is different and may be valid. A global uniqueness constraint on receipt number alone would reject legitimate records from another branch.
4. Receiving a uniqueness error does not prove that the competing record contains the same request payload. Before returning an existing outcome as a replay, check the intended request identity and its recorded meaning.

22.4.4 PLAIN — what is really happening inside#

1. The engine’s constraint mechanism participates in concurrency control; the application does not implement uniqueness merely by querying first.
2. A pre-check can improve a user message, but it cannot replace the final enforcement point. Handle a conflict even when the pre-check passed.
3. Blindly retrying a permanently conflicting insert wastes resources. A new attempt with the same impossible key and unchanged policy is likely to encounter the same problem again.

22.4.5 TECHNICAL — the engineer’s version#

1. Define the unique constraint on the complete non-null identity required by the model. Consult the engine’s treatment of NULL, deferred constraints and conflict clauses rather than assuming universal behaviour. [S74] [S61] [S73]
2. PostgreSQL’s serialization-retry guidance distinguishes transient concurrency situations from persistent uniqueness failures. A uniqueness error is not a universal instruction to retry automatically. [S120]
3. An upsert is a chosen write policy, not proof of semantic equivalence. Replacing an existing payload on conflict may destroy the very evidence needed to identify incompatible uses of a request key.

22.4.6 WORDS — remember these#

1. Check-then-insert race: two requests act on the same observed absence — an unsafe gap between an existence query and an unprotected insertion decision. Composite uniqueness: a combination must be distinct — a constraint enforcing uniqueness over a tuple of columns. Conflict policy: what to do when a rule prevents a write — an explicit decision to reject, reconcile, return an equivalent result or apply a defined update.

22.5.1 PLAIN — in simple words#

1. Some rules depend on more than the row being changed. A branch may allow at most ten active reservations across many request rows. Each individual row can be valid while their total is too large.
2. Protecting one writer’s own row does not necessarily protect the shared total. Two writers can change different rows after reading the same acceptable total.
3. The remedy must match the invariant. It may use a shared guarded capacity record, an appropriate locking protocol or serializable transactions with correct retry handling. Choosing a familiar keyword without tracing the rule is not a design.

22.5.2 PLAIN — a picture in your head#

1. Two staff members each have a locked drawer for booking forms, but both sell space in the same ten-seat room. Locking the drawers does not coordinate the room’s remaining capacity.
2. The scarce thing is shared, even though the pieces of paper are separate.
3. Where the comparison breaks: database locks operate on specific engine resources, not abstract business concepts. A design must identify the actual row, predicate or coordination protocol representing the shared capacity.

22.5.3 PLAIN — a worked example#

1. Revisit CAP-DEMO as a separate rule demonstration: capacity is 10 and allocations of 7 and 6 are individually positive. Their sum is 13, exceeding capacity by 3.
2. A CHECK requiring each allocation to be between 1 and 10 accepts both. It checks each row, not the combined allocation rule.
3. A possible redesign uses a capacity row with available=10. Every allocating path must atomically reduce that same available value under a sufficient-stock condition and record its allocation in the same transaction.
4. The redesign is incomplete if an administrator, import job or cancellation path can bypass the coordination protocol. Enumerate all mutation paths before claiming the invariant is enforced.

22.5.4 PLAIN — what is really happening inside#

1. The read set and write set can differ. A request may read a sum of many rows but write only its new allocation. Another request can have a disjoint write set while depending on the same shared read condition.
2. A lock on a row that does not exist cannot casually be treated as a universal lock on future matching rows. Engines have different predicate and range-locking behaviours.
3. Computing a decision outside the protected transaction and merely repeating the final INSERT inside it can preserve the original unsafe assumption. Recompute or validate the decision at the correct boundary.

22.5.5 TECHNICAL — the engineer’s version#

1. Express the invariant mathematically, such as sum(active quantities for capacity_key) <= capacity. Then specify a protocol that every competing writer follows.
2. PostgreSQL Serializable can detect dependency patterns that threaten serializability, but applications must retry the whole transaction when required. Row-locking alone and snapshot isolation alone do not imply the same guarantee. [S66] [S120]
3. Avoid holding a transaction open across an unbounded external call to preserve a read. Separate durable intent and external execution where possible; Chapter 26 explains the outbox boundary.

22.5.6 WORDS — remember these#

1. Cross-row invariant: a rule about several records together — a correctness condition involving a set of rows rather than one row’s fields. Shared guard record: one coordination point for a shared limit — a record whose guarded mutation serializes changes to the represented resource. Mutation path: a way records can change — an application route, job, import, administrator action or other writer that must respect the invariant.

22.6.1 PLAIN — in simple words#

1. A reservation should have a clear outcome: accepted with a specific quantity, rejected for a stated business reason, or unresolved because the caller has not learned the result.
2. “The connection disappeared” belongs to the third category. It does not prove either acceptance or rejection. The database may have committed before the response was lost.
3. Build the operation in layers: validate the request, coordinate the stock change, record the outcome, commit, and expose a way to resolve retries. Do not let a success message outrun the evidence supporting it.

22.6.2 PLAIN — a picture in your head#

1. The reservation desk records a numbered decision before sending a confirmation. A customer who loses the confirmation can ask about that same decision rather than buying another ticket by accident.
2. A decision number is useful only if the desk keeps its meaning stable and can tell a retry from a new purchase.
3. Where the comparison breaks: database and notification systems may fail separately. Recording a decision does not guarantee that an email was delivered or that the physical item was handed over.

22.6.3 PLAIN — a worked example#

1. Start a new exercise with RES-PEN=5. R-A requests 3; R-B requests 3. An accepted R-A leaves 2 and a matching accepted reservation record. R-B cannot acquire another 3 from that state.
2. Inject a failure after reducing stock but before recording R-A. Rolling back the owned transaction should restore stock to 5 and leave no accepted R-A record.
3. Next allow R-A to commit but pretend its response was lost. Simply submitting another subtraction is unsafe. Chapter 26 records a scoped request identity and stable outcome so the same intent can be resolved without allocating twice.
4. Reconcile the accepted reservation quantities with the stock change in the exercise. This proves agreement within the model, not agreement with an unobserved real shelf.

22.6.4 PLAIN — what is really happening inside#

1. The database transaction supplies a local atomic boundary. The request protocol adds a durable connection between the caller’s intent and the recorded outcome.
2. Failure handling must know whether a transaction is still active, rolled back or committed. Where the client cannot determine that state remotely, it needs reconciliation rather than a guessed status.
3. Cancellation is another business operation, not deletion of inconvenient history. Define whether, when and how it releases stock and whether a repeated cancellation has any additional effect.

22.6.5 TECHNICAL — the engineer’s version#

1. Minimum tests include success, insufficient stock, missing scoped product, invalid quantity, duplicate request identity, failure before commit and reconciliation after a lost response. Add actual coordinated competing-connection tests before claiming an engine-specific concurrency result.
2. The companion code separates deterministic schedule models from real SQLite transactions. A sequential model demonstrates why a protocol is needed; a passing in-memory transaction test is not multi-host or power-loss evidence.
3. Request deduplication requires a stable identifier and an explicit equivalence contract. Atomic recording of that identity, its outcome and the business write is developed in Chapter 26. [S126]

22.6.6 WORDS — remember these#

1. Reservation: a recorded allocation under defined rules — a business state transition that reduces available capacity while linking the allocation to a request. Unknown outcome: the caller lacks reliable completion evidence — a state in which a request may have committed even though its response was not received. Reconciliation: compare related records to resolve disagreement — a procedure that checks quantities, identities and outcomes against stated invariants and evidence.