Decision: Which isolation level?
The transaction isolation level determines the balance between concurrency and consistency. Higher isolation means fewer anomalies but more locking/conflicts and retries. This tree helps you choose between Read Committed, Repeatable Read/Snapshot, and Serializable based on which read anomalies (dirty, non-repeatable, phantom) you can tolerate.
Can you tolerate dirty reads?
Start with the most basic anomaly: should a transaction be able to read data that has not been committed yet (and may be rolled back)? Almost no serious application wants this; in most modern engines the default already prevents dirty reads. It is relaxed only for approximate/analytical rough counts and maximum speed.
- No, I must read only committed data
- Yes, a rough/approximate read is enough (rare)
Must you see the same result when you re-read within the same transaction?
Having ruled out dirty reads, the next question: if you read the same row twice within a transaction, is it a problem for you to see a different value because someone committed in between (non-repeatable read), or for new rows to appear (phantom)? Logic that requires report/calculation consistency usually needs a consistent snapshot.
- No, it is fine for each statement to see the latest committed data
- Yes, a consistent snapshot is needed for the whole transaction
Must concurrent transactions be logically fully ordered?
You want consistent reads; is that enough, or must concurrent transactions produce a result as if they had run one by one, in sequence (serializable)? If you can tolerate subtle anomalies such as write skew (e.g. two transactions reading different rows and writing in a way that invalidates each other), Snapshot is enough; otherwise full serialization is required.
- No, a consistent snapshot is enough
- Yes, no concurrency anomaly is acceptable
Recommendation: Read Uncommitted (special cases only)
The lowest isolation; it allows dirty reads. It reads with almost no waiting on locks, but you may see data that can be rolled back. Not recommended for general-purpose applications.
Recommendation: Read Committed (default OLTP)
Each statement sees the latest data committed at that moment; dirty reads are prevented. It is the default in most engines and offers the best concurrency/consistency balance for typical OLTP workloads.
Recommendation: Repeatable Read / Snapshot (consistent reads)
The transaction works on a consistent snapshot fixed at its start (or at its first read); reads do not change during the transaction. With MVCC, readers do not block writers and writers do not block readers.
Recommendation: Serializable (full isolation)
The highest isolation: concurrent transactions produce a result as if they had been run serially (one by one, in sequence). It prevents all read anomalies and write skew; the price is more conflicts and the need for retries.