From: oliver@infix.de (Oliver Okrongli)
Subject: Re: *** HELP - Index Locks are creating DEADLOCKS ***
Organization: infix Software-Systeme GmbH
Date: Tue, 10 Nov 1992 21:14:19 GMT

>>>>> 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