CodePark/ questions / Isolation levels & anomalies
START PRACTICINGPRACTICE
Backend question · Databases & SQL › Isolation levels & anomalies

Which statement about PostgreSQL's Repeatable Read level is true?

It prevents phantom reads but still allows write skew

The true statement is that it prevents phantom reads but still allows write skew.

In PostgreSQL's Repeatable Read, each transaction gets one
snapshot (a fixed picture of the data) for its whole life. So if you run the same query again, you never see new rows. A phantom read is when new rows appear like this, so they can't happen here. This is stricter than the SQL standard requires, so "It allows phantom reads, as the SQL standard permits" is wrong for PostgreSQL.

But Repeatable Read does not check for conflicts between the rows you read and the rows other transactions write. So
write skew is still possible. Write skew means two transactions each read the same data, then each write something different, and together they break a rule. Only Serializable prevents write skew, so "It behaves exactly like Serializable" is wrong.

Under MVCC (multi-version concurrency control), reads take no row locks. So "It takes shared locks on every row it reads, so writers wait for readers" is also wrong.