Re: Dirty reads with OnLine

jgordon@us.DHL.COM (Jim Gordon) writes: >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. Where is it defined in the docset as a matter of interest? I can RTFM as well as the next man but maybe not good enough.. >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. No I can't. And I know what DIRTY READ does. The whole point is I HAVE to use DIRTY READ with Informix as I can't guarantee someone else hasn;t locked something with a bad app. design for too long. This is in a shrinkwrapped product that reads gobs of other people's data. I can't complain to MIS.. And with Informix if I hit a -244 I'm dead meat. Just loops, no row level status and move on. >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. Indeed....but as I say, out of my control. If it were me I;'d be using Oracle.