Re: Row locking and serializability

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 Sat Jun 05 1999 - 08:00:36 CDT