June 24, 2017 at 10:17 am
Hello All,
We are getting frequent deadlocks on one of sql server instance.
Any help\suggestions will be greatly appreciated.
Below is the deadlock info.
2017-06-23 12:05:26.760 spid28s deadlock-list
2017-06-23 12:05:26.760 spid28s deadlock victim=process5dfe2c8
2017-06-23 12:05:26.760 spid28s process-list
2017-06-23 12:05:26.760 spid28s process id=process5dfe2c8 taskpriority=0 logused=0 waitresource=KEY: 20:72057594113163264 (20108c853da6) waittime=4502 ownerId=3403758819 transactionname=UPDATE lasttranstarted=2017-06-23T12:05:21.487 XDES=0x16783b740 lockMode=U schedulerid=3 kpid=91392 status=suspended spid=149 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2017-06-23T12:05:21.487 lastbatchcompleted=2017-06-23T12:05:21.487 clientapp=.Net SqlClient Data Provider hostname=xyz hostpid=22496 loginname=*** isolationlevel=read committed (2) xactid=3403758819 currentdb=20 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056
2017-06-23 12:05:26.760 spid28s executionStack
2017-06-23 12:05:26.760 spid28s frame procname=KSE.dbo.DequeueOrders line=11 stmtstart=204 stmtend=1212 sqlhandle=0x030014003ae5955d944a77018fa300000100000000000000
2017-06-23 12:05:26.760 spid28s UPDATE Orders WITH(UPDLOCK, READPAST)
2017-06-23 12:05:26.760 spid28s SET OrderStatusID = 1
2017-06-23 12:05:26.760 spid28s OUTPUT inserted.OrderId, inserted.ServiceProviderID, inserted.OrderStatusID, inserted.ReprocessCount, inserted.EffectiveDate, inserted.CreateDate, inserted.LastModifiedDate, inserted.SourceName, inserted.OrderXML
2017-06-23 12:05:26.760 spid28s WHERE OrderId in (SELECT TOP(@MaxRows) OrderId
2017-06-23 12:05:26.760 spid28s FROM Orders WITH (UPDLOCK)
2017-06-23 12:05:26.760 spid28s WHERE OrderStatusID = 0 AND
2017-06-23 12:05:26.760 spid28s EffectiveDate <= GETDATE()
2017-06-23 12:05:26.760 spid28s ORDER BY CreateDate)
2017-06-23 12:05:26.760 spid28s inputbuf
2017-06-23 12:05:26.760 spid28s Proc [Database Id = 20 Object Id = 1570104634]
2017-06-23 12:05:26.760 spid28s process id=processe0da7288 taskpriority=0 logused=0 waitresource=KEY: 20:72057594113163264 (e305804383b4) waittime=4738 ownerId=3403759704 transactionname=UPDATE lasttranstarted=2017-06-23T12:05:22.013 XDES=0x1e9810e90 lockMode=U schedulerid=1 kpid=92780 status=suspended spid=128 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2017-06-23T12:05:22.013 lastbatchcompleted=2017-06-23T12:05:22.013 clientapp=.Net SqlClient Data Provider hostname=XYZ hostpid=8644 loginname=*** isolationlevel=read committed (2) xactid=3403759704 currentdb=20 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056
2017-06-23 12:05:26.760 spid28s executionStack
2017-06-23 12:05:26.760 spid28s frame procname=KSE.dbo.DequeueOrders line=11 stmtstart=204 stmtend=1212 sqlhandle=0x030014003ae5955d944a77018fa300000100000000000000
2017-06-23 12:05:26.760 spid28s UPDATE Orders WITH(UPDLOCK, READPAST)
2017-06-23 12:05:26.760 spid28s SET OrderStatusID = 1
2017-06-23 12:05:26.760 spid28s OUTPUT inserted.OrderId, inserted.ServiceProviderID, inserted.OrderStatusID, inserted.ReprocessCount, inserted.EffectiveDate, inserted.CreateDate, inserted.LastModifiedDate, inserted.SourceName, inserted.OrderXML
2017-06-23 12:05:26.760 spid28s WHERE OrderId in (SELECT TOP(@MaxRows) OrderId
2017-06-23 12:05:26.760 spid28s FROM Orders WITH (UPDLOCK)
2017-06-23 12:05:26.760 spid28s WHERE OrderStatusID = 0 AND
2017-06-23 12:05:26.760 spid28s EffectiveDate <= GETDATE()
2017-06-23 12:05:26.760 spid28s ORDER BY CreateDate)
2017-06-23 12:05:26.760 spid28s inputbuf
2017-06-23 12:05:26.760 spid28s Proc [Database Id = 20 Object Id = 1570104634]
2017-06-23 12:05:26.760 spid28s resource-list
2017-06-23 12:05:26.760 spid28s keylock hobtid=72057594113163264 dbid=20 objectname=KSE.dbo.Orders indexname=IX_Orders_OrderStatusID_EffectiveDate id=lock29379ed80 mode=U associatedObjectId=72057594113163264
2017-06-23 12:05:26.760 spid28s owner-list
2017-06-23 12:05:26.760 spid28s owner id=processe0da7288 mode=U
2017-06-23 12:05:26.760 spid28s waiter-list
2017-06-23 12:05:26.760 spid28s waiter id=process5dfe2c8 mode=U requestType=wait
2017-06-23 12:05:26.760 spid28s keylock hobtid=72057594113163264 dbid=20 objectname=KSE.dbo.Orders indexname=IX_Orders_OrderStatusID_EffectiveDate id=lock24f28d600 mode=U associatedObjectId=72057594113163264
2017-06-23 12:05:26.760 spid28s owner-list
2017-06-23 12:05:26.760 spid28s owner id=process5dfe2c8 mode=U
2017-06-23 12:05:26.760 spid28s waiter-list
2017-06-23 12:05:26.760 spid28s waiter id=processe0da7288 mode=U requestType=wait
June 24, 2017 at 1:33 pm
Please don't cross post. It only fragments replies and makes it harder to get to a resolution.
Direct replies to https://www.sqlservercentral.com/Forums/1883042/Help-needed-in-resolving-dead-lock
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply