Dirty reads & Isolation Levels

[DGRFL]

Hello,
I am porting a DB/2 application (ISAM based access) towards Oracle SQL (poor me...)

"The original application allows dirty reads : process A updates the database but does not yet commit the transaction. Process B can already retrieve the new information."

I know it sounds silly that we want to emulate this, but we actually need this functionality.

has anybody a suggestion ?

Regards,
Luc

[chrisrlong]

Sorry, but there is no easy way that I know of. Basically, it is not possible to do a dirty read in Oracle, under any of the isolation levels.

Therefore, you would have to come up with some elaborate scheme to maybe update some temp tables, and have people read from those, but only update the real table when you are ready. I cannot even imagine the complexities that something like this would introduce. I would seriously reconsider the necessity of this particular functionality, or the necessity of switching to Oracle :), because the two don't mix.

Sorry,

- Chris

Anyone else out there have any ideas?

[tamilselvan]

If DB2 allows 2nd process to read uncommitted data of the 1st process, better not to use DB2.

[chrisrlong]

Well, just as an aside, it *is* the definition of certain isolation levels that such functionatlity exist. The READ UNCOMMITTED isolation level, as defined in the ANSI SQL-92 standards allows such functionality. It is simply that Oracle does not support said functionality (same as it does not yet support the ANSI-92 join syntax, although that is slated for 9i, so I hear). SQLServer also supports this isolation level (at least in 6.5 it did :) ).

- Chris