Re: Locks/Committed Read Problem

All this is clear and simple. However it does leave some problems. Assuming all locking (all transactions) are done in code that is as high speed as possible there should be no significant problem in simply using committed read and set lock mode to wait or wait some seconds. Every row would eventually be returned (whenever the transaction committed). If you ever timed out on the wait you either got a deadlock situation or (with wait some seconds) you got a transaction that toulk longer than it should. Sometimes it *may* however be necessary to hold a lock over some indeterminate time, or you run an application over which you have no control (someone else wrote it) and it keeps locks unnecessarily long. In these cases it seems to me that something like a versioned read option would have been not only nice, but actually necessary to get some types of report data out of a bussy database. A versioned read would require the database to keep the last committed version of everything available, and you would have an option to allways read that. Using dirty read in this situation isn't allways very helpfull as you would get dirty data which might not be acceptable. June's solution of course isn't helpfull in this situation either, as you get no data for the locked rows, not the last version. Also if you want to read the data using a report generator even this option may not be available to you. Of course versioned reads may have it's share of problems as well, but it would still be interesting to know if Informix is doing anything along these lines. We do see this as a problem, and competitors seems to be bragging about their versioned read capabilities. Anybody (June) know anything about this? PS: Just a small thing to June: Accepting that the manuals don't state things clearly, and referencing a course instead isn't very acceptable. We actually demand that Informix states everything in their manuals, and doesn't require us to take courses to find out how their products works. We'll accept that it's short and you will have to know a lot about databases to understand the manuals fully, but everything must be there. I don't think this is too high a demand from professional customers of a professional supplier. We haven't had the time to take the courses as we have to read and study all the manuals anyway, and have so far understood most things from them. And a PS to the PS: I do think these things are explained in the manual, so the above was only a general pointer to how things should be. junet@informix.com (June Tong) wrote: :Mark Oberfield (oberfiel@thunder.nws.noaa.gov) wrote: :: To Weimin Yang :: I tried indexes on the tables and got the same result. Oh well, it was :: worth a shot. . . :Weimin Yang's solution of adding an index on the table would help if you :were not actually trying to read the locked rows, but were being blocked :by rows you don't really care about during a sequential scan. :: This IS helpful, but it distressing nonetheless. So when isolation :: mode is set to committed read and I try to read the entries in the :: table, the server can NOT **skip** those rows which have a lock on them :: (i.e. not committed yet). :: In other words, I cannot get the committed rows (99.99% of the table's :: contents) because there is a single row which is part of a transaction :: which hasn't been committed yet. The server will wait until the lock :: is released or the fetch statement exceeds the number of seconds to :: wait and I get the errors that I have been getting. How depressing. :: I was hoping that I forgot something when I switched the logging mode :: of the database. :This is true, as it relates to a single cursor using isolation COMMITTED :READ or higher. The defined behavior is to retry the locked row on the :next FETCH, not to skip over the locked row. :However, you can get your desired behavior if you combine DIRTY READ with :COMMITTED READ. Declare a cursor with isolation DIRTY READ to read the :key value, and then use another SELECT (or cursor) with COMMITTED READ to :test the lock status of the row. Somethine like: :SET ISOLATION DIRTY READ :DECLARE c1 CURSOR FOR SELECT key-column FROM tab :OPEN c1 :FETCH c1 INTO key-variable :WHILE SQLCODE = 0 : SET ISOLATION COMMITTED READ : SELECT * INTO variables FROM tab WHERE key-column = key-variable : IF SQLCODE = 0 THEN : DISPLAY variable : SET ISOLATION DIRTY READ : FETCH c1 INTO key-variable :END FOREACH :If the row is locked, the SQLCODE returned will be -107, but the next :FETCH will go to the next row (since it is using DIRTY READ). :: The INFORMIX manuals don't clearly state this as you have done :: (Tutorial 5.1, pg 7-25). . . . :Perhaps not, but it is covered in much more depth in the course :Managing and Optimizing Informix-OnLine (DS) Databases. Nils.Myklebust@ccmail.telemax.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company