deadlock error

I am getting quite a few deadlock errors where both sessions are
trying to execute sp_execsql according to the the trace information in
the error log (see below). The database is being asscessed by an
application written in .NET, as well as a few people using Query
Analyzer. This seems to be happening relative randomly - can't pin it
to any specific circumstances. Any thoughts would be appreciated.

RID: 8:1:617:37 CleanCnt:1 Mode: X Flags: 0x2
Grant List 1::
Owner:0x3738dbe0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:55
ECID:0
SPID: 55 ECID: 0 Statement Type: CONDITIONAL Line #: 47
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:52 ECID:0 Ec:(0x4AC4D570)
Value:0x23297b80 Cost:(0/12C)

Node:2
RID: 8:1:267:91 CleanCnt:1 Mode: X Flags: 0x2
Grant List 0::
Owner:0x3efae340 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:52
ECID:0
SPID: 52 ECID: 0 Statement Type: CONDITIONAL Line #: 115
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x483FB570)
Value:0x37c0e060 Cost:(0/138)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:52 ECID:0 Ec:(0x4AC4D570)
Value:0x23297b80 Cost:(0/12C)[posted and mailed, please reply in news]

Scot Schneider (sschneider@.ebags.com) writes:
> I am getting quite a few deadlock errors where both sessions are
> trying to execute sp_execsql according to the the trace information in
> the error log (see below). The database is being asscessed by an
> application written in .NET, as well as a few people using Query
> Analyzer. This seems to be happening relative randomly - can't pin it
> to any specific circumstances. Any thoughts would be appreciated.

Both SqlClient and OleDb Client calls sp_executesql to run parameterized
statements, so if your application is doing this a lot, this could be
about anything.

The deadlock itself seems to be due to both processes holding a shared
locks, and both process wants an exclusive lock on the resource on whic
the other process have a shared lock.

There are two things in this deadlock trace that I find a little funny:

> SPID: 52 ECID: 0 Statement Type: CONDITIONAL Line #: 115

If you app is submitting dynamic SQL statements, line 115 is a pretty
high line number. Could it be that your app is actually using stored
procedures, but is using CommandType Text rather than StoredProcedure?
Changing this could give you somewhat better performance and somewhat
more informative deadlock traces.

> RID: 8:1:617:37 CleanCnt:1 Mode: X Flags: 0x2

Both locks are on tables without clustered indexes. This may be fully
conscious decsion, but the recommendation is to always have a
clustered index on your tables. There is no guarantee that the dealocks
goes away if you add a clustered index, but it could happen. At least
with a clustered index, it is easier to find the tables involved in
the deadlocl.

To dig out which tables that are involved in this dead lock you would
do:

SELECT db_name(8) -- gives you the database name.

DBCC TRACEON (3604, 1)
DBCC PAGE(8,1,617)
This gives you the header information for this page.

In the middle of this output, in the leftmost column is m_objId. This
is the object of the table. Copy and paste the value of m_objID, and run
SELECT object_name() for that value.

The value 37 is the row number, I would guess within that page.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp