From: davek@informix.com (David Kosenko)
Newsgroups: comp.databases.informix
Subject: Re: *** HELP - Index Locks are creating DEADLOCKS ***
Date: 9 Nov 92 17:24:54 GMT
Organization: Informix Software, Inc.

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
Newsgroups: comp.databases.informix