Re: Row locking and serializability

Oracle and Rdb do not function the same way. In fact, Rdb did NOT allow updates (while Oracle does) inside a snapshot transaction precisely because such a scheme does not fully support the notion of serializability. There has been a large amount of debate about this ever since the ANSI SQL committee incorrectly described Serializability as part of the SQL92 standard. The error crept into the standard as a result of trying to restate the requirements for serializability to not require pessimistic locking. This is a complex topic that was the subject of a paper at the 1995 SIGMOD. Pat O'Neil and I initiated writing this paper, and very significantly the co-authors included Jim Gray (who authored the original paper describing serializability and isolation levels), Phil Bernstein (who has written more on this topic than any other author, and particularly about optimistic techniques such as snapshots), and Jim Melton (who wrote the description of isolation levels that is in SQL92).

You can find the paper (A Critique of ANSI SQL Isolation Levels) in the proceedings of SIGMOD '95 or at
http://research.microsoft.com/scripts/pubDB/pubsasp.asp?RecordID=5

--
All opinions expressed here are mine and mine alone

David Cressey <dcressey_at_valinet.com> wrote in message news:mc963.922$We5.37638_at_news2.giganews.com...
> I have answers to your questions, but they are not with reference to
Oracle
> 8.
> They are with reference to Oracle RDB, which used to be DEC RDB.
>
> >(1) The author mentions a "greater degree of transaction isolation than
> >defined by SQL92 SERIALIZABLE mode". Has Oracle correctly interpreted
> >the SQL92 definition of serializibility?
>
> I think so. I've seen this in DEC RDB literature some years ago.
>
>
> >(2) "Readers" do not block "writers" in an Oracle database. Is it
> >possible to guarantee serializability without requiring readers to
> >block writers?
> >
>
> Yes, by the use of what I will call "virtual snapshots". This means that
> the before image of every write (inserts, deletes, and updates) must be
> maintained somewhere, so long as there is any reader who ought to be able
> to "see" that before image. Read processes need to detect versions of
data
> that are newer than their snapshot, and the DBMS must retrieve the correct
> version from the snapshot. Of course, this should be transparent
> to all the processes.
>
> Warning: My use of the word "snapshot" is not consistent with the usage
of
> that word in Oracle RDBMS
> documentation. It is consistent with the use of that word in Oracle RDB
> documentation, which is
> different.
>
>
> >(3) Oracle only places locks on rows that satisfy query criteria and
> >does not place read locks on pages that were scanned in the process of
> >executing a query. Is it possible to guarantee serializability without
> >placing read locks on all such pages?
>
>
> Oracle RDB places locks on pages, but these locks are not under control
of
> the user, unlike row locks. That is, they are not held to the end of the
> transaction. It is the responsibility of the engine to synchronize
> access to pages, such that there are no buried updates to pages, in the
> background. This increases overhead, but it also increases concurrency.
> The overall effect on responsiveness is often positive.
>
>
>
> >(4) I was able to perform the referential integrity experiments against
> >an Ingres 6.4/06 database without experiencing the anomalies described
> >in the Oracle documentation. Is Oracle alone in its interpretation of
> >the SQL92 defininition of serializability?
> >
>
> I don't think so. How did you manage to guarantee concurrency of the
events
> that would have created the buried updates? I've always found it very
> difficult to arrange concurrency type tests.
>
>
> Regards,
> Dave
>
>
Received on Sun Jun 06 1999 - 20:48:53 CDT