Re: Dirty reads with OnLine

On Dec 8, 12:50pm, Extel Inc. wrote: } Subject: 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.. I don't currently have fast access to V5 manuals but its covered fairly completely in the V7.1 Informix Guide to SQL: Tutorial Chapter 7 Programming for a Multiuser Environment - Setting the Lock Mode and other sections. I believe a similar chapter is in the V5 manual. } } >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. Well, unfortunately for you, Informix doesn't provide a way of resetting the default isolation level via an external parameter that I'm aware of. If I understand your problem correctly, this could really be described as a bug within the shrink-wrapped package as it is supposed to set its own environment for the job it's doing when it links to the database. I would really consider that a query tool of this kind should do whatever is necessary to set the database isolation to dirty read for whatever database engine it was linking to. I wouldn't expect an end user to know that they needed to set this up in some external environment. If you are using ODBC then, as you are probably aware, this could also depend on the level of ODBC you were using as early versions were not able to query the engine to find out it's type and therefore what kind of specific command was necessary. By the way I notice that at least V7.1 now implements the ANSI SET TRANSACTION statement as well as SET ISOLATION so that query tools don't have to be aware of the specific DB language syntax. } >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. } } Indeed....but as I say, out of my control. If it were me I;'d be using } Oracle. So what does Oracle provide? I thought it had a similar set of statements to set the isolation level? Reading your early section you seem to suggest that Oracle can provide the error message then the next fetch would get the next row. As that isn't what you would always want the engine to do there must be something the tool would have to do so that it can set the engine to do that? It also seems to me that this would violate the ANSI standard but I'm not sure that I'm reading my ANSI references correctly. In any case wouldn't you expect the tool to get the error message, see it as a problem so aborting the process anyway? 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!