Groups > comp.databases.ms-sqlserver > #583

help needed understanding a deadlock

From	migurus <migurus@yahoo.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	help needed understanding a deadlock
Date	2011-08-02 19:56 -0700
Organization	http://groups.google.com
Message-ID	<ac08fe04-4d59-4eb4-be89-6cd358873b29@j9g2000prj.googlegroups.com> (permalink)

Show all headers | View raw

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 TaskProxy:(0x4CB3E378)
Value:0x25dc4b20 Cost:(0/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 TaskProxy:(0x1E59A378)
Value:0x74a6b540 Cost:(0/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 TaskProxy:(0x1E59A378)
Value:0x74a6b540 Cost:(0/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=0x03000f004287880aca1ac800259f00000100000000000000
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=0x01000f003de2e22250daa43f000000000000000000000000
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=0x03000f004287880aca1ac800259f00000100000000000000
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=0x01000f003de2e22250daa43f000000000000000000000000
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,

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

help needed understanding a deadlock migurus <migurus@yahoo.com> - 2011-08-02 19:56 -0700
  Re: help needed understanding a deadlock Erland Sommarskog <esquel@sommarskog.se> - 2011-08-03 23:21 +0200

csiph-web