Re: Inconsistence in transaction isolation docs

From: Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Inconsistence in transaction isolation docs
Date: 2007-10-16 12:33:53
Message-ID: 4714AFB1.4060601@cox.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 10/16/07 07:08, Trevor Talbot wrote:
> On 10/16/07, Nico Sabbi <nsabbi(at)officinedigitali(dot)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-----

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Michael Guiard 2007-10-16 12:38:03 Re: slow request
Previous Message Trevor Talbot 2007-10-16 12:08:16 Re: Inconsistence in transaction isolation docs