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.

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