Re: update impossible in spite of row locking

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

Hi Reinhard, is it a small table so the query optimiser chooses a full scan although you have indexes and update stats up to date? If this is the case, then give optimiser a tip: select {+AVOID_FULL(table_name)} * from table_name where column_name = <some_value>; and the problem should go away. HTH Davorin