In the SQL standard, which is the lowest isolation level that prevents non-repeatable reads?
Options
- Read Uncommitted
- Read Committed
- Repeatable Read (Correct option)
- Serializable
Answer
Repeatable Read
Why
A non-repeatable read: inside one transaction you read a row twice and get different values, because another transaction changed and committed it in between.
Levels, from weakest to strongest:
- Read Uncommitted: can see uncommitted data (dirty reads).
- Read Committed: no dirty reads (PostgreSQL default).
- Repeatable Read: also no non-repeatable reads.
- Serializable: also no phantoms (new matching rows appearing).
Remember
Read Committed < Repeatable Read < Serializable.