Re: Row locking and serializability

From: Vadim Tropashko <vadimtro_at_yahoo.com>
Date: Mon, 14 Jun 1999 13:15:15 -0700
Message-ID: <376562D3.597AE2A0@yahoo.com>

Iggy,

I don't see how your airline booking example works. In time domain addressing aproach (i believe it's the same thing as "snapshot" isolation approach taken by Oracle since release 7.3) the database record history is divided into time intervals:
<98,[t0,t1)>, <99,[t1,*)>
In other words, at time t0 passenger count has been updated to 98, and later, at time t1 the most recent update - count=99 - has been made. That is the current value.

Each transaction is assigned a time at which it begins. In your example, transaction A at time t2 (excuse me for shifting your timeline by 2:-) modifies the history like this:
<98,[t0,t1)>, <99,[t1,t2)>, <99,[t2,*)> As you see, even though the count value didn't change, the history is extended to the present.
Next transaction B reads the count and, therefore, modifies history into: <98,[t0,t1)>, <99,[t1,t2)>, <99,[t2,t3)>, <99,[t3,*)> Now transaction A runs in the past and cannot update the count (while B still can!).

IMHO the difference between Snapshot Isolation and Locking (which "gang of six" seems proved to exist) is much smaller than the effect you describe. Here is a relevant extract from
"http://www.cs.umb.edu/~isotest/isotest.ps":

----------------- begin ------------------

A much more difficult question arose when ORACLE was gearing up to use Snapshot Isolation
(called SERIALIZABLE Isolation in Oracle 7.3 [OR95, OR7.3]) in the TPCC benchmark [TPCC].
The TPCC auditors Tom Sawyer and Francois Raab both communicated to us their thoughts on
being asked to validate the Snapshot Isolation would leave no Isolation holes in the TPCC trans
action profiles. There are several different transaction types present in TPCC (NewOrder
Transaction, Payment Transaction, OrderStatus Transaction, etc.). The benchmark Isolation
requirement for running TPCC does not require serializability of all possible transactions, but
only of the transactions present in TPCC. This is exactly the type of problem the current pro
posal hopes to address: would the lower isolation level (Snapshot Isolation) provide serializable
behavior for the TPCC application? And what method was used to guarantee this? Both Tom
Sawyer and Francois Raab indicated verbally that they, ". . . thought about it really hard for a long
time (days), and ended up confident that there was no isolation problem." In other words, there
was no technique known to them whereby this result could be demonstrated in a rigorous fashion.

---------------- end -----------------

Best Regards,

Vadim

iggy_fernandez_at_my-deja.com wrote:

> 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 Mon Jun 14 1999 - 15:15:15 CDT