Обсуждение: What happens If a table changes during a query/procedure execution
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? 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? Does SQL standard define the behaviour in such cases?
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
2011/3/9 Nicolas Barbier <nicolas.barbier@gmail.com>: > 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. Hmm, make that "stuff as committed by concurrent transactions that committed prior to our statement's execution". Nicolas
09.03.2011 18:54, Nicolas Barbier: > 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. > > Is it also true if a statement contains subqueries or function calls? For instance, CREATE FUNCTION f() RETURNS NUMERIC AS $$ BEGIN RETURN (SELECT SUM(a) FROM t); END; $$ LANGUAGE 'plpgsql'; SELECT a, f() FROM t; or SELECT a, (SELECT SUM(a) FROM t) FROM t;
2011/3/9 Vlad Arkhipov <arhipov@dc.baikal.ru>: > 09.03.2011 18:54, Nicolas Barbier: >> >> 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. >> >> > > Is it also true if a statement contains subqueries or function calls? For > instance, > > CREATE FUNCTION f() RETURNS NUMERIC AS $$ > BEGIN > RETURN (SELECT SUM(a) FROM t); > END; > $$ LANGUAGE 'plpgsql'; > > SELECT a, f() FROM t; > > or > > SELECT a, (SELECT SUM(a) FROM t) FROM t; yes, it is same Regards Pavel Stehule > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >