Given a transaction started with "BEGIN.... (REPEATABLE READ | SERIALIZABLE)", if a concurrent session commits some data before *any* query within the first transaction, that committed data is seen by the transaction. This is not what I'd expect. Specifically, the documentation states that:

"The Repeatable Read isolation level only sees data committed before the transaction began;" [1]

IMHO, from a user perspective the transaction begins when the BEGIN command is issued. If I really want to see a "frozen" view of the database before any real SELECT, I have to issue another query like "SELECT 1". This seems odd to me. I understand tx snapshot may be deferred until real execution for performance reasons, but it is confusing from a user perspective. Is this really expected, or is it a bug? Am I having a bad day and missing some point here? ^_^



[1] http://www.postgresql.org/docs/devel/static/transaction-iso.html

P.S. In case it wasn't clear what I meant, here's an example:

Session 1                                 Session 2

CREATE TABLE i (i integer);
                                                INSERT INTO i VALUES (1);
SELECT i FROM i; -- returns 1 row, value 1
-- should return empty set
                                                INSERT INTO i VALUES (2);
SELECT i FROM i; -- returns 1 row, value 1
-- returns, as it should, the same as the previous query

In the first select, I'd have expected to have no rows. If a "SELECT 1" is issued after BEGIN, there are no rows found.

Álvaro Hernández Tortosa


Reply via email to