ousoonerjoe
Programmer
Using SQL 2008 R2
Over the past several months, we have developed a deadlock issue with our Audit table. We use the following at the beginning of all our stored procedures:
This is a simple INSERT and an occasional SELECT. However, in almost all instances of the deadlock no SELECT was executed. Multiple INSERTs collided as the following results file show. My understanding is that the INSERTs should just "get in line" and process out in a First come First serve order. This example accounts for 95% of our deadlocks. It may not be the same offending stored procedure, but the offending statement is almost always the INSERT INTO Audit statement. I would appreciate any suggestions you may have. I've been banging my head against the wall for a while and the cleaning crew is starting to complain about the blood stains. Thank you.
--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------
Over the past several months, we have developed a deadlock issue with our Audit table. We use the following at the beginning of all our stored procedures:
Code:
[COLOR=#204A87] BEGIN TRANSACTION[/color];
[COLOR=#204A87]INSERT INTO[/color] Audit(ProcName, CmdType, Statement, ExecFrom, RecordIdNum, Arg1, Arg2, Arg3)
[COLOR=#204A87]VALUES[/color]('cst_Proc_Name, 'UPDATE', '', '', @RecId, @Arg1, @Arg2, @Arg3);
[COLOR=#204A87]COMMIT TRANSACTION[/color];
This is a simple INSERT and an occasional SELECT. However, in almost all instances of the deadlock no SELECT was executed. Multiple INSERTs collided as the following results file show. My understanding is that the INSERTs should just "get in line" and process out in a First come First serve order. This example accounts for 95% of our deadlocks. It may not be the same offending stored procedure, but the offending statement is almost always the INSERT INTO Audit statement. I would appreciate any suggestions you may have. I've been banging my head against the wall for a while and the cleaning crew is starting to complain about the blood stains. Thank you.
Code:
logdate procInfo ERRORLOG
----------------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2012-09-21 10:52:05.000 spid7s Deadlock encountered .... Printing deadlock information
2012-09-21 10:52:05.000 spid7s Wait-for graph
2012-09-21 10:52:05.000 spid7s NULL
2012-09-21 10:52:05.000 spid7s Node:1
2012-09-21 10:52:05.000 spid7s OBJECT: 5:1584086221:0 CleanCnt:3 Mode:IX Flags: 0x1
2012-09-21 10:52:05.000 spid7s Grant List 2:
2012-09-21 10:52:05.000 spid7s Owner:0x4425DC00 Mode: IX Flg:0x40 Ref:1 Life:02000000 SPID:93 ECID:0 XactLockInfo: 0x5A224B18
2012-09-21 10:52:05.000 spid7s SPID: 93 ECID: 0 Statement Type: INSERT Line #: 19
2012-09-21 10:52:05.000 spid7s Input Buf: RPC Event: Proc [Database Id = 5 Object Id = 862534752]
2012-09-21 10:52:05.000 spid7s Requested by:
2012-09-21 10:52:05.000 spid7s ResType:LockOwner Stype:'OR'Xdes:0x11A22280 Mode: X SPID:76 BatchID:0 ECID:0 TaskProxy:(0x19C96354) Value:0x3dafaac0 Cost:(0/0)
2012-09-21 10:52:05.000 spid7s NULL
2012-09-21 10:52:05.000 spid7s Node:2
2012-09-21 10:52:05.000 spid7s OBJECT: 5:1584086221:0 CleanCnt:3 Mode:IX Flags: 0x1
2012-09-21 10:52:05.000 spid7s Grant List 2:
2012-09-21 10:52:05.000 spid7s Owner:0x4A4C2820 Mode: IX Flg:0x40 Ref:1 Life:02000000 SPID:76 ECID:0 XactLockInfo: 0x11A222A8
2012-09-21 10:52:05.000 spid7s SPID: 76 ECID: 0 Statement Type: INSERT Line #: 19
2012-09-21 10:52:05.000 spid7s Input Buf: RPC Event: Proc [Database Id = 5 Object Id = 862534752]
2012-09-21 10:52:05.000 spid7s Requested by:
2012-09-21 10:52:05.000 spid7s ResType:LockOwner Stype:'OR'Xdes:0x5A224AF0 Mode: X SPID:93 BatchID:0 ECID:0 TaskProxy:(0x19DFC354) Value:0x4425cfa0 Cost:(0/0)
2012-09-21 10:52:05.000 spid7s NULL
2012-09-21 10:52:05.000 spid7s Victim Resource Owner:
2012-09-21 10:52:05.000 spid7s ResType:LockOwner Stype:'OR'Xdes:0x11A22280 Mode: X SPID:76 BatchID:0 ECID:0 TaskProxy:(0x19C96354) Value:0x3dafaac0 Cost:(0/0)
2012-09-21 10:52:05.000 spid23s deadlock-list
2012-09-21 10:52:05.000 spid23s deadlock victim=process3bed8e8
2012-09-21 10:52:05.000 spid23s process-list
2012-09-21 10:52:05.000 spid23s process id=process3bed8e8 taskpriority=0 logused=0 waitresource=OBJECT: 5:1584086221:0 waittime=448 ownerId=479458334 transactionname=user_transaction lasttranstarted=2012-09-21T10:52:04.553 XDES=0x11a22280 lockMode=X schedulerid=6 kpid=5360 status=sus
2012-09-21 10:52:05.000 spid23s executionStack
2012-09-21 10:52:05.000 spid23s frame procname=UC.dbo.cstDoStageOnly line=19 stmtstart=1558 stmtend=1894 sqlhandle=0x03000500603c69331667dd00be9e00000100000000000000
2012-09-21 10:52:05.000 spid23s INSERT INTO Audit(ProcName, CmdType, Statement, ExecFrom, RecordIdNum, Arg1, Arg2, Arg3)
2012-09-21 10:52:05.000 spid23s VALUES('cstDoStageOnly', 'UPDATE', '', '', 0, @ronum, @stageno, @dtstaged);
2012-09-21 10:52:05.000 spid23s inputbuf
2012-09-21 10:52:05.000 spid23s Proc [Database Id = 5 Object Id = 862534752]
2012-09-21 10:52:05.000 spid23s process id=processff3558 taskpriority=0 logused=0 waitresource=OBJECT: 5:1584086221:0 waittime=448 ownerId=479458335 transactionname=user_transaction lasttranstarted=2012-09-21T10:52:04.553 XDES=0x5a224af0 lockMode=X schedulerid=5 kpid=980 status=suspe
2012-09-21 10:52:05.000 spid23s executionStack
2012-09-21 10:52:05.000 spid23s frame procname=UC.dbo.cstDoStageOnly line=19 stmtstart=1558 stmtend=1894 sqlhandle=0x03000500603c69331667dd00be9e00000100000000000000
2012-09-21 10:52:05.000 spid23s INSERT INTO Audit(ProcName, CmdType, Statement, ExecFrom, RecordIdNum, Arg1, Arg2, Arg3)
2012-09-21 10:52:05.000 spid23s VALUES('cstDoStageOnly', 'UPDATE', '', '', 0, @ronum, @stageno, @dtstaged);
2012-09-21 10:52:05.000 spid23s inputbuf
2012-09-21 10:52:05.000 spid23s Proc [Database Id = 5 Object Id = 862534752]
2012-09-21 10:52:05.000 spid23s resource-list
2012-09-21 10:52:05.000 spid23s objectlock lockPartition=0 objid=1584086221 subresource=FULL dbid=5 objectname=UC.dbo.Audit id=locke491b00 mode=IX associatedObjectId=1584086221
2012-09-21 10:52:05.000 spid23s owner-list
2012-09-21 10:52:05.000 spid23s owner id=processff3558 mode=IX
2012-09-21 10:52:05.000 spid23s waiter-list
2012-09-21 10:52:05.000 spid23s waiter id=process3bed8e8 mode=X requestType=convert
2012-09-21 10:52:05.000 spid23s objectlock lockPartition=0 objid=1584086221 subresource=FULL dbid=5 objectname=UC.dbo.Audit id=locke491b00 mode=IX associatedObjectId=1584086221
2012-09-21 10:52:05.000 spid23s owner-list
2012-09-21 10:52:05.000 spid23s owner id=process3bed8e8 mode=IX
2012-09-21 10:52:05.000 spid23s waiter-list
2012-09-21 10:52:05.000 spid23s waiter id=processff3558 mode=X requestType=convert
(51 rows affected)
--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------