Re: Article about supposed "murky" future for Oracle

From: Serge Rielau <srielau_at_ca.eye-be-em.com>
Date: Tue, 30 Mar 2004 17:35:20 -0500
Message-ID: <c4cso4$hqu$1@hanover.torolab.ibm.com>

Daniel Morgan wrote:
>

>> Instead of SCROLL CURSOR, a TEMP TABLE can also be created for same.

>
>
> And what happens during the time the temp table is being loaded?
> Same thing that happens in SQL Server. Surely you are not advocating
> a full table lock for as long as it takes to load a temp table.
>

There is no such thing as a free lunch. The versioning comes at a cost too. Pushing a version and managing the rollback segments is not for free. Neither is finding the appropriate version for each row. The concept works well if the stack is fairly small. Most rows will not change and hence the overhead is limited to update, delete and insert operations. Interestingly the sweetspot for isolation levels that wait on uncommited data is also that most data does not change and thus waits are rare.

You can look at it from another angle:
If I need to look at a row and it is currently being updated. How do I gamble? If I presume the update will commit then looking at old data will result in a decision being made on stale data. That can be bad. On the flip side, I can read uncommited data in most RDBMS and go wrong when the transaction on which's success I depend rolls back. I wouldn't want to state one isolation level is better than the other.

The only truly safe isolation level is serializable (which matches Repeatable Read in DB2 I think) any other isolation level has drawbacks that the app has to be aware of.
AFAIK e.g. Informix has a "skip uncommitted" mode where it conveniently ignores rows that are in limbo. Dangerous? Yeah. But writers don't block readers and it surely is good enough to find free seats on an airplane :-)

Cheers
Serge

PS: I always thought WallStreet was/is a Sybase Stronghold obviously Banks could live with what Sybase has to offer.

-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Received on Tue Mar 30 2004 - 16:35:20 CST