Thread: Inconsistence in transaction isolation docs
/From: http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html " Read Committed/ is the default isolation level in PostgreSQL. When a transaction runs on this isolation level, a SELECT query sees only data committed before the query began; it never sees either uncommitted data or changes committed during query execution by concurrent transactions. (However, the SELECT does see the effects of previous updates executed within its own transaction, even though they are not yet committed.) In effect, a SELECT query sees a snapshot of the database as of the instant that that query begins to run. Notice that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes during execution of the first SELECT. " to me the above sentence sounds inconsistent: it's asserting that both 1) and 2) apply: 1) it never sees ... changes committed during query execution by concurrent transactions 2) Notice that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes during execution of the first SELECT Can anyone explain, please?
Am Dienstag, 16. Oktober 2007 schrieb Nico Sabbi: > to me the above sentence sounds inconsistent: it's asserting that both > 1) and 2) apply: > > 1) it never sees ... changes committed during query execution by > concurrent transactions What this is supposed to mean is that you don't see changes while your own query runs. > > 2) Notice that two successive SELECT commands can see different data, > even though they > are within a single transaction, if other transactions commit changes > during execution > of the first SELECT -- Peter Eisentraut http://developer.postgresql.org/~petere/
On 10/16/07, Nico Sabbi <nsabbi@officinedigitali.it> wrote: > /From: > http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html > > " > Read Committed/ is the default isolation level in PostgreSQL. When a > transaction runs on this isolation level, a SELECT query sees only data > committed before the query began; it never sees either uncommitted data > or changes committed during query execution by concurrent transactions. > (However, the SELECT does see the effects of previous updates executed > within its own transaction, even though they are not yet committed.) In > effect, a SELECT query sees a snapshot of the database as of the instant > that that query begins to run. Notice that two successive SELECT > commands can see different data, even though they are within a single > transaction, if other transactions commit changes during execution of > the first SELECT. > " > > to me the above sentence sounds inconsistent: it's asserting that both > 1) and 2) apply: > > 1) it never sees ... changes committed during query execution by > concurrent transactions During *query* execution. If you start a SELECT that runs through a table from beginning to end, and while it is running some other transaction quickly commits a row to the end, this SELECT will not see it when it gets there. > 2) Notice that two successive SELECT commands can see different data, > even though they > are within a single transaction, if other transactions commit changes > during execution > of the first SELECT Within a single *transaction*. If you run the above SELECT again, it will see the newly added row.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 10/16/07 07:08, Trevor Talbot wrote: > On 10/16/07, Nico Sabbi <nsabbi@officinedigitali.it> wrote: >> /From: >> http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html >> >> " >> Read Committed/ is the default isolation level in PostgreSQL. When a >> transaction runs on this isolation level, a SELECT query sees only data >> committed before the query began; it never sees either uncommitted data >> or changes committed during query execution by concurrent transactions. >> (However, the SELECT does see the effects of previous updates executed >> within its own transaction, even though they are not yet committed.) In >> effect, a SELECT query sees a snapshot of the database as of the instant >> that that query begins to run. Notice that two successive SELECT >> commands can see different data, even though they are within a single >> transaction, if other transactions commit changes during execution of >> the first SELECT. >> " >> >> to me the above sentence sounds inconsistent: it's asserting that both >> 1) and 2) apply: >> >> 1) it never sees ... changes committed during query execution by >> concurrent transactions > > During *query* execution. If you start a SELECT that runs through a > table from beginning to end, and while it is running some other > transaction quickly commits a row to the end, this SELECT will not see > it when it gets there. > >> 2) Notice that two successive SELECT commands can see different data, >> even though they >> are within a single transaction, if other transactions commit changes >> during execution >> of the first SELECT > > Within a single *transaction*. If you run the above SELECT again, it > will see the newly added row. And this is the big difference between READ COMMITTED and SERIALIZABLE. With the latter, inside a single transaction the same query will return the same result set over and over again regardless of the updates to the base tables. And is why READ COMMITTED makes your RDBMS fail part 3 (Isolation) of the "ACID test". - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHFK+wS9HxQb37XmcRApXmAJ9K5W4taxUX4A3Aihs1971nJ5c6SQCgwfVu 3TKJez3RWeftJr7qeo8zJ/U= =qAhM -----END PGP SIGNATURE-----
Nico Sabbi wrote: > /From: > http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html > > " > Read Committed/ [...] > > to me the above sentence sounds inconsistent: it's > asserting that both 1) and 2) apply: > > 1) it never sees ... changes committed during query > execution by concurrent transactions > > 2) Notice that two successive SELECT commands can see > different data, even though they are within a single > transaction, if other transactions commit changes > during execution of the first SELECT > > Can anyone explain, please? 1) means: as long as the first SELECT runs ("during query execution"), you won't see changes made by another transaction. 2) means: when you run a second SELECT, that SELECT will see changes made by other transactions, even if both SELECTs are in one (read commited) transaction. That doesn't sound contradictory to me. There is a difference between "during query execution" and "within a single transaction", maybe that is where your problem comes from. Yours, Laurenz Albe
Albe Laurenz ha scritto: > Nico Sabbi wrote: > >> /From: >> http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html >> >> " >> Read Committed/ [...] >> >> to me the above sentence sounds inconsistent: it's >> asserting that both 1) and 2) apply: >> >> 1) it never sees ... changes committed during query >> execution by concurrent transactions >> >> 2) Notice that two successive SELECT commands can see >> different data, even though they are within a single >> transaction, if other transactions commit changes >> during execution of the first SELECT >> >> Can anyone explain, please? >> > > 1) means: as long as the first SELECT runs ("during > query execution"), you won't see changes made by > another transaction. > > 2) means: when you run a second SELECT, that SELECT > will see changes made by other transactions, even if > both SELECTs are in one (read commited) transaction. > > That doesn't sound contradictory to me. > There is a difference between "during query execution" > and "within a single transaction", maybe that is where > your problem comes from. > > Yours, > Laurenz Albe > > well, I know how read committed behaves, but I don't see why should anyone expect an update of the resultset of the currently executing query after a commit by a different transaction. Thanks everybody who replied, Nico
On 10/16/07, Nico Sabbi <nsabbi@officinedigitali.it> wrote: > well, I know how read committed behaves, but > I don't see why should anyone expect an update of the resultset > of the currently executing query after a commit by a different > transaction. A currently executing UPDATE will see changes made to matching rows.