Codeunit 80 Deadlock

zeninoleg

Good Day,
I am trying to solve one problem here and need some advice on how to tackle it. BAckground: 3.70A, SQL
I have a process that creates orders from the text file and ships them automatically. This process can take some time (10-20 minutes) due to the number of orders. On another hand there are users posting sales orders.
Sometimes during posting eiter user or Processing Client gets an error saying that "Your activity was deadlocked by another user modifying Document Dimensions Table". I have put a debugger on the Processing Computer and it stopped at this function of the codeunit 80:

CopyAndCheckDocDimToTempDocDim()
TempDocDim.RESET;
TempDocDim.DELETEALL;
DocDim.SETFILTER("Table ID",'%1|%2',DATABASE::"Sales Header",DATABASE::"Sales Line");
DocDim.SETRANGE("Document Type",SalesHeader."Document Type");
DocDim.SETRANGE("Document No.",SalesHeader."No.");
IF DocDim.FIND('-') THEN BEGIN
  REPEAT
    TempDocDim.INIT;
    TempDocDim := DocDim;
    TempDocDim.INSERT;
  UNTIL DocDim.NEXT = 0;
  TempDocDim.SETRANGE("Line No.",0);
  CheckDimComb(0);
END;
SalesLine2."Line No." := 0;
CheckDimValuePosting(SalesLine2);

SalesLine2.SETRANGE("Document Type",SalesHeader."Document Type");
SalesLine2.SETRANGE("Document No.",SalesHeader."No.");
SalesLine2.SETFILTER(Type,'<>%1',SalesLine2.Type::" ");
IF SalesLine2.FIND('-') THEN
  REPEAT                                               ///DEBUGGER WAS POINTING HERE
    TempDocDim.SETRANGE("Line No.",SalesLine2."Line No.");
    CheckDimComb(SalesLine2."Line No.");
    CheckDimValuePosting(SalesLine2);
  UNTIL SalesLine2.NEXT = 0;
TempDocDim.RESET;

Debugger pointed at the REPEAT line.
I am not sure how to approach this problem(I work for the end user, so i quite limited in tools).
Thanks a lot in advance

Lin

We too have had a problem with deadlocks in codeunit 80 to do with document dimensions. We have overcome this by adding the following code to ensure that the document dimention records that we are requiring are locked beforehand.

This is in the ONRUN trigger and the code we added is between the ITSD.009 comments

  IF Status = Status::Open THEN BEGIN
    TempInvoice := Invoice;
    TempShpt := Ship;
    TempReturn := Receive;
    CODEUNIT.RUN(CODEUNIT::"Release Sales Document",SalesHeader);
    Status := Status::Open;
    Invoice := TempInvoice;
    Ship := TempShpt;
    Receive := TempReturn;
    MODIFY;
    COMMIT;
    Status := Status::Released;
  END;

  // >> ITSD.009
  DocDim.RESET;
  DocDim.LOCKTABLE;
  DocDim.SETFILTER("Table ID",'37');
  DocDim.SETFILTER("Document Type",'%1',"Document Type");
  DocDim.SETFILTER("Document No.",'%1',"No.");
  IF DocDim.FIND('-') THEN
     REPEAT
     UNTIL DocDim.NEXT=0;  
  // << ITSD.009

  IF RECORDLEVELLOCKING THEN BEGIN
    IF WhseReceive THEN
      WhseRcptLine.LOCKTABLE;

Hope this helps :?

zeninoleg

Thanks Lin, I will try this solution. But what should be the approach to solve the deadlock. Lock the table beforehand? I am asking because I have few other places where it deadlocks and I want to be able to solve them. Is taht a trial and error method or there is a algorithm fro solving these problems
Also, what is the purpose of the repeat loop in your code?
:-k Thanks a lot!

Saint-Sage

Your process is trying to access the DocDim table while yourself or someone else is trying to aquire a lock on the table as well. This normally does not happen unless there are inappropriate locktable commands in Native, and SQL should be doing row level locking on a table that small.

There are a couple of things I would try here...
In the code:
REPEAT
TempDocDim.INIT;
TempDocDim := DocDim;
TempDocDim.INSERT;
UNTIL DocDim.NEXT = 0;

Add a TempDocDim.MODIFY and a COMMIT after you come out of the loop to ensure that locks are released.


Before you do the code :
TempDocDim.SETRANGE("Line No.",SalesLine2."Line No.");

I would try using the table lock command passing a TRUE,FALSE (the second always has to be false when running SQL Server!).


So
TempDocDim.LOCKTABLE(TRUE,FALSE);
TempDocDim.SETRANGE("Line No.",SalesLine2."Line No.");

This will ensure that you get the error "WHOEVER Has locked the Document Dimension Table" and you can see if it is you or someone else who is causing your code to deadlock. It will wait until any locks are resolved and then continue or allow you to cancel.

I hope this helps a little, you may need to check inside your CheckDimCom, and CheckDimValue functions to see if locks are occuring in there...

Good Luck!

zeninoleg

Thanks a lot guys! I will try your solutions and will post a result.

Marije_Brummel

Why are you trying to solve a deadlock issue?

When I start a performance project, deadlocks are last on my checklist.

Deadlocks are created by other performance issues in your system. Most of the time, when the other issues are resolved deadlocks magicaly dissapear.

Is your database tuned optimum for what you are using it? Tuning has several phases:

1. Checking your hardware & network
2. Checking SQL Server setup
3. Setting up a maintenance plan
4. Tuning your indexes
5. Optimizing code for cursors
6. Resloving (dead) lock issues.

My projects are usualy finished after 4.

Good luck and please let me know if you need any assistance.

zeninoleg

Thanks a lot Mark,
However, all I can do is change code , the rest is responsibility of our solutions center(I am a developer on the client's side). It just happened that it is my program(that creates sales orders from the text files) that comes up with such deadlock errors. We are not in the live stage yet, some development is still under way and database is not that heavly used by users.
However this program can run for 10-15 minutes every 4-5 hours, and when somebody else tries to create a sales order or post it that is when user of my process gets a deadlock problem.
But as I said, I am quite limited in what I can do
I will keep you posted
Thanks!

Marije_Brummel

Please correct me if I am wrong but as an end user you can do the following with some SQL knowledge & Tabledesigner

* Purchase SQL Perform Maintenance and create a Navision SQL maintenance plan.
* Go into the Tabledesigner, open the key menu and correct the biggest SQL issues every out-of-the box navision version has. There are several changes I make at every single customer.

5.0 should be more pre-tuned for SQL like this but 4.0SP3 is not!

Tuning does not need to be expensive.

Lin

I agree with alll that you are saying Mark however, I beleive alot of sites that have heavy use of dimensions are getting deadlocks in many standard functions of Navision on the docdim table.

Oleg, you need to check the locking order of tables through all of the major functions and ensure that tables are being locked in the same sequence. From memory, without going back through it, there are a few objects where the docdim table is locked (modify/insert) before the Sales line (or purchase line) and other objects where they are locked after causing deadlocks between the two processes.

Again this seems to only cause a problem if there is heavy use of dimensions.

Lin

Mark,

Do you have any tips for optimizing code for cursors. Are you achieving this by checking the SQL statement created is optimal or do you have some other way ?

Any suggestion would be appreciated

Mbad

Are there any modifications in any of the codeunits/tables that has to do with Dims?

From what i can see nothing is being modified, inserted or deleted where this code is run, so it baffles me that the deadlock would occur here, unless modifications to the code was made elsewhere.

Unless you are running this at night i would advise you not to lock the dim table, as suggested above.

zeninoleg

Unless you are running this at night i would advise you not to lock the dim table, as suggested above.

I think you are right becuse all other users will be locked out during the posting process. However at this point they want their orders to be posted in the real time - order comes in, gets entered, posted and shipped. I know it is pain.

Saint-Sage

In this case the table would remained locked for one to two seconds at most, and if it resolves your deadlock issues, without any other idea this might not be a bad fix. At the very least I would try it for testing and see who it reports the table is locked by, thats what the TRUE parameter does.