Re: oracle - mysql comparison

VC wrote:

> I'll kill two birds with one stone here if you don't mind. Firstly, I'll
> describe a scenario where DB2/MSSQL would *not* need to lock an entire table
> in order to yield a consistent result in SERIALIZABLE IL. Secondly, I'll
> claim that Oracle won't be able to produce consistent data in Oracle
> SERIALIZABLE under the same circumstances.

You already missed the point. Oracle doesn't need SERIALIZABLE or anything else other than a standard SELECT * FROM to give a read consistent view of the data at a point-in-time.

> Imagine a simple flight reservation system represented by a single table
> containing flight numbers and passenger information:
>
> create table PASSENGERS(FLIGHT_NUMBER ..., INFO ...);
> create index P_Idx on PASSENGERS(FLIGHT_NUMBER);
>
> Let's say that a flight can accomodate maximum 50 people. Our transaction
> in pseudo-code might look like this:
>
> set transaction isolation level serializable
> ...
> select count(*) into l_cnt from PASSENGERS where FLIGHT_NUMBER=999; --
> check the number of sold tickets
> if l_cnt < 50 insert into PASSENGERS(999, <info>...); -- if there is a
> vacancy insert a row
> commit;
>
> Under DB2/MSSQL, a transaction will lock only a subset of rows (not the
> whole table) for a given flight thus ensuring consistent results

Good attempt but completely irrelevant to a point-in-time accuracy of a report.

Try this scenario and see what happens.

One bank with multiple accounts. Need to determine the exact amount of money in the bank at midnight tonight while thousands of people are using ATM machines and while deposits, checks, and credit card transactions are beeing simultaneously cleared. The query of the balance in all accounts will take 3+ hours.

Serialize to your hearts content ... you aren't going to do it without a full table lock except in Oracle and, I believe, PostgreSQL and maybe Firebird. And I wouldn't want to be the one trying to run a bank on either of the later.

Daniel Morgan Received on Tue Jul 20 2004 - 00:41:49 CDT