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

K Lonz writes: >Subject: INDEX LOCKS are creating DEADLOCKS > Index Lock conflicts > >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. If that is the case, and the cause, alter your table to use row locking. > > The error messages are ISAM 154 and isql -271, -245 Unless you are using I-Star and performing distributed updates, the -154 is a bit of a misleading error. More on this in a bit. > >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. There is no such thing as a deadlock timeout on local transactions - it applies only to distributed transactions where a deadlock cannot be truly detected. For local access, a deadlock is aborted as soon as it is detected (actually more technically, a deadlock is not allowed to happen - the transaction is aborted when the server realizes that granting a lock will result in a deadlock). Also note that deadlocks can only happen when you choose to wait for locks (SET LOCK MODE TO WAIT [##]). > 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? If the table is created using (or altered to use) row locking, both rows and indexes are locked at the "item" level (ro rows, an "item" is the row, for indexes it is the key/pointer entry in the b+tree). > 3. We know that solution #4 above is quirky, but we > would like some feedback on it if there is any out > there. Based on the error message you are getting (-154) and assuming that you are *not* doing distributed queries (you didn't mention that you are), then the cause of your problem is likely that you have used SET LOCK MODE TO WAIT ##, i.e. you are waiting on locks with a timeout. The length of the transactions are causing that timeout to be exceeded, so the waiting transaction aborts. To cure this either increase the WAIT time, or WAIT with no timeout (SET LOCK MODE TO WAIT). By the way, if you were encountering a REAL deadlock, you would be receiving ISAM error -143. On -154: Unfortunately, the -154 error was used for two different events that are siliar from a low-level perspective, but very different from a user's perspective. The first, and the one the "message" really applies to, is when a distributed transaction waits "too long" to acquire a lock on a remote server. Since it cannot interrogate the remote server's shared memory to detect a deadlock situation, it assumes this "too long" wait indicates a deadlock situation (this wait time is tuned via DEADLOCK_TIMEOUT in tbconfig). The other is, as I have mentioned above, when you specify to wait for locks with a timeout, e.g. SET LOCK MODE TO WAIT 30, which will wait on a lock for 30 seconds, then return an error if it doesn't get the lock in that amount of time. This is not because of a deadlock, potential or otherwise, since the lock would not be granted if a deadlock would result, but rather a user choice to give up waiting for the lock after a certain amount of time. Both return the same error code (-154). I hope this is of some help. Dave -- Disclaimer: These opinions are not those of Informix Software, Inc. ************************************************************************** "I look back with some satisfaction on what an idiot I was when I was 25, but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney