Re: Row locking and serializability

RE: "we have two transactions which are inherently not serialisable"

More accurately, we have a "non-serial" (i.e. interleaved) history that is not "serializable", i.e. does not produce the same results as some "serial" (i.e non-interleaved) history of the transactions involved. A requirement of the SQL92 standard and the challenge before Oracle and all other database vendors (relational or otherwise), is the prevention of all non-serial tranaction histories that are not serializable. The standard does not require the application developer to use any special manual techniques to guarantee serializability of non-serial transaction histories.

RE: "serialisable transactions do not, BY THEMSELVES, guarantee referential integrity"

Serializability (of non-serial transaction histories) only provides the (important) guarantee that the application developer's intentions have been fulfilled (in the sense that each non-serial transaction history is required to produce the same results as some serial history of the transactions involved). 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.

Here is another example of a non-serial transaction history which is non-serializable but which the Oracle engine cannot prevent. We might term this the "airline overbooking example".

• begin example ---

The "airline overbooking" example:

(The business rule which the application developer in this example is trying to enforce, is a rule that states that "only 100 passenger reservations may be accepted for a flight")

At Time T1, Transaction A performs the query "select count(*) from passenger_reservations where flight_number=123 and flight_time='2000/01/01'". Assume that the result is 99. Transaction A then assumes that it may book an additional passenger on the indicated flight.

At Time T2, Transaction B performs the same query and obtains the same result. Transaction B also assumes that it may book an additional passenger on the indicated flight.

At Time T3, Transaction A books an additional passenger on the indicated flight, raising the total number of passenger reservations for this flight to 100.

At Time T4, Transaction B books an additional passenger on the indicated flight, raising the total number of passenger reservations for this flight to 101. This violates the business rule ("only 100 passenger reservations may be accepted for a flight") which the application developer's source code was trying to enforce by checking the total number of prior reservations before making an additional reservation.

At Time T5, Transaction A commits its work.

At Time T6, Transaction B commits its work.

• end example ---

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 <928961624.26673.1.nnrp-07.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
> There is an interesting philosophical question there.
>
> The example simply has:
> transaction A inserts a (child) row
> transaction B deletes a (parent) row
>
> Arguably the fact that there is a business rule that state:
> child rows should not exist unless the parent row exists
> has nothing to do with serialisable transactions.
>
> If we change transaction A to :
> lock the parent row and insert a child row if the parent is locked
> and transaction B to:
> delete the parent row if no children exist
>
> then we have two transactions which are inherently
> not serialisable and the discussion becomes pointless.
>
> I believe the point of this particular section of the manual
> was simply to demonstrate that serialisable transactions
> do not, BY THEMSELVES, guarantee referential integrity.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
> Michael Ortega-Binderberger wrote in message ...
> >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.
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Sat Jun 12 1999 - 20:49:56 CDT