8.9 Decision cheat sheet
Prefer, in order: (1) redesign so the transaction fits in one shard; (2) idempotency + a message-ID table (§5.6) — this covers most "exactly-once" needs with only local transactio…
Which isolation level?
If any transaction reads something, decides, and writes based on that decision, you need serializable — nothing weaker prevents write skew. Or: a uniqueness constraint, if the invariant happens to be expressible as one; or SELECT FOR UPDATE, if the rows you depend on already exist; or materializing conflicts as a last resort.
For read-modify-write cycles, use an atomic operation, a conditional write with a version column, or a database that detects lost updates — and know that MySQL repeatable read does not detect them.
Which serializability implementation?
| Choose | When |
|---|---|
| Serial execution | Active dataset fits in RAM; transactions are tiny; you can write stored procedures; writes fit one core or shard cleanly with almost no cross-shard transactions |
| 2PL | You need serializability on a system that only offers it this way; contention is low; and you can tolerate unstable tail latency |
| SSI | The default modern answer. Read-heavy, moderate contention, spare capacity, short read/write transactions, and an application that retries |
Do I need a distributed transaction? Prefer, in order: (1) redesign so the transaction fits in one shard; (2) idempotency + a message-ID table (§5.6) — this covers most "exactly-once" needs with only local transactions; (3) database-internal distributed transactions if your database has them; (4) XA — essentially never for new systems.
How do I make retries safe? Retry only transient errors (40001, deadlock, timeout, failover). Use exponential backoff with jitter. Cap attempts. Make the operation idempotent via a unique request ID so a lost acknowledgment doesn't cause a double-apply. Keep external side effects out of the transaction, or make them idempotent too.
Is my invariant enforceable? Single row → check constraint. Single column across rows → uniqueness constraint. Referential → foreign key. Across multiple rows or an aggregate → nothing but serializable isolation (or a single-leader design that funnels the decision through one place). And remember Ch 6: multi-leader and leaderless replication cannot enforce these at all.