July 10, 2009 at 10:29 am
Hello,
We have BizTalk application databases in SQL Server 2005. I have noticed the below dead lock in the error log. Could you please explain me how to know the cause of this dead lock.
2009-07-06 18:46:35.81 spid13s deadlock-list
2009-07-06 18:46:35.81 spid13s deadlock victim=processf3bac8
2009-07-06 18:46:35.81 spid13s process-list
2009-07-06 18:46:35.82 spid13s process id=processbaeef8 taskpriority=5 logused=10000 waitresource=APPLICATION: 5:0:[[dbo].[TrackingData_1_0]]:(13020d16) waittime=1109 schedulerid=1 kpid=5536 status=suspended spid=63 sbid=0 ecid=0 priority=-5 transcount=0 lastbatchstarted=2009-07-06T18:46:34.693 lastbatchcompleted=2009-07-06T18:46:34.687 clientapp=.Net SqlClient Data Provider hostname=BTSCLUSTER-SSO hostpid=9024 loginname=ABC\BTSAppHost isolationlevel=read committed (2) xactid=0 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2009-07-06 18:46:35.82 spid13s executionStack
2009-07-06 18:46:35.82 spid13s frame procname=mssqlsystemresource.sys.xp_userlock line=1 sqlhandle=0x0400ff7f722a782701000000000000000000000000000000
2009-07-06 18:46:35.82 spid13s xp_userlock
2009-07-06 18:46:35.82 spid13s frame procname=mssqlsystemresource.sys.sp_getapplock line=56 stmtstart=2762 stmtend=2958 sqlhandle=0x0300ff7f459c46090790ea005e9b00000100000000000000
2009-07-06 18:46:35.82 spid13s exec @result = sys.xp_userlock 0, @dbid, @DbPrincipal, @Resource, @mode, @owner, @LockTimeout
2009-07-06 18:46:35.82 spid13s frame procname=BizTalkMsgBoxDb.dbo.TDDS_GetTrackingRecord line=12 stmtstart=826 stmtend=954 sqlhandle=0x03000500ec026328b5b3f900219b00000100000000000000
2009-07-06 18:46:35.82 spid13s exec @retVal = sp_getapplock @tableName, 'Exclusive', 'Session'
2009-07-06 18:46:35.82 spid13s inputbuf
2009-07-06 18:46:35.82 spid13s Proc [Database Id = 5 Object Id = 677577452]
2009-07-06 18:46:35.82 spid13s process id=processf3bac8 taskpriority=5 logused=10000 waitresource=APPLICATION: 5:0:[[dbo].[TrackingData_1_1]]:(7665b1ae) waittime=1109 schedulerid=3 kpid=6688 status=suspended spid=65 sbid=0 ecid=0 priority=-5 transcount=0 lastbatchstarted=2009-07-06T18:46:34.703 lastbatchcompleted=2009-07-06T18:46:34.700 clientapp=.Net SqlClient Data Provider hostname=BTSCLUSTER-SSO hostpid=9024 loginname=ABC\BTSAppHost isolationlevel=read committed (2) xactid=0 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2009-07-06 18:46:35.82 spid13s executionStack
2009-07-06 18:46:35.82 spid13s frame procname=mssqlsystemresource.sys.xp_userlock line=1 sqlhandle=0x0400ff7f722a782701000000000000000000000000000000
2009-07-06 18:46:35.82 spid13s xp_userlock
2009-07-06 18:46:35.82 spid13s frame procname=mssqlsystemresource.sys.sp_getapplock line=56 stmtstart=2762 stmtend=2958 sqlhandle=0x0300ff7f459c46090790ea005e9b00000100000000000000
2009-07-06 18:46:35.82 spid13s exec @result = sys.xp_userlock 0, @dbid, @DbPrincipal, @Resource, @mode, @owner, @LockTimeout
2009-07-06 18:46:35.82 spid13s frame procname=BizTalkMsgBoxDb.dbo.TDDS_GetTrackingRecord line=12 stmtstart=826 stmtend=954 sqlhandle=0x03000500ec026328b5b3f900219b00000100000000000000
2009-07-06 18:46:35.82 spid13s exec @retVal = sp_getapplock @tableName, 'Exclusive', 'Session'
2009-07-06 18:46:35.82 spid13s inputbuf
2009-07-06 18:46:35.82 spid13s Proc [Database Id = 5 Object Id = 677577452]
2009-07-06 18:46:35.82 spid13s process id=processf80d4478 taskpriority=0 logused=33156 waitresource=APPLICATION: 5:0:[[dbo].[TrackingData_1_1]]:(7665b1ae) waittime=1062 ownerId=580263509 transactionname=implicit_transaction lasttranstarted=2009-07-06T18:46:33.767 XDES=0x111a0d350 lockMode=S schedulerid=4 kpid=3076 status=suspended spid=185 sbid=0 ecid=0 priority=0 transcount=1 lastbatchstarted=2009-07-06T18:46:34.690 lastbatchcompleted=2009-07-06T18:46:34.683 clientapp=4B8BE232-EA81-43FA-96F4-9C0543D34272 hostname=BLOCK1 hostpid=6412 loginname=ABC\BIZAdmin isolationlevel=read committed (2) xactid=580263509 currentdb=5 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128058
2009-07-06 18:46:35.82 spid13s executionStack
2009-07-06 18:46:35.82 spid13s frame procname=mssqlsystemresource.sys.xp_userlock line=1 sqlhandle=0x0400ff7f722a782701000000000000000000000000000000
2009-07-06 18:46:35.82 spid13s xp_userlock
2009-07-06 18:46:35.82 spid13s frame procname=mssqlsystemresource.sys.sp_getapplock line=56 stmtstart=2762 stmtend=2958 sqlhandle=0x0300ff7f459c46090790ea005e9b00000100000000000000
2009-07-06 18:46:35.82 spid13s exec @result = sys.xp_userlock 0, @dbid, @DbPrincipal, @Resource, @mode, @owner, @LockTimeout
2009-07-06 18:46:35.82 spid13s frame procname=BizTalkMsgBoxDb.dbo.bts_InsertTrackingData line=35 stmtstart=2832 stmtend=2962 sqlhandle=0x0300050009f14b21b1bbf900219b00000100000000000000
2009-07-06 18:46:35.82 spid13s exec @retVal = sp_getapplock @tableName, 'Shared', 'Transaction'
2009-07-06 18:46:35.82 spid13s inputbuf
2009-07-06 18:46:35.82 spid13s Proc [Database Id = 5 Object Id = 558625033]
2009-07-06 18:46:35.82 spid13s process id=process108b4e478 taskpriority=0 logused=33536 waitresource=APPLICATION: 5:0:[[dbo].[TrackingData_1_0]]:(13020d16) waittime=1046 ownerId=580263503 transactionname=implicit_transaction lasttranstarted=2009-07-06T18:46:33.753 XDES=0xc0880b60 lockMode=S schedulerid=2 kpid=800 status=suspended spid=179 sbid=0 ecid=0 priority=0 transcount=1 lastbatchstarted=2009-07-06T18:46:34.653 lastbatchcompleted=2009-07-06T18:46:34.653 clientapp=4B8BE232-EA81-43FA-96F4-9C0543D34272 hostname=BLOCK1 hostpid=6412 loginname=ABC\BIZAdmin isolationlevel=read committed (2) xactid=580263503 currentdb=5 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128058
2009-07-06 18:46:35.82 spid13s executionStack
2009-07-06 18:46:35.82 spid13s frame procname=mssqlsystemresource.sys.xp_userlock line=1 sqlhandle=0x0400ff7f722a782701000000000000000000000000000000
2009-07-06 18:46:35.82 spid13s xp_userlock
2009-07-06 18:46:35.82 spid13s frame procname=mssqlsystemresource.sys.sp_getapplock line=56 stmtstart=2762 stmtend=2958 sqlhandle=0x0300ff7f459c46090790ea005e9b00000100000000000000
2009-07-06 18:46:35.82 spid13s exec @result = sys.xp_userlock 0, @dbid, @DbPrincipal, @Resource, @mode, @owner, @LockTimeout
2009-07-06 18:46:35.82 spid13s frame procname=BizTalkMsgBoxDb.dbo.bts_InsertTrackingData line=35 stmtstart=2832 stmtend=2962 sqlhandle=0x0300050009f14b21b1bbf900219b00000100000000000000
2009-07-06 18:46:35.82 spid13s exec @retVal = sp_getapplock @tableName, 'Shared', 'Transaction'
2009-07-06 18:46:35.82 spid13s inputbuf
2009-07-06 18:46:35.82 spid13s Proc [Database Id = 5 Object Id = 558625033]
2009-07-06 18:46:35.82 spid13s resource-list
2009-07-06 18:46:35.82 spid13s applicationlock hash=[dbo].[TrackingData_1_0]13020d16 databasePrincipalId=0 dbid=5 id=lock8ea51e80 mode=S
2009-07-06 18:46:35.82 spid13s owner-list
2009-07-06 18:46:35.82 spid13s owner id=processf80d4478 mode=S
2009-07-06 18:46:35.82 spid13s waiter-list
2009-07-06 18:46:35.82 spid13s waiter id=processbaeef8 mode=X requestType=wait
2009-07-06 18:46:35.82 spid13s waiter id=process108b4e478 mode=S requestType=wait
2009-07-06 18:46:35.82 spid13s applicationlock hash=[dbo].[TrackingData_1_1]7665b1ae databasePrincipalId=0 dbid=5 id=lock9739cc00 mode=S
2009-07-06 18:46:35.82 spid13s owner-list
2009-07-06 18:46:35.82 spid13s owner id=process108b4e478 mode=S
2009-07-06 18:46:35.82 spid13s waiter-list
2009-07-06 18:46:35.82 spid13s waiter id=processf3bac8 mode=X requestType=wait
2009-07-06 18:46:35.82 spid13s waiter id=processf80d4478 mode=S requestType=wait
July 10, 2009 at 12:40 pm
The fine art of troubleshooting deadlocks, huh?
You may want to start here http://www.mssqltips.com/tip.asp?tip=1036
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply