Re: Adjacent key locking on AIX4.2

MarkDenham@aol.com wrote in article <5o7tdm$gov@cssun.mathcs.emory.edu>... > All > > Environment: > IBM SP2 > AIX4.2 > Online V7.14 UD3 > All tables row locking > > We are seeing a number of -243/-154 errors which I am surprised by since we > have > lock mode wait set to 10 secs. Errors only seem to occur during periods of > heavy > activity. > > I have seen a number of comments about adjacent key locking on IBMs, can > anyone confirm that this feature still exists on this platform? > > Are there any other peculiarities that might lead to seeing these errors? > > Anyone got any good monitoring utilities for tracking these types of events? > They > occur when nobody is around to watch them.... > > Mark > MarkDenham@aol.com (home) > Denham.M@amstr.com (work) > Adjacent key locking should not be a problem in any version of online 6.x or greater. It is more likely that besides inserting new rows, your transactions are updating rows, perhaps in a control table. A typical example would be a control table with a row which is used to hold a counter to implement a "serial-like" column in the main transaction table. (The main reason you would do this rather than use an actual serial column would be to insure a contiguous numbering sequence with no gaps.) FourGen is an example of an application that does this a LOT. The problem comes in when the counter in the control table is incremented within the "begin/commit work" of the transaction at a point where the transaction still has quite a bit of processing left to do. The result is that the "counter" row is locked for the remainder of the transaction. What you want to watch for are locks with waiting sessions that exist for more than a few seconds at a time. I have a script called "onlocks" which will allow you to do this type of monitoring, plus several other options. It is available at http://www.objectsoft.com. (There is a more recent version with a few additional enhancements -- especially for watching for locks with waits -- which I have not yet posted on our Web site. Let me know if you'd like a copy and I'll e-mail it to you.) HTH -- Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com