db wrote:

> can someone suggest the possible reasons,etc., or what can cause this
> to happen ...i check for...
> any help would be appreciated..

The canonical case for causing deadlocks is to have transactions
that operate on the same tables in differnt orders. For example:

First transaction:

begin transaction
update table_a . . .
update table_b . . .
commit transaction

Second transaction:

begin transaction
update table_b . . .
update table_a . . .
commit transaction

If these transactions are run in parallel frequently, sooner or
later you will have a case where each transaction has performed
its first update, and is waiting on the other to release its lock
so it can proceed to the second update.

The fix (and the general advice) is to make sure your transactions
acquire resources in the same order. Other advice is to keep your
transactions as short as possible, and possibly increase lock
granularity (row locks instead of page locks, and/or page locks
instead of table locks), and/or locking scheme (datapage instead
of allpage).


Larry Coon
University of California