August 6, 2009 at 10:52 pm
Hi,
We recently getting Dead locks daily one or two times. We have sql server 2005 EE x64 with SP3 and I have enabled T1222
2009-08-04 14:42:12.34 spid16s deadlock-list
2009-08-04 14:42:12.34 spid16s deadlock victim=processf3b198
2009-08-04 14:42:12.34 spid16s process-list
2009-08-04 14:42:12.34 spid16s process id=processbaeef8 taskpriority=0 logused=444 waitresource=OBJECT: 10:1316199739:0 waittime=1859 ownerId=99761884 transactionname=user_transaction lasttranstarted=2009-08-05T13:52:05.600 XDES=0x10566b130 lockMode=IX schedulerid=1 kpid=4704 status=suspended spid=99 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-08-05T13:52:05.603 lastbatchcompleted=2009-08-05T13:52:05.600 clientapp=.Net SqlClient Data Provider hostname=ABC hostpid=652 loginname=CBZ isolationlevel=read committed (2) xactid=99761884 currentdb=10 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2009-08-04 14:42:12.34 spid16s executionStack
2009-08-04 14:42:12.34 spid16s frame procname=MyDB.dbo.CBZsp_Policy_Save line=154 stmtstart=8868 stmtend=9988 sqlhandle=0x03000a0092be4d0722f919017a9b00000100000000000000
2009-08-04 14:42:12.34 spid16s INSERT INTO dbo.afl
2009-08-04 14:42:12.34 spid16s (aflCTBID
2009-08-04 14:42:12.34 spid16s ,aflTransactionDate
2009-08-04 14:42:12.34 spid16s ,aflTransactionType
2009-08-04 14:42:12.34 spid16s ,aflaflmentType
2009-08-04 14:42:12.34 spid16s ,afl_cstID
2009-08-04 14:42:12.34 spid16s ,aflCTSStatusDate
2009-08-04 14:42:12.34 spid16s ,afl_istID
2009-08-04 14:42:12.34 spid16s ,aflVictorStatusDate
2009-08-04 14:42:12.34 spid16s ,afl_polID
2009-08-04 14:42:12.34 spid16s ,aflVersion
2009-08-04 14:42:12.34 spid16s ,insert_userid
2009-08-04 14:42:12.34 spid16s ,insert_timestamp)
2009-08-04 14:42:12.34 spid16s SELECTCTBID
2009-08-04 14:42:12.34 spid16s ,TransactionDate
2009-08-04 14:42:12.34 spid16s ,TransactionType
2009-08-04 14:42:12.34 spid16s ,aflmentType
2009-08-04 14:42:12.34 spid16s ,@CTSStatus_None
2009-08-04 14:42:12.34 spid16s ,CTSStatusDate
2009-08-04 14:42:12.34 spid16s ,@VictorStatus_None
2009-08-04 14:42:12.34 spid16s ,VictorStatusDate
2009-08-04 14:42:12.34 spid16s ,@polID
2009-08-04 14:42:12.34 spid16s ,GETDATE()
2009-08-04 14:42:12.34 spid16s ,@userid
2009-08-04 14:42:12.34 spid16s ,GETDATE()
2009-08-04 14:42:12.34 spid16s FROM@xmlDoc_aflment
2009-08-04 14:42:12.34 spid16s WHEREIsNew = 1
2009-08-04 14:42:12.34 spid16s inputbuf
2009-08-04 14:42:12.34 spid16s Proc [Database Id = 10 Object Id = 122535570]
2009-08-04 14:42:12.34 spid16s process id=processf3b198 taskpriority=0 logused=292 waitresource=PAGE: 10:1:658 waittime=1859 ownerId=99761840 transactionname=user_transaction lasttranstarted=2009-08-05T13:52:05.560 XDES=0x2eacad0f0 lockMode=U schedulerid=3 kpid=5164 status=suspended spid=90 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-08-05T13:52:05.603 lastbatchcompleted=2009-08-05T13:52:05.600 clientapp=.Net SqlClient Data Provider hostname=ABC hostpid=940 loginname=CBZ isolationlevel=read committed (2) xactid=99761840 currentdb=10 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2009-08-04 14:42:12.34 spid16s executionStack
2009-08-04 14:42:12.34 spid16s frame procname=MyDB.dbo.CBZsp_aflmentCollection_SaveAll line=66 stmtstart=5048 stmtend=5756 sqlhandle=0x03000a00d2cc564461381e01ff9b00000100000000000000
2009-08-04 14:42:12.34 spid16s UPDATE dbo.intPolicy
2009-08-04 14:42:12.34 spid16s SET polDeleted = 1
2009-08-04 14:42:12.34 spid16s WHERE polID IN (
2009-08-04 14:42:12.34 spid16s SELECT afl_polID
2009-08-04 14:42:12.34 spid16s FROM dbo.afl
2009-08-04 14:42:12.34 spid16s INNER JOIN (
2009-08-04 14:42:12.34 spid16s SELECT [ID]
2009-08-04 14:42:12.34 spid16s FROM OPENXML(@hDoc, @aflmentNode, @ElementCentricMapping) WITH (
2009-08-04 14:42:12.34 spid16s [ID]INTEGER)
2009-08-04 14:42:12.34 spid16s ) A ON aflID = A.[ID]
2009-08-04 14:42:12.34 spid16s )
2009-08-04 14:42:12.34 spid16s AND polID NOT IN (
2009-08-04 14:42:12.34 spid16s SELECT afl_polID
2009-08-04 14:42:12.34 spid16s FROM dbo.afl
2009-08-04 14:42:12.34 spid16s WHERE aflDeleted = 0
2009-08-04 14:42:12.34 spid16s )
2009-08-04 14:42:12.34 spid16s inputbuf
2009-08-04 14:42:12.34 spid16s Proc [Database Id = 10 Object Id = 1146539218]
2009-08-04 14:42:12.34 spid16s resource-list
2009-08-04 14:42:12.34 spid16s pagelock fileid=1 pageid=658 dbid=10 objectname=MyDB.dbo.intPolicy id=lock8529da00 mode=IX associatedObjectId=72057594043695104
2009-08-04 14:42:12.34 spid16s owner-list
2009-08-04 14:42:12.34 spid16s owner id=processbaeef8 mode=IX
2009-08-04 14:42:12.34 spid16s waiter-list
2009-08-04 14:42:12.34 spid16s waiter id=processf3b198 mode=U requestType=wait
2009-08-04 14:42:12.34 spid16s objectlock lockPartition=0 objid=1316199739 subresource=FULL dbid=10 objectname=MyDB.dbo.afl id=lock2b6983980 mode=X associatedObjectId=1316199739
2009-08-04 14:42:12.34 spid16s owner-list
2009-08-04 14:42:12.34 spid16s owner id=processf3b198 mode=X
2009-08-04 14:42:12.34 spid16s waiter-list
2009-08-04 14:42:12.34 spid16s waiter id=processbaeef8 mode=IX requestType=wait
thank you
August 7, 2009 at 6:41 am
Is there a trigger or something that relates dbo.intPolicy and dbo.afl?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 7, 2009 at 6:56 am
Can you post the full definition of the following two procs?
MyDB.dbo.CBZsp_Policy_Save
MyDB.dbo.CBZsp_aflmentCollection_SaveAll
Can you also post the definitions and index definitions of these two tables
MyDB.dbo.intPolicy
MyDB.dbo.afl
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 7, 2009 at 6:57 am
Grant Fritchey (8/7/2009)
Is there a trigger or something that relates dbo.intPolicy and dbo.afl?
Could be from other statements earlier in the procs if there are explicit transactions been used.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 7, 2009 at 7:15 am
GilaMonster (8/7/2009)
Grant Fritchey (8/7/2009)
Is there a trigger or something that relates dbo.intPolicy and dbo.afl?Could be from other statements earlier in the procs if there are explicit transactions been used.
True. Thanks for mentioning it.
There has to be a further statement somewhere because I don't think, based on what they've shown so far, that the two statements, by themselves, would lead to a deadlock.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 7, 2009 at 7:57 am
Grant Fritchey (8/7/2009)
There has to be a further statement somewhere because I don't think, based on what they've shown so far, that the two statements, by themselves, would lead to a deadlock.
Agreed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply