Inconsistence in transaction isolation docs

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.