RE: update impossible in spite of row locking

Please post query plans for both sessions Regards Colin There are 10 types of people in the world, those that understand binary and those that don't > Date: Tue, 23 Jun 2009 12:14:31 +0200 > From: tilleul17@web.de > To: RHabichtsberg@arz-emmendingen.de > Subject: Re: update impossible in spite of row locking > CC: informix-list@iiug.org > > Habichtsberg, Reinhard wrote: > > Hi all, > > > > we have a strange behaviour of IDS while trying to update rows. IDS Version > > 11.50.FC3 and 9.40.FC9. > > > > The table has row locking, unique index and primary key. The database is in > > logging mode buffered. > > > > We open two sessions: In the first session we open a transaction and update > > a certain row selected by the primary key. The transaction is not yet > > commited. > > > > In the second session we try to update certain other rows as well > > selected by the primary key. Some rows could be updated. But with some rows > > it appears following error: > > 244: Could not do a physical-order read to fetch next row. > > 107: ISAM error: record is locked.> > > > Why does this happen? We assumed that row locking would only lock the one > > row of session 1. But obviously the update statement of session 2 is blocked > > by a lock. > > > > How can we avoid such a situation (it simulates that a user session is in a > > transaction while the user isn't able to finish it). > > > > May be we are understanding sonething wrong, but our software developer > > affirm that many of our programs depend on the ability of DBMS to update > > several rows of a table simultaneously even though a transaction on the > > table hangs. > > > > Can anybody help? It's rather urgend. > > > > TIA, > > Reinhard. > > Is the PK fully qualified for the rwos selected by the second session? > If the second session updates a range of rows , it might well be, that > the row updated by the first sessions needs to be evaluated. > > -244 sounds as if the second session might be doing a full table scan > (physical-order read). > In case Session 2 does a full table scan, try an update statistics + > distributions for key columns, to see whether it then takes the PK index > to access its rows. > > You could set Lock wait mode to wait indefinitely , but the down side is > of course that wait situations and deadlocks may occurr. > > You might also set a trap for -244 ('onmode -I 244' ) and look at the > generated af file (use 'onmode -I' to unset the trap) > (preferrably you do this in a test environment, not production) > > With kind regards > Tilman Model-Bosch > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ With Windows Live, you can organise, edit, and share your photos. http://clk.atdmt.com/UKM/go/134665338/direct/01/