Re: *** HELP - Index Locks are creating DEADLOCKS ***

>>>>> On 7 Nov 92 14:50:31 GMT, klonz@copper.denver.colorado.edu (K Lonz) said: > We are experiencing a deadlock and subsequent abort with users > who are adding/modifying data in the same table. The table is indexed > on sequential numbers. The users are adding/modifying data records > where the sequential numbers are close to each other. We suspect that > the indexes are using page locking - which is causing our problem. > The error messages are ISAM 154 and isql -271, -245 We are also facing similar locking problems which may or may not be related to yours (error numbers tend to vary between different releases). Our number 1 problem is: when a row gets locked sometimes rows next to it are locked also. This leads to unpredictable locks especially on installations with a larger number of active users. We talked to our Informix distributor providing support here in Germany. They said the problem is known at Informix as the 'sibling problem' but is considered a feature. The officially assigned case number at Informix is 11473. We have been told that there is no schedule to correct this problem. The problem has been verified to occur on the following engines OnLine 4.0 on i486, OnLine 5.0 on HP9000/700 and to a lesser extent also on SE 4.0 on i486. With OnLine we use row level locking exclusively. We have verified that page level locking results in a higher failure rate. SE uses row level locking exclusively. The following code fragments should confirm this (using OnLine) Step 1: Create our test database: create database test with log; create table tab1 ( col1 integer, col2 integer ) lock mode row; create index i1 on tab1 (col2); insert into tab1 values (1, 1); insert into tab1 values (2, 2); Step 2: Lock row (1, 1) with this update cursor: declare uc cursor for select col1 into $col1 from tab1 where col2 = 1 for update of col1; Step 3: Start a second process using the following update statement: update tab1 set col2 = -col2 where col1 = 2; This will produce the following error (OnLine) although row (2,2) should be still unlocked: -244: Could not do a physical-order read to fetch next row. -107: ISAM error: record is locked. Step 4: Now repeat steps 1 to 3 replacing the index statement with: create index i1 on tab1 (col1); No error should occur. Unpredictable locks with the same consequences as noted above are generated by DELETE statements locking the `next, or adjacent key, value in the index' as documented in OnLine literature. We do consider these effects extremely undesirable because they lead to unpredictable application behaviour. We are currently not aware of any workaround. We would appreciate any kind of recommendations - especially from within Informix US (Alan are you listening?) -- Oliver Okrongli infix Software-Systeme GmbH Phone +49 531 238090 Rebenring 33 Fax +49 531 3801152 oliver@infix.de D-W-3300 Braunschweig F.R. Germany