Which transaction isolation level would you pick, and why?
Name the anomaly you are preventing before you name the level.
Points worth saying
- Read committed prevents dirty reads and is the usual application default.
- Repeatable read and snapshot prevent a row from changing under you.
- Serializable prevents phantoms, at the cost of blocking or retries.
A full answer
Start from the anomaly. Dirty reads mean you see another transaction’s uncommitted work. Non-repeatable reads mean the same row changes between two reads in your transaction. Phantoms mean a new row appears in a range you already read.
Read committed is the right default for most OLTP code. You do not see uncommitted data, writers are not blocked longer than their statement, and you handle conflicts with constraints and retries.
If a report must see one consistent moment, use snapshot or a replica, not a long serializable transaction on the primary. Snapshot keeps readers from blocking writers, and writers from blocking readers, by using row versions.
Serializable is for invariants the database cannot express with a simple constraint, such as “at most one overlapping booking for this room.” Expect retries. If you cannot explain the anomaly, you are not ready to raise the level.
Mark your progress
Create a practice profile to mark this question.