transactions et concurency

hi, i'm trying to simulate oracle behavior on the following point: (from 4js technical documentation) ORACLE uses a multi-version consistency model: A copy of the original row is kept for readers before performing writer modifications. Readers do not have to wait for writers as in INFORMIX. The simplest way to think of Oracle's implementation of read consistency is to imagine each user operating a private copy of the database, hence the multi-version consistency model. for the purpose i create a view on sysmaster:syslock and each sql statement is doing a sub-query with the view. syslock can't be optimized and performance are really poor. ie: select * from table where condition and rowid not in (select rowidlk from viewsyslock where owner<>... and tabname=lower('table')) could it be possible to improve that ? thanks for any idea. regards,

On Fri, 09 Jul 2004 11:25:58 -0400, Jack wrote: HUH??? Just SET LOCK MODE TO WAIT 5; in your code. Most locks are transitory, unless your apps are poorly written, so this prevents lockout errors and maximizes concurrency. If you have long lasting locks it may be that interactive apps are fetching rows FOR UPDATE and holding the lock while the user modifies the on-screen data. This is a poor design. Better to fetch the row without lock then once the users is ready to commit the changes, you reread the row FOR UPDATE effectively locking it then and compare data (usually this involves only comparing a timestamp maintained by a DEFAULT clause and an UPDATE TRIGGER). If the row was modified by another users while this users diddled around, then reject the mods, and possibly display the new row for the user to modify again, and release the update lock on the row. If the row was not modified by anyone else, then you update and commit releasing the lock in a fraction of a second either way. This is known as optimistic locking. Art S. Kagel > hi, > > i'm trying to simulate oracle behavior on the following point: (from 4js > technical documentation) > ORACLE uses a multi-version consistency model: A copy of the original row is > kept for readers before performing writer modifications. Readers do not have > to wait for writers as in INFORMIX. The simplest way to think of Oracle's > implementation of read consistency is to imagine each user operating a > private copy of the database, hence the multi-version consistency model. > > for the purpose i create a view on sysmaster:syslock and each sql statement > is doing a sub-query with the view. > syslock can't be optimized and performance are really poor. ie: select * > from table where condition and rowid not in (select rowidlk from viewsyslock > where owner<>... and tabname=lower('table')) > > could it be possible to improve that ? > > thanks for any idea. > regards,

Jack wrote: > hi, > > i'm trying to simulate oracle behavior on the following point: > (from 4js technical documentation) > ORACLE uses a multi-version consistency model: A copy of the original > row is kept for readers before performing writer modifications. > Readers do not have to wait for writers as in INFORMIX. There's SET ISOLATION TO DIRTY READ, as well as the lock wait mentioned by Art. I think the Oracle model can still be circumvented; although I need an engine and an inclination to test my theory...