The twin problem (was Re: HELP - Index Locks are creating DEADLOCKS)

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