Re: Row locking and serializability

From: David Cressey <dcressey_at_valinet.com>
Date: Sat, 3 Jul 1999 08:35:03 -0400
Message-ID: <KAnf3.108267$_m4.1357169@news2.giganews.com>

From the absence of further messages on this thread, I'm guessing the thread has pretty much played itself out. Please forgive me if what follows is beating a dead horse.

I looked up in the Oracle Concepts (Release 8.0) Manual, and here's what I found under "Preventable Phenomena and Transaction Isolation Levels"

Quote:

"The ANSI/ISO SQL standard (SQL92) defines four levels of transaction isolation with differing degrees of impact on transaction processing throughput. These isolation levels are defined in terms of three phenomena that must be prevented between concurrently executing transactions."

It then goes on to outline "dirty read", "nonrepeatable (fuzzy) read" and "phantom read".

Hre's my question about the SQL92 standard: Did the standard include the three phenomena as being illustrative or definitive?

Also, consider the following scenario, which is a minor variation from one previously given in this thread:

user A> set transaction read write;
user B> set transaction read write;
user A> select count(*) from passengers;
user B> select count(*) from passengers;
user A> insert into passengers values (...);
user B> insert into passengers values (...);
user A> commit;
user B> commit;

It's clear that the above is not serializable, because A and B get the same count in interleaved execution, while in serial execution they get different counts.

Here's what happens under Oracle 8: It goes through. No apparent problems. It can't be repeated in serial fashion, though.

Here's what happens under RDB (v6.1):

user A> set transaction read write;
user B> set transaction read write;
user A> select count(*) from passengers;  (process blocks)
user B> select count(*) from passengers;

  (short pause, then DEADLOCK error message appears.

user B> rollback;
user A> insert into passengers values (...);
user A> commit;

now user B can try again. If successful, the operations have been serialized. The failed transaction is, of course not serializable. I expect that failed transactions are outside the scope of the definition of serializability.

It's clear that the two systems behave differently. It's clear to me that one of them is wrong.

I'm still unclear as to whether Oracle has misinterpreted the SQL-92 standard concerning serializability. It's less important to me than whether their implementation has a flaw that needs to be compensated for in certain circumstances.

David Cressey mailto:David_at_dcressey.com

David Cressey wrote in message ...
>Iggy,
>
>Thanks for posting the excerpt from the SQL-92 paper.
>
Received on Sat Jul 03 1999 - 07:35:03 CDT