Re: Dirty reads with OnLine

On Dec 5, 5:18pm, Extel Inc. wrote: > Subject: Re: Dirty reads with OnLine > tonytd@ttyrwhit.demon.co.uk (Tony Tyrwhitt-Drake) writes: > > >In article <49d3rv$fg3$2@mhafm.production.compuserve.com>, Tim Porreca <72560.460@CompuServe.COM> says: > >> > >> > >>As a recent SE convert, I am having trouble migrating to OnLine. > >>What I want (NEED) is row level locking and dirty reads. Just > >>like SE. Problem is I can't seem to make it happen. > >> > > >In 5 the default locking level is page and the default > >isolation level for a non ansi database is committed read. > > >Create tables with lock mode set to row or alter them to row > >level locking. Syntax is > > >CREATE TABLE tabname ( field1 char(3) etc ) lock mode row;> > >or ALTER TABLE tabname lock mode (row); > > But as I just found out, if you do COMMITTED READ or above, you'll > loop on a -244 SQLCODE if any update locks are around. Nice .. >-- End of excerpt from Extel Inc. This is the defined and correct behaviour. You can use SET LOCK MODE TO WAIT or SET LOCK MODE TO WAIT 10 to give the lock a chance to disappear, but the reason you use committed read and above is to guarantee that the data in the database is commited. If someone is currently changing it but has not yet committed it you really don't want to use the changed data because a rollback of the other transaction may occur invalidating what you are doing. Dirty Read is intended for when this is not an important issue such as most reports. You do run the risk of making judgements based on inaccurate data. If you really wanted the data that was previously in the table before the update started you need an audit trail or you need to buy a database that handles versioning. Generally though this is rarely a requirement and there are always workarounds such as log tables. I doubt that you want the database to skip these rows! Cheers - Jim -- ----------------------------------------------------------------------------- Jim Gordon DHL Airways Inc. jgordon@us.dhl.com ----------------------------------------------------------------------------- My opinions are my own. They may vary with time but they remain mine!