update impossible in spite of row locking

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.

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. What is the table schema? What is you update statistics strategy? What is the query plan?

> From: theBP@Usenet-News.Net > > Can anybody help? It's rather urgend. > > > > TIA, > > Reinhard. > > What is the table schema? > > What is you update statistics strategy? > > What is the query plan? > _______________________________________________ Well I think it could be query plans. The OP didn't say how many or which applications were hitting the database. Based on personal observations, I'd say that a majority causes for bad performance is due to poor table design and poor application design and coding. No offense to Lester and his "Fastest DBA" contest(s), but truly bad programming logic will hurt you more. Using a hotel reservation system as an example... Suppose you're writing an app where the user wants to rent a hotel room in NYC. Hyatt has several properties. A really, really bad design would be to start a transaction here and lock the hotels for the duration of the transaction. Even if you select a hotel room and then lock that row, while you search other properties, you still had trouble. You're still holding a very long transaction. A better idea would be to start a 'logical programatic transaction' by putting a hold on a room that met the user's requirements, and continued with the search. At the end of the 'logical programatic transaction', you either have a session timeout which would then release the rooms back to the vacancy list, or the user selects a room, and then releases the holds on other rooms at other properties. (Again, I'm going from memory of a presentation that Dana L. gave.) If you were around in the early 90's and knew any of the consultants in Chicago at the time, you couldn't miss her. ;-) Along with Johnny W., Eric O, Pete C, Stephan B, El Stubbe, Mark J. and a couple of others that I'm probably missing. ... So until Reinhard shares more information about the application(s), we probably can't really help him. But hey! What do I know? I'm an app developer so my first choice of where to look is in the application. -G _________________________________________________________________ Bing™ brings you maps, menus, and reviews organized in one place. Try it now. http://www.bing.com/search?q=restaurants&form=MLOGEN&publ=WLHMTAG&crea=TEXT_MLOGEN_Core_tagline_local_1x1