Post reply

Help needed in resolving deadlocks

  • June 24, 2017 at 10:22 am

    #337897

    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:32 pm

    #1948050

    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

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • June 26, 2017 at 11:11 am

    #1948210

    Please post here. This is more appropriate for the administration forum. The other post is closed.

  • June 27, 2017 at 9:57 am

    #1948414

    Hi Shamantha4,

    Analysing Deadlock and its resolution requries indepth analysis of deadlock logs. Better if you could enable Deadlock Graph, as it gives relevant information in clear concise manner. 

    For complete undertsanding of deadlocks please go through following links:

    https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/

    https://support.microsoft.com/en-us/help/832524/sql-server-technical-bulletin---how-to-resolve-a-deadlock

    Reagrds,

    Ankur

  • June 27, 2017 at 7:22 pm

    #1948481

    Steve Jones - SSC Editor - Monday, June 26, 2017 11:11 AM

    Please post here. This is more appropriate for the administration forum. The other post is closed.

    You should probably move the other post because it has answers in it already.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply