2011/3/9 Vlad Arkhipov <arhipov@dc.baikal.ru>:
> Let there are two transactions that were created with read commited
> isolation level. In the first one we're executing a SELECT query:
> SELECT * FROM t UNION ALL SELECT * FROM t;
>
> In the second transaction we're modifying the same table:
> INSERT INTO t DEFAULT VALUES;
> COMMIT;
>
> Is it possible that the last UNION part in the first query will retrieve not
> the same rows as the first one?
No, because statements never see changes made by other transactions
while they are in flight.
> Another scenario is where we're executing two SELECT queries in a stored
> procedure:
> BEGIN
> ...
> SELECT * FROM t;
> SELECT * FROM t;
> END;
>
> Is it possible to get different results in the second query?
Yes, because they are separate statements, and in READ COMMITTED mode,
a new snapshot is taken when a statement starts. See:
<URL:http://www.postgresql.org/docs/9.0/static/transaction-iso.html#XACT-READ-COMMITTED>.
> Does SQL standard define the behaviour in such cases?
The first one certainly. The standard doesn't describe PL/PgSQL, so
the question is moot in the second case; nonetheless, I assume that
the answer would be yes in the case of SQL/PSM.
Note that the standard defines things that must never happen in the
case of READ COMMITTED, it does not specify that one *must* be able to
see the stuff as committed by previous transactions, for example.
Nicolas