Re: Row locking and serializability

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 Mon Jun 14 1999 - 10:40:07 CDT