TransactionsAdvanced
Which isolation level would you choose?
“How do you pick an isolation level, and what does Serializable cost?”
What this tests
- Isolation levels
- Retry handling
- Anomaly-to-level mapping
Answers by level
Read the beginner answer first and notice what is missing.
Default to Read Committed and make each statement self-contained (SET x = x + 1, INSERT … ON CONFLICT, FOR UPDATE when reading first). Use Repeatable Read for anything that reads the same data twice — reports, exports. Use Serializable only when correctness depends on a condition you checked but did not write, and only with a retry loop.
At Serializable, a 40001 serialization failure is normal operation, not an error — you must catch and retry it.
Green flags · Red flags
Strong green flag · Knows 40001 is expected at Serializable and must be retried.
Green flags
- Defaults to Read Committed with self-contained statements
- Implements retries for Serializable
- Maps anomaly to level
Red flags
- "Serializable everywhere"
- No retry loop
- Assumes level names are portable
Follow-up questions
F1
Repeatable Read vs Serializable for a booking that must not double-book?
Scenario
A team sets everything to Serializable and throughput collapses under load. What happened and what do you advise?