mibuso.com
Home› SQL Performance
Options

Deadlocking

jwilderjwilder Member Posts: 263
edited 2011-04-23 in SQL Performance
Over the years I have resolved many deadlocking issues. Normally the problem stems from either an incorrect locking sequence of 2 processes or some bad code. There are all sorts of ways to solve deadlocking in NAV such as putting LockTables or Commits in the code, Optimzing the Code (using IsEmpty where possible so there is no lock, using a proper key, etc...) or more. I have always use Client Monitor to track these probems down. I am now stuck and need more then what client monitor can give me.

I have 2 processes: Posting an Order (many table locks) and Creating a Settlement (only 3 table locks) for ChargeLogic. The locking sequences for each process are the same. The code exectues quickly with 99% of the statements running under 16ms. I think I need to turn to SQL Server now to identify the deadlock so I can see how to fix it.

Can someone tell me the simplest way to monitor deadlocking in SQL? I have used SQL Profiler before but am not an expert with it.
Jason Wilder
jwilder@stonewallkitchen.com

Comments

  • Options
    krikikriki Member, Moderator Posts: 9,096
    Run SQL Profiler.
    You need the following events in "Locks":
    -Deadlock graph
    -Lock:Deadlock
    -Lock:Deadlock Chain

    And you need specially TextData. The Deadlock graph will be the most interesting: it shows a nice graph with lots of info.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Options
    jwilderjwilder Member Posts: 263
    Thank you this has proven what I was suspecting. The locks are all key locks. The table being deadlocked is in an addon called ChargeLogic where there is a table called EFT Transaction. This table has 40 keys! I think the only way to solve this is to reduce the number of keys. Do you agree or is there a way that I can write some code to solve this. I have already put in locktables in the NAV code but they do not seem to be helping.
    Jason Wilder
    jwilder@stonewallkitchen.com
  • Options
    Alex_ChowAlex_Chow Member Posts: 5,063
    jwilder wrote:
    Thank you this has proven what I was suspecting. The locks are all key locks. The table being deadlocked is in an addon called ChargeLogic where there is a table called EFT Transaction. This table has 40 keys! I think the only way to solve this is to reduce the number of keys. Do you agree or is there a way that I can write some code to solve this. I have already put in locktables in the NAV code but they do not seem to be helping.

    Since the deadlock is happening on an ISV product, you should ask the ISV what you should do to resolve this.
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • Options
    kinekine Member Posts: 12,562
    And you do not need to change number of keys, but optimize indexes for the keys (disable them or change them), and this is different thing...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    tenantfiletenantfile Member Posts: 2
    Yes very much right myself also face the same problem pf deadlock . And it seems to be very much irretating to work in the dedlock condition.
  • Options
    strykstryk Member Posts: 645
    Check out this one:
    http://dynamicsuser.net/blogs/stryk/archive/tags/Locking/default.aspx

    I hope this could help you to identify the blocking and deadlocking issues.

    Cheers,
    Jörg
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
Sign In or Register to comment.