ADJACENT KEY LOCKING

One of our customers recently upgraded from Standard Engine to Online version 5.02.UC6, and they're now frequently experiencing locking errors. I suspect that these are due to the key locking strategy used by Online, even though the system misleadingly returns error number -154 (Deadlock Timeout Expired - Possible Deadlock). (Note: I suspect the Deadlock error code is spurious because deadlocks supposedly only happen in multiple-server environments. The customer in question has only one dbserver. Also, the error returned after only 5 seconds, which is a lot less than the 60 seconds specified in TBCONFIG for DEADLOCK_TIMEOUT.) I'm trying to understand how adjacent key locking works, but my test results don't agree with the ENGREL_5.0 documentation, nor the FAQ located at http://ww.garpac.com/informix.html. The FAQ states: > Another side-effect of adjacent key locking is reduced concurrency of > insertions. If Px inserts the key value 9 into a unqie index, Py will > now receive a locking error if it attempts to insert the value 8. However, I tried the following: Px: create table mytab ( Px: col1 integer not null, Px: col2 char(10) not null Px: ); Px: create unique index mytab1 on mytab(col1); Px: begin work; Px: insert into mytab values (9,"ABC"); then Py: begin work; Py: insert into mytab values (8,"DEF"); According to the FAQ and the Informix release notes, the insert attempted by process Py in the above test should fail, but it succeeds quite happily. The Informix release notes (ENGREL_5.0) contain the following: > Right > Current key Adjacent key Released when ... > -------------- --------------- --------------- > SELECT w/ > COMMITTED READ S lock test n/a > CURSOR STABILITY S lock Fetch Next > REPEATABLE READ S lock Commit/Rollback > INSERT X lock X lock test Commit/Rollback > DELETE X lock test X lock Commit/Rollback > > Note: S = Shared (compatible with other shared locks) > X = Exclusive (incompatible with all other lock types) > test = never really acquired but will detect the presence of > an incompatible lock After conducting various tests, it seems to me as though DELETEs behave as described, but INSERTs do not. The release notes continue: > an insert will encounter a lock if the key value to the immediate > right of any of the keys in the new row has not been committed. which I believe is false. An insert will succeed if the right adjacent key value is an uncommitted insert. Am I missing something? Any info would be much appreciated ... -- ______________________________________________________________________________ Steve Chell steve@nezsdc.fujitsu.co.nz Fujitsu NZ Ltd, Auckland, New Zealand FAX: +64.9.3564851