Scenarios

If each transaction lasts W = 5 ms, then L = λW = 8,000 × 0.005 = 40 in flight, against 4 at 800 per second. Pairs of live transactions grow from about 8 to about 800, a hundredfold. And if 90% of buyers want the same 200 seats, the effective number of keys is closer to 200 than 50,000, so the conflict probability is far above a uniform estimate.

Optimistic or snapshot-based schemes pay for conflicts in wasted, retried work; locking would pay in queueing instead.

1. Measure the real abort rate under replayed traffic.
2. Shorten W: move external calls out of the transaction. Its effect on conflicts is quadratic.
3. Reformulate the hot operations so they commute: sub-counters or reservations per section.
4. Partition by event or section so most orders stay local.

Sub-counters need rebalancing, and may refuse a sale while stock sits in another sub-counter. Partitioning makes orders that span sections expensive.

Shorten W and split the hot counter first; do not change the isolation scheme until the abort rate is known.

Use the conflict probability p ≈ L² k² / 2N: L transactions in flight, each touching k keys, out of N effective keys.

L is tiny, so p is almost zero: optimistic, or serializable snapshot isolation, which also catches write skew.

With W = 3 ms, L = 15; with k = 2 and N = 10⁶, p ≈ 15² × 4 / (2 × 10⁶) ≈ 0.05%, well below the 1–2% break-even: optimistic wins, and its retries are rare. Watch for a few giant accounts, which shrink the effective N.

N ≈ 1, so any scheme only chooses between queueing and burning CPU. The answer is not a scheme but a reformulation: partitioned counters or escrow reservations, which commute.

MVCC is working as designed. Old versions must be kept as long as the oldest live snapshot might need them, so a long read forces a choice of who pays.

On the PostgreSQL-style system, versions live in the table, so writers pay in bloat, which is not recoverable without rewriting the table. On an Oracle-style system, versions live in a bounded undo area, so the reader pays with an error. (InnoDB keeps undo too, but its history grows instead of failing the reader.)

Move long reads to a replica; split the report into shorter reads, or paginate by key; set idle-in-transaction and statement timeouts; and monitor the age of the oldest snapshot, bloat and undo retention before deploying.

Space, a collector, and the oldest reader's veto over both.

Write skew under snapshot isolation. Two transactions each read the same set of agents, each sees another agent on shift, and each takes a different agent off. Snapshot isolation checks only write–write conflicts, and these wrote different rows.

1. Run only this transaction at SERIALIZABLE (serializable snapshot isolation), which aborts one side of a dangerous dependency pattern; expect a small abort rate and add a retry loop.
2. Lock the rows the check depends on (SELECT … FOR UPDATE on all agents), at the price of queueing.
3. Make the conflict explicit by having both transactions update one shared guard row.
4. Use a database constraint, if the rule can be expressed as one.

State it as an abort rate. How coarsely reads are tracked (row, page or table) sets how many aborts are false alarms.

True predicate locking is intractable, so databases lock ranges of an index instead (next-key and gap locks). Which locks a statement takes therefore depends on the index the optimizer chose; without a suitable index, the only safe choice is to lock much more, up to the whole table. Dropping the index changed the plans, and with them the lock footprints and the deadlock structure. Isolation behaviour depends on the plan.

Treat index and plan changes as concurrency changes, and test them under realistic concurrent load. Keep a deadlock log and a deadlock retry in the application, take locks in a consistent order, and keep transactions short.

It would need a global waits-for graph built from a consistent distributed snapshot, which is slow and stale.

Timestamp-based wound-wait, as Spanner does. Older transactions abort younger lock holders, which wastes little work and lets short transactions wait out brief locks. A restarted transaction keeps its original timestamp, so it cannot starve. Every decision is a local comparison, with no communication.

Lock-wait timeouts only as a safety net; no correct value exists, because waits have a heavy tail.

Some unnecessary aborts: prevention aborts whenever a cycle might form, not only when one has.

With the working set in memory, a transaction takes about 50 µs, so one core handles about 20,000 transactions per second per partition, and with one transaction at a time no concurrency control is needed.

1. Does the partition key match the transaction boundary? Each cross-partition transaction stalls the thread for a coordination round trip, so throughput falls off a cliff as their share grows.
2. Does the working set stay in memory? When disk I/O returns, transactions get long and the argument reverses.
3. Can every transaction be a one-shot, non-interactive procedure?

Adopt it only for workloads with a clean partition key, such as per-user or per-event state, and keep the rest in a general-purpose database.

Concurrency control has become a data-modelling decision you must defend permanently.

The client cannot tell whether the top-up committed: the outcome is unknown.

The client generates one idempotency key per intent and sends it on every retry. The server inserts the key into a unique column inside the same transaction as the balance change. A duplicate key returns the stored result instead of applying the change again.

The key table needs a retention window, say 7 days. A retry after a key is evicted would be applied twice, so cap the client's retry period below the retention. Deduplication state also needs clean-up and monitoring.

A reconciliation report against the payment provider, because the data pays quietly whenever the window assumption breaks.

The unit of conflict is a file, not a row. If each job touches about 100 of 10,000 files, any two jobs collide with probability near 1, and a commit taking about 50 ms makes each window of exposure long.

Whether failed commits can be rebased: an append can simply retry its metadata step, but a MERGE or a compaction that rewrote files others read cannot.

One writer per table, or disjoint partitions per writer. Run compaction in a window without ingestion. Use append mode where possible. Retry with jittered backoff and a retry budget, and commit in fewer, larger batches: the ceiling is tens of commits per second.

Partitioning writers constrains the data model.

The payment API has no prepare phase, so two-phase commit is not available across all three.

For the two databases, locks are held for the local work plus two round trips and two log flushes, and with hot rows that duration enters the conflict rate squared. Availability is the product of all participants, 0.999³ ≈ 0.997.

A saga: reserve inventory, authorize the payment, then capture it, putting the irreversible step last, with compensating actions that are idempotent. Publish events through an outbox, with idempotent consumers.

Intermediate states are visible (use a pending status), compensations can get stuck (route them to a human queue), and you need a reconciliation job.

Write ahead and force at commit: the commit is the moment its record becomes durable. Release locks after the flush, and acknowledge last.

Without batching, 1 ÷ 50 µs = 20,000 flushes per second, exactly the target, with no headroom. Use group commit: its self-sizing batches let throughput rise with load.

For tables that can tolerate a tiny loss window, offer asynchronous commit with a bounded window, as an explicit trade.

They still face the unknown-outcome window, so their retries need idempotency keys.

Both make the database commit the atomic event, and both deliver at least once, so consumers need idempotence either way.

You design event shapes independently of your schema, at the price of a table with constant insert-and-delete churn (index bloat, vacuum pressure), eased by reading it through CDC rather than polling.

No extra table, but 12 teams become coupled to your physical schema: renaming a column breaks them.

With 12 consumers and long-lived contracts, the outbox. Keep per-key order by partitioning events by order ID.

Both pay for the same clock uncertainty ε. Commit wait (TrueTime-style) puts ε on every write as pure latency, and reads stay cheap. Hybrid logical clocks with an uncertainty interval put it on reads, as restarts whose rate grows with clock skew.

With 95% reads, billing the 5% of writes is usually cheaper.

The cost of making ε small (GPS and atomic clocks, or a cloud bounded-time service) against the latency it saves. And correct the folklore: atomic clocks are not required, only a bound on uncertainty.

Recover the coordinator first: its log holds the decisions, and prepared participants cannot decide alone. If recovery is impossible, an operator makes heuristic decisions, accepting a possible atomicity violation, and reconciles by hand.

Replicate the coordinator with consensus; set timeouts and alerts on prepared transactions; keep lock-hold times short by repartitioning so most transactions are local; and design the reconciliation path before launch.

Under presumed abort, a transaction with no decision record is treated as aborted.

Exactly-once delivery is not; exactly-once processing is.

Use an outbox so the database commit covers the intent to publish. Make consumers idempotent, by a natural key or a deduplication table whose retention exceeds how far back the broker can replay. For Kafka-to-Kafka stages, transactions commit the output and the input offset as one atomic event.

Deduplication entries expiring is the silent failure; monitor with reconciliation counts.