Re: Row locking and serializability

RE: "the proposition that Oracle has correctly interpreted the SQL92 definition of serializability"

> Subclause 4.28, "SQL-transactions", in [ANSI] notes that the
> SERIALIZABLE isolation level must provide what is "commonly known as
> fully serializable execution."

Would it be possible to extract from 4.28 the strict definition of "fully serializable execution" so that we can all see it please.

Interesting point: the Abstract to the paper by Berenson, Hal ; Bernstein, Phil ; Gray, Jim ; Melton, Jim ; O'Neil, Elizabeth ; O'Neil, Patrick, dated June 1995 reads:

        ANSI SQL-92 [MS, ANSI] defines Isolation Levels in terms of phenomena:

        Dirty Reads, Non-Repeatable Reads, and Phantoms.

I haven't been able to download the paper yet so I presume from your extract that this was the editor's interpretation not the authors' statement.

A side-issue on your comment about application code and database engine

> To the extent that the database engine performs
> integrity checks automatically (referential integrity checks or checks
> of other types), these checks may be considered an integral part of the
> application developer's source code for the purposes of this discussion.

Application code would have to obey the three denial rules, so could not be used to do pre-emptive checks of business rules such as the 100-seat limit on the aircraft.

On the other hand, a database constraint (or commit-time trigger perhaps) could be allowed to be allowed to cheat and check for e.g. uncommitted rows (which is how Oracle caters for one of the problems of parent/child RI constraints).

So there is likely to be a significant difference between application level code and embedded database code.

Finally a thought experiment -

Table X holds 20,000,000 rows

Time T1 Tx A inserts one row
Time T2 Tx B inserts one row
Time T3 Tx A selects count(*) from X - answer 20,000,001 Time T4 Tx B selects count(*) from X - answer 20,000,001 Time T5 Tx A commits
Time T6 Tx B commits

This is a very simple example of an interleaved history which would not return the same result if the two transactions were re-arranged to operate serially (as far as Oracle was concerned)

The way I see it, the only thing you can do in this case is to ensure that transaction B cannot insert a row until transaction A has committed - i.e. make the interleaved sequence one that cannot occur. Which tends to suggest that all sorts of transactions can be serialisable only if they actually are serialised: a loss of concurrency that most database users would not be too happy with.

I must say I would be interested in downloading the paper - any chance you could email it to me ?

Thanks.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk Received on Sun Jun 13 1999 - 09:22:57 CDT