From: cortesi@informix.com (David Cortesi)
Newsgroups: comp.databases.informix
Subject: The twin problem (was Re: HELP - Index Locks are creating DEADLOCKS)
Date: 11 Nov 92 20:35:38 GMT
Organization: Informix Software, Inc.

In article <OLIVER.92Nov10221419@snoopy.infix.de> oliver@infix.de (Oliver Okrongli) writes:
>>>>>> 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 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).

Purely a side issue, but this is in general not true. Error
numbers are very stable.  Sometimes an error number is retired
because the condition no longer occurs. Often new error
conditions are added, and these sometimes re-use the error
numbers of old, no-longer-occurring, conditions.  For example
in version 5.0, some of the error conditions related to the
new referential integrity features re-used error numbers that
had been retired effective with version 4, 2 years earlier.

But as long as the error cause is the same, the number is the same.

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

Sibling problem, twin problem, same thing.  The essence is that an
index key has been changed (updated or deleted) as part of a
transaction which is not yet complete.  Since the transaction could
still be rolled back, there has to be some way to reserve the original
key value, so that no other transaction will attempt to use it.
However: the original key value is no longer in the database.

The method used is to place a lock on the next higher index entry,
and to require any transaction that would insert a value to 
acquire a lock on the adjacent key.  This ensures that no other
process can insert the original key until the first transaction
completes.

Unfortunately it can also prevent another transaction from
inserting an unrelated key that merely happens to fall in the
same span of existing keys.

Note: this was documented in an issue of Tech Notes sometime in 1988
(I remember because it was one of the first pieces of tech writing
I did at Informix :-) The twin problem is not a new issue!

>  The officially assigned case
>number at Informix is 11473.  We have been told that there is no
>schedule to correct this problem.

As Dave Kosenko posted here earlier, a different mechanism will
be used effective with version 6.0.

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

The "lesser extent" has to be purely a matter of luck and timing, since
the logic is the same in all engines.

>We do consider these effects extremely undesirable because they lead
>to unpredictable application behaviour.  We are currently not aware of
>any workaround.

There is no basic difference between a lock conflict based on the twin
problem and one caused by two processes trying to update the same row.
Both result from two processes attempting to seize the same resource at
the same time.

It seems to me in my ignorance that this lock conflict could be
handled exactly like a lock conflict from any other source.
You could either
	* run with SET LOCK MODE TO WAIT
or
	* when any operation discovers a lock conflict, roll
	  the transaction back and retry it.

Is this not so?

Dave Cortesi
  cortesi@informix.com