3.70 Deadlock

gerrypaul

We upgraded from 2.60B to 3.7 and we are now getting a lot of deadlocks when users are try to post purchase orders. We usually have up to 10 users posting at a time. Any Ideas?

kine

Are you using MS SQL or Navision Database Server?

We have customer with 3.70 on MS SQL and they have deadlocks too. Many deadlocks are between Service module and Sales module (Service ledger entry and other tables) etc... turn on tracking of deadlocks in SQL and look, which tables are cross-locking.

Deadlocks are on:

Document Dimensions vs. Sales line
Sales line vs. Reservation Entry
G/L Entry vs. Detailed Cust. Ledg. Entry
No. Series vs. Service Ledger Entry
Value Entry vs. Item Ledger Entry

Some deadlock we solved (Service ledger entry, Dimensions vs Sales Line) some i am now solving... :evil:

gerrypaul

We are running Navision SQL.

How are you solving the deadlocks?

kine

We are adding

record.LOCKTABLE;

or sometime, when we need

record.LOCKTABLE;
if RECORDLEVELLOCKING then
  record.FIND('+');

into appropriate position into code. Table locking rules must be kept (for example you must lock Value Entry before Item Ledger Entry etc....)

for example:

code 1:

...
rec1.FIND('+');
rec1.MODIFY;     //Rec1 will be locked
rec2.FIND('+');
rec2.MODIFY;     //Rec2 will be locked
....

code 2:

...
Rec2.FIND('+');
Rec2.Modify;      //Rec2 will be locked
Rec1.FIND('+');
Rec1.MODIFY;     //Rec1 will be locked
...

will lead into deadlock when runned simultaneously. In this case you must change code to:

code 2:

Rec1.LOCKTABLE;
Rec1.FIND('+');  //Rec1 will be locked
Rec2.MODIFY;    //Rec2 will be locked
Rec1.MODIFY;