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 READDECLARE 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 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 ----