SEO

i need some help with a deadlock situation -

i have a situation where the point of contention is a single table
(rather some row/s in a table, i think).

looking thru the logs and here is what i see -
one process is trying to insert data in the table x and has held
exclusive "page" lock
and the other process is trying to do an update and has also held an
exclusive "page" lock

i have been asked to do a implement a retry thru the application when
this happens but
i would like to prevent or reduce the possiblity of this if there is
some way...

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


thanks in advance,
db

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