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

Subject: INDEX LOCKS are creating DEADLOCKS Index Lock conflicts Background: We're running Informix 4.1 on an Amdahl UTS. We're enhancing and modifying a system which was originally developed and used on 2.1. Problem Summary: 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 Problem (Step-by-step): 1. User 1 begins a Multi-Statement Transaction (mst) which takes several minutes to perform, and navigates through several screens. Note: The (mst) modifies and adds data in a row-locked table, indexed on sequentially assigned integers. 2. User 1 acquires an index lock (in a transaction) which will last several more minutes. (tbstat -u, flag 2 changes from B to T). 3. User 2 begins another (mst) to add/modify data in the same table. The record has the next sequential number (or a very close number). 4. User 2 attempts to do work but waits for the index lock to be freed up from User 1. (The add/modify row is different) 5. User 2 aborts after the deadlock time limit is exceeded. Desired Behavior: The users must be able to add/modify records without encountering the deadlocks. We would like to retain the (mst)'s and not implement a major revision of the code. Possible Answers: 1. Ignore the problem - obviously unacceptable. 2. Disable the indexes - :( - also unacceptable. 3. Rewrite application without (mst)'s - not enough time and undesirable at best. 4. Allocate integers non-sequentially in the hopes of not running into the page locks. (We know - we're grasping!) Can anyone help: 1. Most Importantly: Does someone have an alternative solution? 2. Do indexes use page locking exclusively or can indexes be 'tuned' to use row locking? 3. We know that solution #4 above is quirky, but we would like some feedback on it if there is any out there. Thanks in advance! -- Michael Klonecki Life is a gift. 1391 S. Argonne Circle Aurora, CO 80017-4418 Choose wisely what to klonz@copper.denver.colorado.edu do with your gift.