Re: Locks/Committed Read Problem

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 READDECLARE 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. June ---- June Tong Informix Software ---- ---- Senior Consultant (415) 926-6140 ---- ---- International Support junet@informix.com ---- ---- Location-du-jour: Oakland ---- * * Standard disclaimers apply * - Please do not send me requests/questions by mail. When I have the knowledge - and time permits, I try to answer questions on comp.databases.informix, but - travel schedule, time, and volume make responding to personal requests - difficult and often slow. Please call your local Informix Technical Support - organization for assistance with technical issues.