Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

http://www.postgresql.org/docs/9.2/static/transaction-iso.html

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?

share|improve this question

migrated from stackoverflow.com Jun 6 '13 at 10:02

This question came from our site for professional and enthusiast programmers.

    
No. "phantom reads are not possible in the PostgreSQL implementation of Repeatable Read" The table shows what's permitted by the SQL standard. –  alice May 15 '13 at 16:43
    
Oops you're right @alice => comment deleted. –  Daniel Vérité May 15 '13 at 17:06
1  
suggest migrating this one to DBA.se? –  Chris Travers Jun 6 '13 at 6:27

2 Answers 2

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

Suppose I have two tables:

CREATE TABLE batch (
   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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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