5
votes

Quote from http://www.postgresql.org/docs/9.4/static/transaction-iso.html :

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.

To clarify: does it mean pg's repeatable read = serializable ?

2
Note that in older versions of PostgreSQL (specifically, versions before 9.1) the answer to your question is "yes, they are the same". 9.1 introduced a true "serializable" mode that meant that Repeatable Read and Serializable are now two different isolation levels with different rules.Mark Hildreth
@MarkHildreth Good catch, I will edit this into my answer.IMSoP

2 Answers

6
votes

No; the difference is described on the page you linked to:

In fact, this isolation level works exactly the same as Repeatable Read except that it monitors for conditions which could make execution of a concurrent set of serializable transactions behave in a manner inconsistent with all possible serial (one at a time) executions of those transactions.

The documentation goes on to give an example where Repeatable Read and Serializable behave differently. A Serializable transaction can abort with a "serialization failure", but does not block any extra transactions from completing.

The section you quoted is explaining some anomalies because the standard SQL isolation levels are designed around locking data, but PostgreSQL is implemented with an "MVCC" design, where concurrent transactions can be given independent snapshots of the data. Thus some of the distinctions present in other systems don't apply, and Postgres interprets the isolation levels as "at least as strict as..."

As Mark Hildreth pointed out in comments, this distinction is only true from PostgreSQL 9.1 onwards. The documentation for 9.0 states:

But internally, there are only two distinct isolation levels, which correspond to the levels Read Committed and Serializable.

Whereas in newer versions this has been amended to:

But internally, there are only three distinct isolation levels, which correspond to the levels Read Committed, Repeatable Read, and Serializable.

3
votes

No.

Repeatable read is snapshot isolation. It means your transaction sees a single consistent "snapshot" of the database. It is not full serializability, because some operations may produce results inconsistent with any serial ordering. For example, if one transaction inserts a row which matches another transaction's SELECT operation, and vice versa, this may cause serialization anomalies. Serializable uses a technology called "predicate locking" to detect these situations and reject any offending transactions. This "locking" does not block the transaction and cannot participate in a deadlock.