Scenarios

With 40 ms of application time and round trips, about 300 × 0.04 = 12 operations overlap each window, which is why it happens at all. A single SQL statement taking 40 µs would give 300 × 0.00004 = 0.012, a thousandfold reduction.

Read Committed allows the lost-update shape: one read–write edge plus one write–write edge.

1. SET balance = balance - x, guarded by CHECK (balance >= 0): it commutes, and the rule is a single-row constraint.
2. Otherwise, a version column and a retry loop.
3. Otherwise, SELECT … FOR UPDATE, accepting blocking and the risk of deadlock.

A retry loop needs an owner and a limit; locks must always be taken in the same order.

The commutative update.

Write skew: read a set (the bookings), check a rule over it, write into it. In PostgreSQL, Repeatable Read is snapshot isolation, whose conflict check sees nothing, because the two transactions inserted different rows.

1. Run those transactions at SERIALIZABLE and retry on serialization failure (error 40001). Cost: some false aborts and memory for tracking reads.
2. Make the conflict concrete by updating a single room row in both. Cost: false conflicts, and it silently stops working when a new code path forgets the update.
3. A unique constraint, if the rule can be expressed as one: enforced per row, and free.

Option 1 for a few code paths, since this analysis does not compose across paths. On MySQL's InnoDB the locking behaviour differs, so test there.

Serializability limits which interleavings can happen, not how recent a read is. A read-only transaction may be ordered far in the past, and a replica may serve stale data. Recency is the other axis: single-object consistency. Strict serializability combines the two, and costs real-time coordination.

After a write, route the user's reads to the primary using a token, or use a system that offers external consistency, and measure replication lag.

With 667 operations per second and 2 s of lag, about 1,333 operations sit inside the stale window at any moment.

Serializability does not compose: independently serializable systems put together need not be serializable. Linearizable objects would compose, but transactions across services do not.

Keep the invariant in a single database; add a coordination protocol; or redesign so each invariant lives inside one service and uses commutative operations.

That each service is transactional tells you nothing about the pipeline as a whole.

Restate them as commutative deltas on rows with CHECK (balance >= 0); then any level works.

It needs one consistent snapshot to avoid read skew, so snapshot isolation (or READ ONLY DEFERRABLE serializable). Read Committed would show totals that never existed.

A rule over a set of rows, so snapshot isolation is disqualified (write skew). Use SERIALIZABLE on that path and measure the abort rate, or a shared guard row.

A vendor's name for a level must be tested, for example with a three-transaction test, not trusted.

Each is a new place that can observe the data. The replica lags by 20 ms to 2 s; the cache serves values until their TTL expires. Expected anomalies ≈ rate of conflicting operations × window.

Read-your-writes, through a session token or by routing a user's reads to the primary for a short time after they write. Bound staleness and monitor lag. Keep serializable checks on the primary. Document which screens tolerate stale data.

More load on the primary.

Tokens lost when a request passes through a component that does not forward them.

Any linearizable or sequentially consistent path pays r + w ≥ 80 ms, so a 50 ms read in Singapore cannot be both linearizable and served from Frankfurt.

1. A single leader in Frankfurt for transfers (writes from Singapore take a 160 ms round trip: rare and acceptable), and the balance screen served from a Singapore replica with a stated bound, for example at most 2 s stale, with an alarm.
2. Reads from the leader for the sender after a transfer, using a session token, so senders always see their own transfer.
3. Lease-based local reads, if the product truly needs linearizable local reads, accepting an assumption about clocks that cannot be verified.

At 667 conflicting operations per second and 2 s of lag, about 1,300 operations are inside the window.

The token is dropped at a load balancer (silently stale); the lag exceeds the bound (alarm, and fall back to the primary).

Option 1 plus tokens, and never promise linearizable local reads in Singapore.

"At most 5,000" is an upper bound that sign-ups push towards, so it is not invariant-confluent: coordination is required somewhere.

Escrow. Split the 5,000 into regional quotas, for example 2,500, 1,500 and 1,000 by expected volume. Each region issues bonuses from its own quota with no cross-region call, and asks for more when it falls below a threshold.

Coordination rate ≈ consumption ÷ slack held: a region using 5 per second and holding 1,000 refills about once every 200 seconds.

Stranded slack. During a partition, a region that runs out must refuse bonuses even though another region still has some. Reduce it with small refill blocks and a threshold-triggered refill.

"Count, check, then insert", at any isolation level: it is the write-skew shape again. Use a bounded counter, or a table of tokens decremented transactionally in each region.

• R + W > N guarantees overlap only.
• Last-write-wins makes "latest" depend on clock skew.
• With no write-back after a read, monotonic reads can fail.
• A failed write can reappear later.
• Sloppy quorums let the W acknowledgements come from nodes outside the key's usual replicas, so the overlap argument fails exactly during failures.

Run a list-append or register test with a history checker.

A ledger needs a linearizable store, or a leader with consensus. A feed needs a stated staleness bound and session tokens. Counters can use CRDTs.

LOCAL_QUORUM quietly redefines N as one data centre, and a sequence of calls at mixed levels gets the weakest pairing.

A failure of monotonic reads and read-your-writes, not a concurrency bug. Most likely a recent infrastructure change (a load balancer, a CDN, retries to another region) now sends requests to replicas at different positions in the log, and the client carries no token.

Log the replica ID and its log position for every request.

A token: the commit position, carried in a header or cookie, so the router picks a replica that has applied it or falls back to the primary. Or sticky routing. Prefer the token: sticky routing turns a consistency guarantee into a load-balancing constraint that fights failover and autoscaling.

Name the owner of the token and add the guarantee to the SLO. Expected impact ≈ lag × request rate.

Last-write-wins is acceptable: any recent value will do, and its authority is the user who edited last. Warn that skewed clocks pick the winner, and a lost edit raises no error.

An observed-remove set (OR-Set), where an add wins over a concurrent remove. It costs tags and tombstones, so plan the collector and monitor the oldest uncollected tombstone: one offline replica blocks collection.

A grow-only counter (G-Counter), or a PN-Counter if decrements exist. Its metadata grows with the number of replicas, so avoid giving each client its own replica.

A rule such as "at most 10 collaborators" is an upper bound, not preserved by merging sets, so it needs escrow or coordination.

The choice is made per operation, and only matters during a partition. Transfers and uniqueness checks, which are not invariant-confluent, must be CP: the minority side refuses them. Notes and counters can be AP, using CRDTs.

The latency side of PACELC: p99 on a normal day for each operation, the distribution of replication lag, and how often the staleness bound is exceeded.

"CP" does not mean low availability: the system is unavailable only to the minority side, and only while partitioned. Identify out-of-band channels, such as SMS or phone calls, that need external consistency or a token-based wait.

Treat the vendor's level as a hypothesis to test.

Build a workload whose version order can be recovered from reads, such as appending to lists. Record the history, infer the dependency graph with write–write, write–read and read–write edges, and search it for cycles. Do not search for a valid linearization: that is NP-complete.

A cycle with exactly one read–write (anti-dependency) edge is G-single, read skew, which snapshot isolation forbids. A cycle with two or more is G2, write skew, which snapshot isolation allows.

Which cycle classes appear at which level, with the exact histories.

A clean run does not prove absence; liveness and unobserved operations cannot be checked this way.

1. Try to make the operation commute.
2. Check whether the rule fits inside one row.
3. Check whether the rule is invariant-confluent.
4. If it is not, try to partition the slack (escrow).
5. If the application does read-modify-write itself, rule out Read Committed.
6. If a rule over a set is checked and then written, rule out snapshot isolation.
7. Ask whether any out-of-band channel is faster than replication.
8. Only then choose the model, and write down the staleness bound.

A table: screen, rule, the step that decided it, the choice, and who owns the token.

How many observers, how far apart, and what does each believe?