Deadlocking

jwilder

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.

kriki

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.

jwilder

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.

Alex_Chow

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.

kine

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...

tenantfile

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.

stryk

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