dropping and index resolves row-level deadlocks ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JIMC - 21 Sep 2006 00:32 GMT

We've been battling a deadlock problem in II2.6 for two weeks now. We
are seeing page deadlocks on the index ( there's only one ) as well as
deadlocks on rows. The table has row level locking and I understand
that the indexes are page level locking.

We can pretty much recreate the deadlocks but looking at the code ( and
there's a ton of it so maybe we're missing something ) we can't see
where the row level deadlocks would occur.

We dropped the index today, retested and all deadlocks went away !!!
We tested three times so we're pretty sure this resolves the problem.

Does this sound right ? Why would dropping an index eliminate row
level locks ?

Reply to this Message

franky.leeuwerck@gmail.com - 21 Sep 2006 11:41 GMT

Hi JIMC,

Are your indexes also built with min. 4K pages ?
They should if you want to apply row level locking.

Further, I'm sure your indexes have no overflows.

Franky

Reply to this Message

Chip Nickolett - 21 Sep 2006 13:31 GMT

> We've been battling a deadlock problem in II2.6 for two weeks now. We
> are seeing page deadlocks on the index ( there's only one ) as well as
[quoted text clipped - 10 lines]
> Does this sound right ? Why would dropping an index eliminate row
> level locks ?

Hi Jim,

Typically there are far more entries per page on an index then on the
base table. This is usually dependent on the overall schema design and
transaction design and is therefore avoidable. It is possible that
this is just a side effect of an inefficient query plan that could be
corrected by generating better statistics.

There are sometimes ways to tune around that with different storage
index storage structures and fillfactors. It may also be possible to
change the indexing strategy to accomplish what you need in a more
efficient way that resolves this problem.

Sometimes the best fix is to rewrite a query or redesign a transaction.
If this is the legacy system that we discussed once then that could be
more challenging. Please give me a call if you would like to discuss
this further (262-785-8101).

Best regards.

Chip Nickolett (ChipN@Comp-Soln.com)

US - Comprehensive Solutions www.Comp-Soln.com
UK - Comprehensive Solutions International www.Comp-Soln.co.uk

Reply to this Message