The Repeatable Read mode provides a rigorous guarantee that each transaction sees a completely stable view of the database. However, this view will not necessarily always be consistent with some serial (one at a time) execution of concurrent transactions of the same level. For example, even a read only transaction at this level may see a control record updated to show that a batch has been completed but not see one of the detail records which is logically part of the batch because it read an earlier revision of the control record. Attempts to enforce business rules by transactions running at this isolation level are not likely to work correctly without careful use of explicit locks to block conflicting transactions.

Isn't that a phantom read, which is not possible in repeatable read mode?

The documentation says that a query in a repeatble read transaction sees a snapshot as of the start of the transaction, then how could it be possible for a query to read inconsistent data?


3 Answers 3


Here is my reading of that section. I will admit it is confusing.

Suppose I have two tables:

   id serial not null unique,
   control_code text primary key,
   date_posted date not null default now()

CREATE TABLE details (
   batch_id int not null references batch(id),
   description text,
   primary key(batch_id, description)

Now, suppose we insert batch and details records in different transactions. Session 1 inserts a batch and starts to insert details but before it finishes, session 2 starts up. Session 2 gets to see the batch heading info, but does not wait on the commit on details to inform the user that no records are found. Now if your batch and details are entirely in the same transaction then this never is a problem.

this would differ from serializable where you would expect to wait for the previous insert to complete and commit or rollback before determining whether to notify the user that no rows were found.


There is a document in the PostgreSQL Wiki that show some problems which can occur with certain combinations of transactions at the REPEATABLE READ transaction isolation level, and how they are avoided at the SERIALIZABLE transaction isolation level beginning with PostgreSQL version 9.1.

It also includes an example of how it could be possible for a REPEATABLE READ-level READ-ONLY transaction to read inconsistent data.

  • @dezso You might be interested in that Commented May 11, 2015 at 17:09

Phantom reads (be sure to not confuse this with non-repeatable reads) are possible in "Repeatable read" isolation level... in principle. But Postgresql de-facto behaviour when you select "Repeatable read" is stronger than the standard, (almost a "Serializable" isolation), so that, as a matter of fact you won't have phantom reads. Docs:

When you select the level Read Uncommitted you really get Read Committed, and phantom reads are not possible in the PostgreSQL implementation of Repeatable Read, so the actual isolation level might be stricter than what you select.

Now, what about that caveat "this view will not necessarily always be consistent with some serial (one at a time) execution of concurrent transactions of the same level"? I think (I'm not sure) that means that it means that the snapshot "from the outside" (fixed at the start of the transaction) can eventually include rows from other transactions but fail to include some other rows from that same transaction.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.