PostgreSQL's Repeatable Read Allows Phantom Reads But its document says that it does not allow

Question

I have a problem with Postgresql repeatable read isolation level. I did make an experiment about repeatable read isolation level's behavior when phantom read occurred.

Postgresql's manual says "The table also shows that PostgreSQL's Repeatable Read implementation does not allow phantom reads."

But phantom read occurred;

CREATE TABLE public.testmodel
(
    id bigint NOT NULL
);

--Session 1 --

BEGIN TRANSACTION ISOLATION LEVEL Repeatable Read;
INSERT INTO TestModel(ID)
VALUES (10);

Select sum(ID)
From TestModel
where ID between 1 and 100;

--COMMIT;

--Session 2--

BEGIN TRANSACTION ISOLATION LEVEL Repeatable Read;    
INSERT INTO TestModel(ID)
VALUES (10);

Select sum(ID)
From TestModel
where ID between 1 and 100;

COMMIT;

Steps I followed;

1. Create Table
   
2. Run session 1 (I commented commit statement)
   
3. Run session 2
   
4. Run commit statement in session 1.

To my surprise, both of them (session 1, session 2) worked without any exceptions.

As far as I understand from the document. It shouldn't have been. I was expecting session 1 throw exception, when committing it after session 2.

What is the reason of this? I am confused.

Consciencestricken

Why would you expect an error (and which error did you expect)? You haven't defined a constraint that would be violated by your code. – Bauer

if I used Serializable isolation level,I would get "ERROR: could not serialize access due to read/write dependencies among transactions".Like this I expect an exception.So this transaction must be rollbacked.But Repeatable Read does not provide this validation – Consciencestricken

Well, you didn't use serializable, so why do you expect an error? – Bauer

"PostgreSQL's Repeatable Read implementation does not allow phantom reads" statement is confusing.May the manual is wrong ? – Consciencestricken

Answer 1

The docs you referenced define a "phantom read" as a situation where:

A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.

In other words, a phantom read has occurred if you run the same query twice (or two queries seeking the same data), and you get different results. The REPEATABLE READ isolation level prevents this from happening, i.e. if you repeat the same read, you will get the same answer. It does not guarantee that either of those results reflects the current state of the database.

Since you are only reading data once in each transaction, this cannot be an example of a phantom read. It falls under the more general category of a "serialization anomaly", i.e. behaviour which could not occur if the transactions were executed serially. This type of anomaly is only avoided at the SERIALIZABLE isolation level.

There is an excellent set of examples on the Postgres wiki, describing anomalies which are allowed under REPEATABLE READ, but prevented under SERIALIZABLE isolation: https://wiki.postgresql.org/wiki/SSI

Hitt

You are right.I could not grasp phantom read concept.I should have understood from "another recently-committed transaction" statement.This is serialization anomally not phantom read.Actually as far as i understand ,postgresql repeatable read works like snapshot isolation level in sql server and it uses mvcc tecnique.So it always return last committed data as of current transaction beginning.I mean it snapshots database the last state at the beginning of the transaction.when I run same query twice in same transaction it always returns same results.Thank you. – Consciencestricken

Answer 2

Phantom read doesn't occur in REPEATABLE READ in PostgreSQL as the documentation says. *I explain more about phantom read in my answer on Stack Overflow .

I experimented if phantom read occurs in REPEATABLE READ in PostgreSQL with 2 command prompts.

First, to set REPEATABLE READ, I rans the query below and log out and log in again:

ALTER DATABASE postgres SET DEFAULT_TRANSACTION_ISOLATION TO 'repeatable read';

And, I created person table with id and name as shown below.

person table:

id	name
1	John
2	David

Then, I did these steps below with PostgreSQL queries:

Flow	Transaction 1 (T1)	Transaction 2 (T2)	Explanation
Step 1	BEGIN;		T1 starts.
Step 2		BEGIN;	T2 starts.
Step 3	SELECT * FROM person; 1 John 2 David		T1 reads 2 rows.
Step 4		INSERT INTO person VALUES (3, 'Tom');	T2 inserts the row with 3 and Tom to person table.
Step 5		COMMIT;	T2 commits.
Step 6	SELECT * FROM person; 1 John 2 David		T1 still reads 2 rows instead of 3 rows after T2 commits. *Phantom read doesn't occur!!
Step 7	COMMIT;		T1 commits.

Rashid

Answer 3

You are misunderstanding what "does not allow phantom reads" means.

This simply means that phantom reads can not happen, not that there will be an error.

Session 2 will not see any committed changes to the table until the transaction from session 2 is committed as well.

repeatable read guarantees a consistent state of the database for the duration of the transaction where only changes made by that transaction itself will be visible, but no other changes. There is no need to throw an error.

Bauer