Deadlocks in Profiler

I'm trying to diagnose deadlocks in SQL Profiler. The deadlocks were
generated by Loadrunner scripts (stress testing) simulating application
SQL via an ODBC DSN connection.

2 things are puzzling me in the SQL Profiler traces that I have logged

1) There are a large number of Lock:Timeout events but the 'lock
timeout' setting is the default 'wait forever' so I dont know what is
timing out.

2)When say 2 distinct SPIDs are in a Deadlock Chain, they are using the
same ClientProcessId at the time of deadlock. What is the
ClientProcessId and is it relevant to the deadlock?

Thank you in advance for any replies.<Robert_Couldry@.linfox.com> wrote in message
news:1113887215.714232.311400@.f14g2000cwb.googlegr oups.com...
> I'm trying to diagnose deadlocks in SQL Profiler. The deadlocks were
> generated by Loadrunner scripts (stress testing) simulating application
> SQL via an ODBC DSN connection.
> 2 things are puzzling me in the SQL Profiler traces that I have logged
> 1) There are a large number of Lock:Timeout events but the 'lock
> timeout' setting is the default 'wait forever' so I dont know what is
> timing out.

Lock timeouts are set by the client, so perhaps one particular connection or
application has set a specific value - you can use sp_lock to investigate
exactly which process is holding a lock and on which resource, and Erland
has a useful tool for investigating locking:

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

> 2)When say 2 distinct SPIDs are in a Deadlock Chain, they are using the
> same ClientProcessId at the time of deadlock. What is the
> ClientProcessId and is it relevant to the deadlock?

ClientProcessID is the operating system process ID (ie. the PID in Task
Manager) of the client application connecting to MSSQL. One application may
have multiple SPIDs - Query Analyzer has one for the main window, and
another for the Object Browser. So it seems that your application is somehow
blocking itself.

This KB article may help you, if you haven't already seen it (there are a
number of other articles about deadlock caused by known issues also):

http://support.microsoft.com/defaul...kb;en-us;832524

Simon

Deadlock Problems

Hi all
Now a day I am facing too-many deadlock problems, is there any way or
possibility that I make automated job, for diagnose the deadlock victim and
kill that process.
Any advice will be greatly appreciated.
Farhan Iqbal
Why would you want to kill the victim? The application should be able to
recover from a deadlock situation and either retry or gracefully handle it
some how. That really isn't a database issue. You would be best served by
spending that time fixing the reason why deadlocks occur in the first place.
Andrew J. Kelly SQL MVP
"Farhan Iqbal" <mr_farhaniqbal@.hotmail.com> wrote in message
news:eR0mzUrTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> Hi all
>
> Now a day I am facing too-many deadlock problems, is there any way or
> possibility that I make automated job, for diagnose the deadlock victim
and
> kill that process.
>
> Any advice will be greatly appreciated.
> Farhan Iqbal
>
>
|||Shouldn't SQL automatically be killing the victim, rolling back it's transaction? There really isn't a need to write a job to do this for you.
Have the application constantly checking for the 1205 message.
"Farhan Iqbal" wrote:

> Hi all
>
> Now a day I am facing too-many deadlock problems, is there any way or
> possibility that I make automated job, for diagnose the deadlock victim and
> kill that process.
>
> Any advice will be greatly appreciated.
> Farhan Iqbal
>
>
>