CodeParkThe four levels, from weakest to strongest, are Read Uncommitted, Read Committed, Repeatable Read and Serializable. The SQL standard defines them by three anomalies (wrong results caused by transactions running at the same time):
- A dirty read sees data that another transaction has not committed yet.
- A non-repeatable read reads the same row twice in one transaction and gets a different value.
- A phantom read runs the same query twice and gets a different set of rows.
What each level prevents:
- Read Uncommitted allows all three.
- Read Committed prevents dirty reads.
- Repeatable Read also prevents non-repeatable reads.
- Serializable prevents every anomaly. The result is the same as running the transactions one after another in some order.
How PostgreSQL differs:
- Read Committed is the default.
- Read Uncommitted behaves exactly like Read Committed, so dirty reads never happen.
- Repeatable Read is snapshot isolation: the transaction sees one fixed view of the data. It also prevents phantoms, but it still allows write skew (two transactions read the same data, then each changes a different row, and together they break a rule).
- Serializable uses Serializable Snapshot Isolation (SSI). It may abort transactions with serialization failures, and you must retry them.
By contrast, MySQL InnoDB uses Repeatable Read as its default.
- The three anomalies are dirty read, non-repeatable read and phantom read.
- Each stronger level prevents one more anomaly.
- PostgreSQL's default is Read Committed, and Read Uncommitted acts the same.
- PostgreSQL's Repeatable Read prevents phantoms but allows write skew.
- Serializable uses SSI and needs retries.
Read more