Deadlocks, why?

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.

ThanksDon 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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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).|||Don Vaillancourt (donv@.webimpact.com) writes:
> Oh, I know which queries are involved and which ones are usually the
> victims.

OK. With table definitions and indexes and the queries, it's possible
that we can spot some potential problems. Without them it's going to
be hard. :-)

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Don Vaillancourt wrote:
> 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.

Oh - you meant -that- kind of deadlock. Try some dried plums <g>.|||Hi Don

It could be that you will never be able to replicate the deadlock if your
hardware/environment is exactly the same. If you have not run
sp_blocker_pss80 you may want to try it
http://support.microsoft.com/defaul...kb;en-us;271509

John

"Don Vaillancourt" <donv@.webimpact.com> wrote in message
news:bWVxf.9204$43.7861@.nnrp.ca.mci.com!nnrp1.uune t.ca...
> 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).
>