Update Statement Deadlock Issue
-
The server I am working with is SQL Server 2005 v9.0.4262 Enterprise x64
I have a deadlock issue between two stored procedures. Both are doing an UPDATE statement to the same table at the time of the deadlock. The table is clustered on an ID field. All SELECT statements in the procedures against that table are using the NOLOCk locking hint.
The issue I am seeing is that procedure A has a mode=U lock on a key and proc B is waiting on a mode=X lock, meanwhile proc B has a mode=U lock on the same key and proc A is waiting on a mode=X lock.
Based on what I've read, this scenario should be impossible because you can't have an update lock and an exclusive lock on the same resource. Does this look like some kind of bug in SQL Server for this version I am running or am I misunderstanding something with how locking works?
Any help is appreciated. Below is the locking table and the deadlock trace.
Lock Chart
Deadlock output
Deadlock encountered .... Printing deadlock information
Wait-for graph
Node:1
KEY: 6:72057594515554304 (f500c4ba8e6b) CleanCnt:2 Mode:U Flags: 0x0
Grant List 1:
Owner:0x0000000004520280 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:72 ECID:0 XactLockInfo: 0x0000000274655758
SPID: 72 ECID: 0 Statement Type: UPDATE Line #: 134
Input Buf: RPC Event: Proc [Database Id = 6 Object Id = 1918994263]
Requested By:
ResType:LockOwner Stype:'OR'Xdes:0x00000000A6A506C0 Mode: U SPID:59 BatchID:0 ECID:0 TaskProxy:(0x000000037C0F2598) Value:0x55bff80 Cost:(0/0)
Node:2
KEY: 6:72057594515554304 (91008ead68c0) CleanCnt:3 Mode:X Flags: 0x0
Grant List 0:
Owner:0x00000000049916C0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:61 ECID:0 XactLockInfo: 0x00000002D6E0C3A8
SPID: 61 ECID: 0 Statement Type: UPDATE Line #: 59
Input Buf: RPC Event: Proc [Database Id = 6 Object Id = 1166991584]
Requested By:
ResType:LockOwner Stype:'OR'Xdes:0x0000000274655720 Mode: U SPID:72 BatchID:0 ECID:0 TaskProxy:(0x00000001BB8AC598) Value:0xed128900 Cost:(0/220)
Node:3
KEY: 6:72057594515554304 (2d0053c43b84) CleanCnt:2 Mode:U Flags: 0x0
Grant List 1:
Owner:0x0000000004520F40 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:72 ECID:0 XactLockInfo: 0x0000000274655758
Requested By:
ResType:LockOwner Stype:'OR'Xdes:0x00000002D6E0C370 Mode: X SPID:61 BatchID:0 ECID:0 TaskProxy:(0x000000016EE0E598) Value:0x4d9b080 Cost:(0/468)
Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x0000000274655720 Mode: U SPID:72 BatchID:0 ECID:0 TaskProxy:(0x00000001BB8AC598) Value:0xed128900 Cost:(0/220)
deadlock-list
deadlock victim=process3827048
process-list
process id=process38129b8 taskpriority=0 logused=468 waitresource=KEY: 6:72057594515554304 (2d0053c43b84) waittime=4578 ownerId=12298661353 transactionname=user_transaction lasttranstarted=2012-05-31T11:27:51.970 XDES=0x2d6e0c370 lockMode=X schedulerid=2 kpid=2696 status=suspended spid=61 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2012-05-31T11:27:51.970 lastbatchcompleted=2012-05-31T11:27:51.970 clientapp=.Net SqlClient Data Provider hostname=MyWebServer hostpid=7448 loginname=MyLoginName isolationlevel=read committed (2) xactid=12298661353 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=MyDB.dbo.ProcA line=59 stmtstart=3220 stmtend=3444 sqlhandle=0x03000600e0e08e450cf824016d9a00000100000000000000
UPDATE dbo.MyTable
SET JobsRemaining = JobsRemaining - @Transactions
WHERE MyTable.ID = @NewFounderID;
inputbuf
Proc [Database Id = 6 Object Id = 1166991584]
process id=process3827048 taskpriority=0 logused=220 waitresource=KEY: 6:72057594515554304 (91008ead68c0) waittime=4718 ownerId=12298661341 transactionname=user_transaction lasttranstarted=2012-05-31T11:27:51.960 XDES=0x274655720 lockMode=U schedulerid=4 kpid=5452 status=suspended spid=72 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2012-05-31T11:27:51.960 lastbatchcompleted=2012-05-31T11:27:51.960 clientapp=.Net SqlClient Data Provider hostname=MyWebServer hostpid=5400 loginname=MyLoginName isolationlevel=read committed (2) xactid=12298661341 currentdb=6 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
executionStack
frame procname=MyDB.dbo.ProcB line=134 stmtstart=14710 stmtend=16208 sqlhandle=0x0300060057876172541c9500739e00000100000000000000
UPDATE dbo.MyTable
SET JobsRemaining = JobsRemaining - 1
FROM
dbo.MyTableB WITH (NOLOCK)
JOIN dbo.MyTableC WITH (NOLOCK) ON MyTableC.UserID = MyTableB.UserID
WHERE
MyTableB.JdtID = @JdtID
and MyTable.ID = COALESCE((SELECT TOP 1 AncestorID
FROM dbo.MyTableD WITH (NOLOCK)
WHERE GroupID = MyTableC.GroupID
ORDER BY Generation DESC), MyTableC.GroupID);
inputbuf
Proc [Database Id = 6 Object Id = 1918994263]
resource-list
keylock hobtid=72057594515554304 dbid=6 objectname=MyDB.dbo.MyTable indexname=PK_MyTable id=lock3919280 mode=X associatedObjectId=72057594515554304
owner-list
owner id=process38129b8 mode=X
waiter-list
waiter id=process3827048 mode=U requestType=wait
keylock hobtid=72057594515554304 dbid=6 objectname=MyDB.dbo.MyTable indexname=PK_MyTable id=lock43be180 mode=U associatedObjectId=72057594515554304
owner-list
owner id=process3827048 mode=U
waiter-list
waiter id=process38129b8 mode=X requestType=wait
-
Would also help if you would post the code involved.
-
I don't think I'm allowed to post the code.
I really just want to know if this locking scenario is something that should be unexpected. Based on everything I've read online, having two spids with an update and an exclusive lock on the same resource should be impossible. I want to know if there are any known scenarios that could cause this situation and what the resolution to those scenarios are.
The only statements that are touching the table involved in the deadlock are a couple of SELECT statements using NOLOCK and then one update statement in one proc and 3 update statements in the other proc. The code for the exact statements that are deadlocking are in the deadlock output with the sensitive names replaced.
-
Here is the problem, we can't see from here what you see. You are basically asking us to shoot in the dark and hope we hit the mark. You have to help us help you. If you can't post the actual code for the procedures then you you need to mock up something that properly represents your code but isn't.
If two update procedures are deadlocking each other there is a very good chance that it is code related. Without looking at it, there is no way for us to even know. I'm sorry, but the force only helps so much.
-
I understand. I will see if I can get a mocked up version of the code that accurately represents what's actually going on.
thank you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply