Re: Row locking and serializability

Hallo All,

unforunetly I don't have the article starting this discussion on my news server so I cant answer directly.
Maybe the following link helps
http://www.csee.umbc.edu/help/oracle8/server803/A54643_01/ch_intro.htm#3932

Best Regards

iggy_fernandez_at_my-deja.com schrieb:

> RE: Definition of Serializability in Subclause 4.28 of the SQL92 standard.
>
> Here's what I have. I am referring to the copy of the SQL92 standard that I
> found at the address
> "http://sunsite.doc.ic.ac.uk/pub/computing/programming/languages/perl/db/refi
> nfo/sql2/sql1992.txt". The revision date on this document is July 1992.
> Please also refer to my postings of June 1 for a longer excerpt.
>
> --- begin extract ---
>
> The execution of concurrent SQL-transactions at isolation level SERIALIZABLE
> is guaranteed to be serializable. A serializable execution is defined to be
> an execution of the operations of concurrently executing SQL-transactions
> that produces the same effect as some serial execution of those same
> SQL-transactions. A serial execution is one in which each SQL-transaction
> executes to completion before the next SQL-transaction begins.
>
> --- end excerpt ---
>
> The first sentence in the above paragraph is "legalese" that equates the use
> of the word "serializable" (in the context of isolation levels) with the
> rigourous definition that immediately follows. The standard goes on to define
> three anomalies that transactions executing at various isolation levels may
> (and may not) experience and concludes this characterization of isolation
> levels with the following remark.
>
> --- begin excerpt ---
>
> Note: The exclusion of these phenomena for SQL-transactions executing at
> isolation level SERIALIZABLE is a consequence of the requirement that such
> transactions be serializable.
>
> --- end excerpt ---
>
> Note in particular, the use of the word "consequence".
>
> Here are the four questions I asked originally.
>
> (1) The Oracle8 concepts manual mentions a "greater degree of transaction
> isolation than defined by SQL92 SERIALIZABLE mode". Has Oracle correctly
> interpreted the SQL92 definition of serializibility? (2) "Readers" do not
> block "writers" in an Oracle database. Is it possible to guarantee
> serializability without requiring readers to block writers? (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? (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 definition of serializability?
>
> I now believe that the short answers to the first three questions are No, No
> and No. The answer to the second and third questions are found in the
> "fundamental serialization theorem" (also discussed in the paper by Berenson
> et al) which lists "predicate" locks among the necessary and sufficient
> conditions for serializability. (A predicate lock is a lock on the entire
> universe of objects that satisfy the query conditions (predicates), not just
> those physically present in the database.) I have posted a request on other
> newsgroups for information on the behavior of database engines other then
> Oracle, when they encounter non-serial transaction histories that cannot be
> serialized. I will update this thread with detailed answers to the above
> questions once I have more information.
>
> RE: Research paper by Berenson et al
>
> Try http://research.microsoft.com/~gray/isolation.doc. This is in MS-Word
> format and the server response is above average.
>
> Disclaimers: (1) My employer may have opinions very different from mine. (2)
> My opinions may prove to be significantly incorrect. (3) Oracle itself is the
> final authority on the capabilities of the Oracle product line.
>
> In article <929284005.7896.0.nnrp-13.9e984b29_at_news.demon.co.uk>,
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
> > 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
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Tue Jun 15 1999 - 11:15:32 CDT