Re: Reading over locked rows

Subject: Re: Reading over locked rows From: Eddie Hulme <ehulme@ironbdge.demon.co.uk> Date: 24 May 1995 18:25:04 +0100 Message-ID: <85978479wnr@ironbdge.demon.co.uk> In article: <2f9be823@aventure.teuto.de> Joerg.Spilker@p0.f0.n0.z0.aventure.teuto.de (Joerg Spilker) writes: > > Hi, > > 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. Repeatable read does more than assure that you don't get uncommitted rows. It also places a shared lock on *all rows examined by your server process*, until your transaction is committed. *NO* other processes can update or delete rows you've read. You could try a less intense isolation level, such as cursor stability if that suits your needs. Cursor stability will only lock one row at a time, and as you fetch the next row, the lock on the row you fetched previously is released. > > 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? > You didn't mention what the programs getting the -242 to -246 are trying to do? Updates or deletes maybe? If the other user programs "set lock mode to wait", and you used cursor stability for an isolation level on the program that is unloading the rows, hopefully you can avoid a conflict. The Informix-SQL tutorial has a pretty good discussion on isolation levels. Regards, J. Dority >>I am using 5.01 and am getting exactly the same problem when 2 or more >>users >>access the same table. ie 246. It seems that Informix is still locking the >>index of an adjacent row. The help message in finderr is not correct and >>this is a definate bug. >>I seem to remember that this bug was present in 4.10. >> >>I also would be most interested in a workaround. Will gather together more >>details of the testing we have done and post. -- >>Eddie Hulme