Deadlocks on Queries

Hi,
I have a situation where I have inherited a system that is being
stress tested at the moment. The main table used in the DB has a
number of indexes. The problem I am having is that under load, I keep
getting deadlocks on iwhat appears to be the indexes on the tables.
More specifically a row appears to be inserted / updated, and another
select query is being locked out when trying to query that table.
I haven't touched fill factors (they are still the default zero). Any
pointers / reading material would be appreciated.Check if your query includes BEGIN TRAN and COMMIT TRAN at proper place
"Spondishy" wrote:
> Hi,
> I have a situation where I have inherited a system that is being
> stress tested at the moment. The main table used in the DB has a
> number of indexes. The problem I am having is that under load, I keep
> getting deadlocks on iwhat appears to be the indexes on the tables.
> More specifically a row appears to be inserted / updated, and another
> select query is being locked out when trying to query that table.
> I haven't touched fill factors (they are still the default zero). Any
> pointers / reading material would be appreciated.
>

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