Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: oracle - mysql comparison

Re: oracle - mysql comparison

From: VC <>
Date: Tue, 20 Jul 2004 14:47:31 GMT
Message-ID: <7gaLc.143279$Oq2.98704@attbi_s52>

"Daniel Morgan" <> 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

Original text of this message