Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: oracle - mysql comparison
"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
news:1090302131.661893_at_yasure...
> VC wrote:
>
> 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.
>
Well, the example I provided was a response to the two statements you've
made:
1. DB2/MSSQL have to lock the entire table in order to produce a consistent
result in SERIALIZABLE IL . (No, they have not)
2. Oracle query will produce a 'read-consistent' result. Yes, Oracle
will indeed produce a snapshot of the data ('read-consistent' result) as of
the point in time the transaction started. The problem is that the snapshot
itself may be inconsistent due to concurrent update/insert transactions (see
my example).
> 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.
As I've already mentioned several times, no one disputes the fact that in certain cases Oracle provides higher concurrency due to MVCC. I also said that there are several solutions to the reporting problem in locking databases, such as a replicated or stand-by database. There is another solution, namely triple mirroring of the OLTP database. SAN vendor harware can "split off" the third mirrored drive set creating almost instantaneously a clone of the original database (e.g. EMC BCV) at a given point in time. It's interesting to notice, that the same technique is widely used for Oracle databases as well in order to off-load the main instance. The clone is used both for reporting and backups.
>
> Serialize to your hearts content ... you aren't going to do it without
> a full table lock ...
As I've demonstrated, only a subset of rows involved in the transaction has to be locked which naturally can be the whole table. Received on Tue Jul 20 2004 - 09:47:31 CDT