Re: Row locking and serializability

I would like to collect information on how database engines other than Oracle handle serializability issues. Here are two examples of non- serial transaction histories which are not "serializable" i.e. do not produce the same results (were they to actually succeed) as some serial history of the same transactions. The Oracle database engine permits both these histories. I would like to find out how database engines other than Oracle behave if they were to encounter the histories in these examples. The "orphaned child" example: (The application developer in this example is trying to enforce referential integrity. The source code checks for the existence of a parent record before inserting a child record. Similarly it checks that children do not exist before deleting a parent. This example is featured in the Oracle8 Concepts Manual.) At Time T1, Transaction A determines that a parent record exists. At Time T2, Transaction B determine that this parent does not have any children. At Time T3, Transaction A attempts to insert a child record. At Time T4, Transaction B attempts to delete this parent. 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". To enforce this rule the source code first checks the total number of prior reservations before making a new one.) At Time T1, Transaction A performs the query "select count(*) from passenger_reservations where flight_number=123 and flight_time='2000/01/01 00:00'". 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 attempts to book an additional passenger on the indicated flight i.e. attempts to insert a new record into the passenger_reservations table with flight_number=123 and flight_time='2000/01/01 00:00'. At Time T4, Transaction B attempts to book an additional passenger on the indicated flight i.e. attempts to insert a new record into the passenger_reservations table with flight_number=123 and flight_time='2000/01/01 00:00'. --- 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. Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

iggy_fernandez@my-deja.com wrote: > I would like to collect information on how database engines other than > Oracle handle serializability issues. Here are two examples of non- > serial transaction histories which are not "serializable" i.e. do not > produce the same results (were they to actually succeed) as some serial > history of the same transactions. The Oracle database engine permits > both these histories. I would like to find out how database engines > other than Oracle behave if they were to encounter the histories in > these examples. Yes, I can still remember my reaction when I read these examples in the Oracle docs some time ago. I though the examples were too contrived and laboured (and rather poorly at that). I suspect that their design my be symptomatic of Oracle's locking model. > The "orphaned child" example: > > (The application developer in this example is trying to enforce > referential integrity. The source code checks for the existence of a > parent record before inserting a child record. Similarly it checks that > children do not exist before deleting a parent. This example is > featured in the Oracle8 Concepts Manual.) There are other, possibly better, methods to enforce referential integrity. In the simplest case a correct locking model can be employed. Its not clear from this example whether childless parents are continually scanned, in which case, it may be better at the design stage to determine why such parents may exist and come up with a better model for their maintenance. It may be better, for example, to run a batch process that scans for them after hours when the creation process may not be running. Even Oracle should be capable of this. > The "airline overbooking" example: Another feeble example. But this one just suffers from poor design. > (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". To enforce this rule the > source code first checks the total number of prior reservations before > making a new one.) > > At Time T1, Transaction A performs the query "select count(*) from > passenger_reservations where flight_number=123 and > flight_time='2000/01/01 00:00'". Assume that the result is 99. > Transaction A then assumes that it may book an additional passenger on > the indicated flight. The "select count(*)" creates a lot of overhead which should really be avoided and doesn't provide an adequate solution to the locking problem which is really the crux of the problem. The flight record itself should have a count field showing the number of available seats remaining. This provides a much faster and simpler select than the count(*) approach. It also means that a seat can be reserved thru a simple update on the field where its value is greater than 0. In other words first in first served. The other transaction would fail. Of course, this approach doesn't cover reserving particular seats, but then the example doesn't either, so I won't go into it. -am

iggy_fernandez@my-deja.com wrote: : I would like to collect information on how database engines other than : Oracle handle serializability issues. I suggest you consult Gray & Reuter, _Transaction_Processing_:_Concepts_and _Techniques_ published by Morgan Kaufmann, 1996(?). This is the 'bible' on transaction processing techniques.