Hi!
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? ^_^
Regards,
Álvaro
[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);
BEGIN ISOLATION LEVEL REPEATABLE READ;
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
-----------
8Kdata
