Re: Reading over locked rows

} } Joerg wrote: } : } i've a program which unload tables to an ascii file and updates a specific } : } column in the rows after they are written to the file. } : } } : } Isolation mode is set to repeatable read to avoid rows to be written to the } : } file which are not committed. } : } } : } Everything was fine but if there are open transactions over some rows of a } : } table i got repeated errors in the range -242 to -246 (depends if the table is } : } indexed or not) until all transactions for this table are completed. Is there a } : } way to "jump" over the locked rows? } } Michael Strother (mstrothr@informix.com) wrote: } : Yes use Dirty Read Isolation level, this does negate the Repeatable } : read stuff but that is what you are asking when you ask to ignore locks. } : If you want to skip the records that are locked set lock mode to not wait } : and fetch the records testing for a lock...if you get one of the codes } : you indicated ...ignore it and fetch the next record. } } Well, sort of. I'm not sure exactly what Michael had in mind here, but if } you want to avoid rows which are not committed, you will need to use } DIRTY READ in conjunction with something else, like COMMITTED READ, and do } two SELECTs. } } I haven't tried this, but here's an idea: } } SET ISOLATION TO DIRTY READ } DECLARE c1 CURSOR FOR } SELECT ROWID FROM tabname WHERE { whatever your criteria are } } ORDER BY ROWID } } FOREACH c1 INTO intvar } SET ISOLATION TO COMMITTED READ } SELECT <cols> INTO <vars> FROM tabname WHERE ROWID = intvar } IF sqlca.sqlcode = 0 THEN { row is not locked or deleted } } { process as desired } } END IF } END FOREACH Problem Here....You must stop using ROWID, it goes away in 7.0 and above use the UID instead. } } The only problem I can think of off the top of my head is that if someone } deleted your rowid after you selected, and someone inserted a new row which } re-used the rowid, you could end up with a row which did not match your } criteria. A relatively remote possibility, I suppose (?), but you could } re-iterate your original criteria on the 2nd SELECT to avoid that too. } } Again, this is all off the top of my head, and untested, so caveat emptor. } } June } } ---- June Tong Informix Asia/Pacific ---- } ---- On-Loan Engineer Singapore ---- } ---- Location-du-jour: Bangkok ---- } ---- junet@informix.com (65) 298-1716 ---- }