This is SQL 2005
I have a stored procedure that deadlocked om PR_CUST table

CREATE TABLE [dbo].[PR_CUST](
[FROM_CUST_ID] [int] NULL,
[TO_CUST_ID] [int] NULL,
[CUST_REFERENCE_NO] [int] NULL,
[TEST_REQUIRED] [int] NULL,
[RANK_REQUIRED] [int] NULL,
... <snip for brevity>
[CUST_ID] [int] IDENTITY(1,1) NOT NULL
CONSTRAINT [PK_PR_CUST] PRIMARY KEY CLUSTERED ( [CUST_ID] ASC )
)

The procedure looks like this:

DECLARE @IDS TABLE (tid INT NOT NULL)
INSERT INTO @IDS (tid) SELECT TID FROM TTBL WHERE TDATE between @p1
AND @p2

UPDATE PR_CUST SET PR_RUN_ID = NULL
WHERE CUST_ID IN (select tid from @IDS)

....
other updates and deletes
based on ids stored in @IDS
follow here
....
END

The deadlock happened when the stored procedure was fired with the
same set of parameters twice in parallel, the trace is below:



2011-07-28 01:45:10.23 spid4s Deadlock encountered .... Printing
deadlock information
2011-07-28 01:45:10.23 spid4s Wait-for graph
2011-07-28 01:45:10.23 spid4s
2011-07-28 01:45:10.23 spid4s Node:1

2011-07-28 01:45:10.23 spid4s OBJECT: 15:2052202361:0
CleanCnt:4 Mode:S Flags: 0x0
2011-07-28 01:45:10.23 spid4s Grant List 1:
2011-07-28 01:45:10.23 spid4s Grant List 2:
2011-07-28 01:45:10.23 spid4s Owner:0x74A6AE40 Mode: S
Flg:0x0 Ref:1 Life:02000000 SPID:73 ECID:0 XactLockInfo: 0x1E5AA474
2011-07-28 01:45:10.23 spid4s SPID: 73 ECID: 0 Statement Type:
UPDATE Line #: 40
2011-07-28 01:45:10.23 spid4s Input Buf: Language Event: (@P0
datetime,@P1 int)EXEC Purge @P0 , @P1
2011-07-28 01:45:10.23 spid4s Requested By:
2011-07-28 01:45:10.23 spid4s ResType:LockOwner Stype:'OR'Xdes:
0x041A9240 Mode: IX SPID:64 BatchID:0 ECID:0 TaskProxy0x4CB3E378)
Value:0x25dc4b20 Cost0/0)
2011-07-28 01:45:10.23 spid4s
2011-07-28 01:45:10.23 spid4s Node:2

2011-07-28 01:45:10.23 spid4s OBJECT: 15:2052202361:0
CleanCnt:4 Mode:S Flags: 0x0
2011-07-28 01:45:10.23 spid4s Grant List 1:
2011-07-28 01:45:10.23 spid4s Owner:0x25F96BE0 Mode: S
Flg:0x0 Ref:1 Life:02000000 SPID:64 ECID:0 XactLockInfo: 0x041A9264
2011-07-28 01:45:10.23 spid4s SPID: 64 ECID: 0 Statement Type:
UPDATE Line #: 40
2011-07-28 01:45:10.23 spid4s Input Buf: Language Event: (@P0
datetime,@P1 int)EXEC Purge @P0 , @P1
2011-07-28 01:45:10.23 spid4s Grant List 2:
2011-07-28 01:45:10.23 spid4s Requested By:
2011-07-28 01:45:10.23 spid4s ResType:LockOwner Stype:'OR'Xdes:
0x1E5AA450 Mode: IX SPID:73 BatchID:0 ECID:0 TaskProxy0x1E59A378)
Value:0x74a6b540 Cost0/0)
2011-07-28 01:45:10.23 spid4s
2011-07-28 01:45:10.23 spid4s Victim Resource Owner:
2011-07-28 01:45:10.23 spid4s ResType:LockOwner Stype:'OR'Xdes:
0x1E5AA450 Mode: IX SPID:73 BatchID:0 ECID:0 TaskProxy0x1E59A378)
Value:0x74a6b540 Cost0/0)
2011-07-28 01:45:10.23 spid15s deadlock-list
2011-07-28 01:45:10.23 spid15s deadlock victim=processc1ab68
2011-07-28 01:45:10.23 spid15s process-list
2011-07-28 01:45:10.23 spid15s process id=process8de6b8
taskpriority=0 logused=0 waitresource=OBJECT: 15:2052202361:0
waittime=2375 ownerId=306798032 transactionname=implicit_transaction
lasttranstarted=2011-07-28T01:45:07.437 XDES=0x41a9240 lockMode=IX
schedulerid=3 kpid=7044 status=suspended spid=64 sbid=0 ecid=0
priority=0 transcount=2 lastbatchstarted=2011-07-28T01:45:07.853
lastbatchcompleted=2011-07-28T01:45:07.837 clientapp=jTDS
hostname=PUSLAB1 hostpid=123 loginname=sa isolationlevel=read
committed (2) xactid=306798032 currentdb=15 lockTimeout=4294967295
clientoption1=673186080 clientoption2=128058
2011-07-28 01:45:10.23 spid15s executionStack
2011-07-28 01:45:10.23 spid15s frame procname=PR_QA.dbo.Purge
line=40 stmtstart=2754 stmtend=3072
sqlhandle=0x03000f004287880aca1ac800259f0000010000 0000000000
2011-07-28 01:45:10.23 spid15s UPDATE PR_CUST
2011-07-28 01:45:10.23 spid15s SET PR_RUN_ID = NULL
2011-07-28 01:45:10.23 spid15s WHERE CUST_ID IN (select tid from
@IDS)
2011-07-28 01:45:10.23 spid15s frame procname=adhoc line=1
stmtstart=44
sqlhandle=0x01000f003de2e22250daa43f00000000000000 0000000000
2011-07-28 01:45:10.23 spid15s EXEC Purge @P0 , @P1
2011-07-28 01:45:10.23 spid15s inputbuf
2011-07-28 01:45:10.23 spid15s (@P0 datetime,@P1 int)EXEC Purge
@P0 , @P1
2011-07-28 01:45:10.23 spid15s process id=processc1ab68
taskpriority=0 logused=0 waitresource=OBJECT: 15:2052202361:0
waittime=2578 ownerId=306798004 transactionname=implicit_transaction
lasttranstarted=2011-07-28T01:45:07.107 XDES=0x1e5aa450 lockMode=IX
schedulerid=5 kpid=7968 status=suspended spid=73 sbid=0 ecid=0
priority=0 transcount=2 lastbatchstarted=2011-07-28T01:45:07.487
lastbatchcompleted=2011-07-28T01:45:07.413 clientapp=jTDS
hostname=PUSLAB2 hostpid=123 loginname=sa isolationlevel=read
committed (2) xactid=306798004 currentdb=15 lockTimeout=4294967295
clientoption1=673186080 clientoption2=128058
2011-07-28 01:45:10.23 spid15s executionStack
2011-07-28 01:45:10.23 spid15s frame procname=PR_QA.dbo.Purge
line=40 stmtstart=2754 stmtend=3072
sqlhandle=0x03000f004287880aca1ac800259f0000010000 0000000000
2011-07-28 01:45:10.23 spid15s UPDATE PR_CUST
2011-07-28 01:45:10.23 spid15s SET PR_RUN_ID = NULL
2011-07-28 01:45:10.23 spid15s WHERE CUST_ID IN (select tid from
@IDS)
2011-07-28 01:45:10.23 spid15s frame procname=adhoc line=1
stmtstart=44
sqlhandle=0x01000f003de2e22250daa43f00000000000000 0000000000
2011-07-28 01:45:10.23 spid15s EXEC Purge @P0 , @P1
2011-07-28 01:45:10.23 spid15s inputbuf
2011-07-28 01:45:10.23 spid15s (@P0 datetime,@P1 int)EXEC Purge
@P0 , @P1
2011-07-28 01:45:10.23 spid15s resource-list
2011-07-28 01:45:10.23 spid15s objectlock lockPartition=0
objid=2052202361 subresource=FULL dbid=15 objectname=PR_QA.dbo.PR_CUST
id=lock1903de40 mode=S associatedObjectId=2052202361
2011-07-28 01:45:10.23 spid15s owner-list
2011-07-28 01:45:10.23 spid15s owner id=process8de6b8 mode=S
2011-07-28 01:45:10.23 spid15s owner id=processc1ab68 mode=S
2011-07-28 01:45:10.23 spid15s waiter-list
2011-07-28 01:45:10.23 spid15s waiter id=processc1ab68
mode=IX requestType=convert
2011-07-28 01:45:10.23 spid15s waiter id=process8de6b8
mode=IX requestType=convert

Because set of params is the same, the controlling list of IDs in @IDS
in both runs should be the same. I am not clear how these two simple
updates managed to deadlock.

So, my understanding is that table 2052202361 is the problem

select object_name(2052202361)
shows PR_CUST

I can not identify which resources specifically were the culprit. Does
the trace shows a page, or some other resource that was a center of
deadlock?

Can someone please help to determine what has been deadlocked?
Regards,

migurus (migurus (AT) yahoo (DOT) com) writes:
It seems that both processes has a shared lock on the table, and then asks
for an Intent-Exclusive whereupon they clash. This is a bit strange, since
they should take out an Update lock instead; only one proecss at a time can
only an Update lock on a resource.

But what indexes are there on this table? It seems that this table is a
heap (:0 in the object address.) Is there an index on CUST_ID?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx