Search code examples
postgresqltransactionsisolation-levelrepeatable-read

Postgresql REPEATABLE READ can see data committed after transaction begins(and do nothing before other transaction starts and commits)


Postgresql REPEATABLE READ can see data committed after transaction begins(and do nothing before other transaction starts and commits) even if its document says it cannot.

Start Transaction A with isolation level REPEATABLE READ, and then start Transaction B which commits data. After that, if Transaction A selects data which Transaction B commits, it can see committed data.

Postgresql document states that

The Repeatable Read isolation level only sees data committed before the transaction begins; it never sees either uncommitted data or changes committed by concurrent transactions during the transaction's execution.

If Transaction A begins and then execute any query, then it cannot see committed data by other. However if begins transaction but do nothing it can see it. I just wonder the specific logic behind this.

Appreciate in advance!


Solution

  • You are right, the documentation is imprecise there.

    The snapshot for the repeatable read transaction is not taken by BEGIN (the start of the transaction), but by the first SQL statement executed inside the transaction.