Re: Informix vs. Oracle

I've believe I have fact that Oracle lock all rows associated with update cursors at the open of the cursor. The way I've done this is by coding a simple PRO C program that creates an update cursor and running multiple instances of it on overlapping data rows. If there is ANY overlap in the data rows multiple programs the first program will be allowed to open its update cursor while the others will have to wait until the transaction is complete. Someone posted the fact that others can still read the data while the update cursor is in progress. This is in fact true. Even if a row gets updated others will still be able to see the uncommitted version of the row. In Informix this would create an exclusive lock on the row (assuming row level locking) and even reading will be denied (unless the isolation level is set to dirty read - in which case the dirty reader would see the updated value). -Bill } } >From: Nils.Myklebust@idg.no (Nils Myklebust) } >Date: Wed, 20 Aug 1997 18:39:53 GMT } >X-Informix-List-Id: <news.41885> } > } >ennis@ssax.com (Bill Ennis) wrote: } > } >:What about update cursors in Oracle? Oracle will lock EVERY row } >:associated with an update cursor when the cursor is opened! Informix } >:will lock the row when it fetches. In this case Infomrix is better } >:for transaction independency. } > } >Such behaviour would have been a total showstopper for us. } } It would be for most people. I'm not at all sure that this is an accurate } characterization of Oracle's behaviour, though. Specifically, as far as I } understand it, no rows are locked when a cursor is opened. As rows are } modified, the new image is added to the table, but other transactions are } allowed to read the old images for the rows. } } Somewhere along the line, a transaction can be aborted if it has seen old } data when it should have seen new data and it made changes to the database. } This conflict resolution is done up front (pessimistically) by Informix's } locking, but it has to be done at commit time under the optimistic } versioning systems such as that used by Oracle. I'm not sure of the in's } and out's of Oracle's conflict resolution, but I don't think 'every row is } locked when an update cursor is opened' is an accurate description of it... } } Any Oracle buffs out there who can confirm or deny or clarify what goes on? } } Yours, } Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> } PS: Warning I do not reply to messages with anti-spam in the return path. } -- Bill Ennis Voice: 312-474-7516 SSA Fax: 312-474-7460 500 W. Madison email: ennis@ssax.com ennis@accesschicago.net