Posted by Don Vaillancourt on 01/14/06 00:41

Oh, I know which queries are involved and which ones are usually the
victims.

But thanks for the trace idea.

We haven't been able to replicate the deadlock issue in-house ass of
yet, but I will certainly keep those options in mind and use them.

Thank you


Erland Sommarskog wrote:
> 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).
>

[Back to original message]