Re: Online Row Level Locking

> The problem seems to be errors relating to the fact that Online locks > adjacent rows in a table when a row is locked. Does anyone have any > suggestions or comments about the best way for the applications to work > around this? There is considerable activity occurring on the system, and > the potential for lockouts happening is high. Any thoughts would be > appreciated. Additional details as soon as I get them (The system is in > Cincinnati and I am in Wash.DC). Thanks. > > Rory Martin This is a known design issue with Online. In V5.02 and below attempts to delete a row or update keys in a row will place locks on the next higher key value on each index on the table for the old key values. This is done so that anyone attempting to insert a key with the same key value is refused until the transaction in progress is completed as the transaction may need to be rolled back. The fault in this approach is that it doesn't just lock the key value being changed/removed it can lock many values not affected by the transaction. For example with the following numeric index value entries 5 50 100 If 50 is deleted a lock will be placed on 100 and all values 6-100 will be inaccessible until the transaction completes. As you can see the problem gets bigger the larger the index value spread is and with how many indexes you have on a table. Unfortunately there is no way around this except to take it into consideration when the system is designed. My understanding is that in V6 the locking strategy is changing and Informix are extending the locking mechanism to be able to hold locks on values that don't currently exist in the indexes. This means that V6 will be able to hold a lock on 50 even after the index entry has been removed. The lock tests will also change, instead of testing that the next higher key is not locked on an insert of a new or updated key, Online will test whether the index value is locked. Cheers - Jim My opinions are my own. They may vary with time but they remain MINE! ---------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 375-5222 (Work) Address: 700 Airport Blvd. #300 (415) 775-7762 (Home) Burlingame, CA 94010-1937 Fax: (415) 375-5019 ----------------------------------------------------------------------