Posted by Erland Sommarskog on 01/14/06 00:28

Don Vaillancourt (donv@webimpact.com) writes:
> We have a problem with a table giving us deadlock issues and we can't
> figure out why.
>
> It's a table we write to fairly often perhaps 50 times a minute. And
> also do a select of 200 rows at a time from 4 servers every 5 minutes or
> so.
>
> We are only keeping 48 hours worth of rows in the table which averages
> at 30000 a day on a busy day.
>
> This table has 1 PK and 2 FKs plus one TEXT column which does not
> participate in the WHERE clause.
>
> We are using binded variables.
>
> We have applied the latest patch to SQL2003 server running on
> Windows2003. The patch is supposed to resolve deadlock issues.
>
> Anyone have any advice on how to alleviate this problem.

I'm afraid that there is not enough information your post to make it
possible to give solutions.

Except one: if it is acceptable that one of the process is always
is the victim, make this process emit SET DEADLOCK_PRIORITY LOW.
We have done this in quite a few places in our system. Background
processes don't scream so much about deadlocks as users do.

But if that is not an option, I can only suggest methods to get more
information.

First, have you enabled deadlock trace on your server and looked at
the output? To enable deadlock trace, use Enterprise Manager to add
these two startup options: -T 1204 -T 3605.

Once you have the deadlock output, try to narrow down exactly which
queries that collide. Once you have the queries, you could post them
together with the table definitions (including indexes!). Or you could
post the deadlock traces (which is not very easy to interpret).

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]