| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Row locking and serializability
On Sat, 5 Jun 1999, David Cressey wrote:
> 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.
I disagree. The fact that as the original mail suggests, you can
construct and example where it breaks down, means that Oracles
serializability is not what is commonly assumed.
> >(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.
Really called shadowing. However extremly error prone to implement,
and usually buys you nothing in speed. I have no knowledge if oracle
is using this but I would almost lean on the no side.
> >(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.
>
Row locks are still not under user control (at least should not).
As to when locks can safely be released, this depends on the
characteristics of the schedule you come up with. You can get away
with releasing locks earlier if you want to sacrifice some
properties such as ACA. In general I believe this is not a good
idea, although for performance reasons this could be done and would
work best in a mostly read environment I believe.
How do you say that inclreases overhead and also concurrency? This isn't clear to me.
> >(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.
Yes, its hard to make these tests, but apparently Ingres does a
proper job of ensuring serializability, which should make
constructing such an example impossible.
Michael
>
>
> Regards,
> Dave
>
>
>
>
---
Michael Ortega-Binderberger miki_at_acm.org, miki_at_ics.uci.edu Department of Computer Science Office (949)824-7231, Fax (949)824-4056U. of Illinois, Urbana Champaign http://www.ics.uci.edu/~miki On loan to University of California, Irvine ICQ: 25412074
"Any addition to the truth is a subtraction from it." Alexander Solzhenitsyn Received on Sun Jun 06 1999 - 19:43:23 CDT
 |
 |