On 11/30/2010 1:06 AM, Dmitry Yemanov wrote:

> Ann W. Harrison wrote:
>
>> Postgres, InnoDB (the MySQL transactional engine), and Oracle
>> implement another isolation mode between "Snapshot" and
>> "Read Committed." Normal Select statement works just as
>> they do in Firebird's Snapshot mode.
>
> What's exactly meant here regarding selects? Do they really work like in
> snapshot, i.e. not seeing committed data of other transactions?

Yes, that's the way they work. They don't see committed data by other
transactions.

> so. And it's surely not "read committed" in PGSQL.

But it is..

http://www.postgresql.org/docs/7.4/interactive/transaction-iso.html

> I rather believe you
> were talking about the cursor stability, i.e. the statement being run
> (or cursor being fetched from) doesn't see concurrent modifications
> committed after statement start. But the next statement in the same
> transaction will see those committed data (under the same rules).

No, that's not the way InnoDB works from personal experience, and that's
not the way postgresql works according to their documentation.

>
>> Update, delete, and select for update wait if there's a
>> newer version of the record than the transaction can see,
>> then succeed when the transaction that created the
>> newer version commits.
>
> So it differs from our read-committed mode in the fact that it
> overwrites the data after a concurrent commit, not throw an update
> conflict as we do. Did I get it right?

No you didn't get it right. This mode retains read stability for
the duration of the transaction, with the exception that a select
for update will return and lock a record version that was committed
after the reading transaction started, and the reader will wait if
the most recent version is not yet committed. Updates and deletes
block if there is an uncommitted version, but succeed after that
version commits.

> This has been mentioned a few
> times and I do agree it can be useful in various usage scenarios.

Yes, read committed mode would be better if it could update or delete
a record that was committed by a concurrent transaction, but that's
not what I'm writing about.

>
> But implementation looks tricky at the first glance. We cannot proceed
> at the VIO layer, because the modified record may affect the predicate
> our update/delete/lock is based on, i.e. the modified record should no
> longer be visible for us and thus be skipped, not overwritten. It means
> that we have to unwind the whole RSB stack, re-fetch all the streams,
> re-evaluate all the booleans and if everything still looks well re-try
> the modification. This is surely possible for some statements, but
> hardly problematic for others (DISTINCT, GROUP BY, etc). So I'm not
> surprised Oracle may re-start the whole statement in this case. Is it
> really the cost we should pay? Do I miss anything?

Yes, I think you did. Jim will do a better job of explaining his
implementation, but it certainly did not involve redoing whole
statements.

Best regards,

Ann