Post reply

Analysing Deadlock

  • June 19, 2007 at 4:36 am

    #177067

    Hey Guys,

    We have just migrated a large OLTP application from SQL2K to SQL2005 and we are seeing a sudden upsurge in deadlocks. I have used the Profiler to capture the deadlock chain/graph but to be honest I don't really understand what I'm looking at and BOL isn't much help.

    Could anyone help me decipher what all this means? It would be much appreciated. I've also posted the XDL file from the profiler at http://www.spibey.com/tmp/Deadlock.xdl

    Cheers

    2007-06-19 10:49:43.83 spid5s Deadlock encountered .... Printing deadlock information

    2007-06-19 10:49:43.83 spid5s Wait-for graph

    2007-06-19 10:49:43.83 spid5s

    2007-06-19 10:49:43.83 spid5s Node:1

    2007-06-19 10:49:43.83 spid5s PAGE: 10:1:13064 CleanCnt:3 Mode:U Flags: 0x2

    2007-06-19 10:49:43.83 spid5s Grant List 2:

    2007-06-19 10:49:43.83 spid5s Owner:0x00000000802AC240 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:218 ECID:0 XactLockInfo: 0x00000001ED24E0B8

    2007-06-19 10:49:43.83 spid5s SPID: 218 ECID: 0 Statement Type: UPDATE Line #: 215

    2007-06-19 10:49:43.83 spid5s Input Buf: RPC Event: Proc [Database Id = 10 Object Id = 1373247947]

    2007-06-19 10:49:43.83 spid5s Requested By:

    2007-06-19 10:49:43.83 spid5s ResType:LockOwner Stype:'OR'Xdes:0x00000002871C3A50 Mode: U SPID:234 BatchID:0 ECID:0 TaskProxy0x0000000236260598) Value:0x4ddce5c0 Cost0/10228)

    2007-06-19 10:49:43.83 spid5s

    2007-06-19 10:49:43.83 spid5s Node:2

    2007-06-19 10:49:43.83 spid5s PAGE: 10:1:38103 CleanCnt:2 Mode:UIX Flags: 0x2

    2007-06-19 10:49:43.83 spid5s Grant List 2:

    2007-06-19 10:49:43.83 spid5s Owner:0x0000000094527D80 Mode: UIX Flg:0x0 Ref:0 Life:02000000 SPID:234 ECID:0 XactLockInfo: 0x00000002871C3A88

    2007-06-19 10:49:43.83 spid5s SPID: 234 ECID: 0 Statement Type: UPDATE Line #: 118

    2007-06-19 10:49:43.83 spid5s Input Buf: RPC Event: Proc [Database Id = 10 Object Id = 1357247890]

    2007-06-19 10:49:43.83 spid5s Requested By:

    2007-06-19 10:49:43.83 spid5s ResType:LockOwner Stype:'OR'Xdes:0x00000001ED24E080 Mode: U SPID:218 BatchID:0 ECID:0 TaskProxy0x0000000305B06598) Value:0x944efc40 Cost0/400)

    2007-06-19 10:49:43.83 spid5s

    2007-06-19 10:49:43.83 spid5s Victim Resource Owner:

    2007-06-19 10:49:43.83 spid5s ResType:LockOwner Stype:'OR'Xdes:0x00000001ED24E080 Mode: U SPID:218 BatchID:0 ECID:0 TaskProxy0x0000000305B06598) Value:0x944efc40 Cost0/400)

  • June 19, 2007 at 4:45 am

    #713767

    Your key lies in the:

    2007-06-19 10:49:43.83 spid5s Input Buf: RPC Event: Proc [Database Id = 10 Object Id = 1373247947]

    and vitim:

    2007-06-19 10:49:43.83 spid5s Input Buf: RPC Event: Proc [Database Id = 10 Object Id = 1357247890]

    What are the updates hitting those two tables and how are they related?

    What are the query plans like for the updates?

    If there are questionable indexes, SQL2k5 uses a different method to 2000 to join two tables with avaliable indexes. While normally better, this may be creating your problem.

    First call - check the query plan of the two queries. Any scans, try get rid of them (Indexes needed)

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • June 19, 2007 at 5:49 am

    #713782

    Please review these links for more information on deadloack and how to troubleshoot them.

    http://support.microsoft.com/kb/118552/en-us

    FIX: A deadlock occurs and a query never finishes when you run the query on a computer that is running SQL Server 2005 and has multiple processors

    How to reduce lock contention in SQL Server

    How to resolve  deadloack : http://support.microsoft.com/kb/832524/en-us

    Minaz

    "More Green More Oxygen !! Plant a tree today"

Viewing 3 posts - 1 through 2 (of 2 total)

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