Question on table locks on Insert Records on MSSQL Server DB

jordi79

TEST1

I have created 2 codeunits to test how SQL Server handles concurrent inserts on 2 same tables.

//Codeunit 99998
d.OPEN('##1############');

FOR n := 1 TO 10000 DO BEGIN
  GJL.INIT;
  GJL."Line No." := n;
  GJL.VALIDATE("Journal Template Name", 'GENERAL');
  GJL.VALIDATE("Journal Batch Name", 'BATCH1');
  GJL.VALIDATE("Posting Date", TODAY);
  GJL.VALIDATE("Document Type", GJL."Document Type"::Invoice);
  GJL.VALIDATE("Document No.", 'ABC123');
  GJL."Account Type" := GJL."Account Type"::"G/L Account";
  GJL.VALIDATE("Account No.",  '101010');
  GJL.VALIDATE(Amount, n);
  GJL.VALIDATE("Bal. Account No.", '101010');
  GJL.INSERT;
  d.UPDATE(1, n);
END;

//Codeunit 99999

d.OPEN('##1############');

FOR n := 1 TO 10000 DO BEGIN
  GJL.INIT;
  GJL."Line No." := n;
  GJL.VALIDATE("Journal Template Name", 'GENERAL');
  GJL.VALIDATE("Journal Batch Name", 'BATCH2');
  GJL.VALIDATE("Posting Date", TODAY);
  GJL.VALIDATE("Document Type", GJL."Document Type"::Invoice);
  GJL.VALIDATE("Document No.", 'ABC123');
  GJL."Account Type" := GJL."Account Type"::"G/L Account";
  GJL.VALIDATE("Account No.",  '101010');
  GJL.VALIDATE(Amount, n);
  GJL.VALIDATE("Bal. Account No.", '101010');
  GJL.INSERT;
  d.UPDATE(1, n);
END;

d.CLOSE;

The 2 codeunits above inserts genjnllines into "BATCH1" and "BATCH2" gen. journal batches. After creating these 2 codeunits, I opened 2 clients connecting to a SQL Server, and run both of them at the same time. I have no problems running these 2 codeunits concurrently. GenJnlLine is inserted into the 2 batches BATCH1 and BATCH2.

TEST2

For test 2, the 2 codeunits are as below:

//Codeunit 99998
d.OPEN('##1############');

FOR n := 1 TO 10000 DO BEGIN
  GJL.INIT;
  GJL."Line No." := n;
  GJL.VALIDATE("Journal Template Name", 'GENERAL');
  GJL.VALIDATE("Journal Batch Name", 'BATCH1');
  GJL.VALIDATE("Posting Date", TODAY);
  GJL.VALIDATE("Document Type", GJL."Document Type"::Invoice);
  GJL.VALIDATE("Document No.", 'ABC123');
  GJL."Account Type" := GJL."Account Type"::"G/L Account";
  GJL.VALIDATE("Account No.",  '101010');
  GJL.VALIDATE(Amount, n);
  GJL.VALIDATE("Bal. Account No.", '101010');
  GJL.INSERT;

  JnlLineDim.INIT;
  JnlLineDim."Table ID" := 81;
  JnlLineDim."Journal Template Name" := 'GENERAL';
  JnlLineDim."Journal Batch Name" := 'BATCH1';
  JnlLineDim."Journal Line No." := n;
  JnlLineDim."Dimension Code" := 'COSTCENTER';
  JnlLineDim."Dimension Value Code" := '1003';
  JnlLineDim.INSERT;

  d.UPDATE(1, n);
END;

//Codeunit 99999

d.OPEN('##1############');

FOR n := 1 TO 10000 DO BEGIN
  GJL.INIT;
  GJL."Line No." := n;
  GJL.VALIDATE("Journal Template Name", 'GENERAL');
  GJL.VALIDATE("Journal Batch Name", 'BATCH2');
  GJL.VALIDATE("Posting Date", TODAY);
  GJL.VALIDATE("Document Type", GJL."Document Type"::Invoice);
  GJL.VALIDATE("Document No.", 'ABC123');
  GJL."Account Type" := GJL."Account Type"::"G/L Account";
  GJL.VALIDATE("Account No.",  '101010');
  GJL.VALIDATE(Amount, n);
  GJL.VALIDATE("Bal. Account No.", '101010');
  GJL.INSERT;

  JnlLineDim.INIT;
  JnlLineDim."Table ID" := 81;
  JnlLineDim."Journal Template Name" := 'GENERAL';
  JnlLineDim."Journal Batch Name" := 'BATCH2';
  JnlLineDim."Journal Line No." := n;
  JnlLineDim."Dimension Code" := 'COSTCENTER';
  JnlLineDim."Dimension Value Code" := '1003';
  JnlLineDim.INSERT;

  d.UPDATE(1, n);
END;

d.CLOSE;

These 2 codeunits is similar with the 2 codeunits in TEST1, but this time instead of inserting just GenJnlLine, it also inserts JnlLineDim. This time when these 2 codeunits are run concurrently, I got the error:

---

Microsoft Dynamics NAV

---

The Journal Line Dimension table cannot be changed because it is locked by another user.

Wait until the user is finished and then try again.

---

OK

---

The question here is, why did the error no appear in TEST1, but appeared in TEST2? Does SQL Server table lock behave differently, if there are 2 insert statements in 1 codeunit?

kine

I think because the first example is fast enough to finish before the timeout. The second one takes longer than timeout, thus you will get the timeout error.

kapamarou

You can start debugging both clients to see what happens. Start the first one, let it make a couple of inserts and then leave it at a breakpoint. Then start debugging your second client and see what happens.

Keep in mind that SQL Server won't lock only the record you are inserting. It will also lock (I think at the best scenario) the previous and the next one.

So debug it and use also the SQL managment studio to investigate your locks...

kine

And because you are not reading and there is something called "Delayed inserts", the table could be locked only short period of time after the transaction is commiting... ;-)

You need to take into account different things together 8)

jordi79

In addition to the 2 tests above, I modified TEST2 a little (call it TEST3), instead of inserting JnlLineDim dimensions, I modified to insert Customer table. And now, it was able to insert without any locking errors with both clients running concurrently on SQL.

I can make a simple conclusion here. The reason why TEST2 triggered the locking would be because, there are certain validations (e.g. GenJnlLine.Validate("no.", 'XXXXX') itself that is triggering modifications to JnlLineDim. So when I make explicit changes to JnlLineDim in TEST2, this triggered the table locking error.

This is the only explanation I can make, that would justify TEST3 giving a positive result (no table lock error) and TEST2 giving a negative result. (prompting a table lock error). Apart from that, I cannot provide any more explanations.

rdebath

jordi79

You need to use the client monitor to see what statements are being locked, eg to see if there any range locks applied. They are the usual problem as they HAVE TO go from record to record but Navision normally places them on a range of values. The result is that one record gets locked past the range in both the previous & next batches (or lines).

The inserts themselves should normally be fine, though if you don't have the 'row lock only' set on the alter database page in NAV you may be getting page locks.

In this case I would guess you could be getting a page lock on the second index of the j-dim table. The one that's only used if you're deleting a dimension value. Try removing that index.

BTW at this point (debugging) the 'Delayed Inserts' that Kamil mentioned are a pain. If you want to flush them to the SQL server use

IF GJL.COUNTAPPROX = 0 THEN;

The advantage of that over other methods is that it won't set any extra locks.

jordi79

I have debugged the code, and it stopped at:

CODEUNIT 408: UpdateJnlLineDefaultDim(TableID,JnlTemplateName,JnlBatchName,JnlLineNo,AllocationLineNo,GlobalDim1Code,GlobalDim2Code)

01 GetGLSetup;
02 JnlLineDim.SETRANGE("Table ID",TableID);
03 JnlLineDim.SETRANGE("Journal Template Name",JnlTemplateName);
04 JnlLineDim.SETRANGE("Journal Batch Name",JnlBatchName);
05 JnlLineDim.SETRANGE("Journal Line No.",JnlLineNo);
06 JnlLineDim.SETRANGE("Allocation Line No.",AllocationLineNo);
07 IF NOT JnlLineDim.ISEMPTY THEN
08  JnlLineDim.DELETEALL;
09 GlobalDim1Code := '';
10 GlobalDim2Code := '';
11 IF TempDimBuf2.FINDSET THEN BEGIN
...

The code above is triggered by GenJnlLine.VALIDATE("No.", 'XYZ'); The debugger stopped at line 07.

After I remarked all the setranges coming from validation of GenJnlLine then I was able to run TEST2 with positive results.

Ok, this could be probably caused the upper and lower limit setranges lock described in :

http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2008/02/06/record-level-locking-in-the-sql-server-option-for-microsoft-dynamics-nav.aspx

But what still puzzles me, is why this error did not happen for TEST1? As TEST1 also had 2 codeunits running validations on GenJnlLine, and this means that there are 2 codeunits modifying the JnlLineDim table, but with no errors. And the error only happens when an "explicit" change to JnlLineDim is done in TEST2.

rdebath

jordi79 wrote:

But what still puzzles me, is why this error did not happen for TEST1? As TEST1 also had 2 codeunits running validations on GenJnlLine, and this means that there are 2 codeunits modifying the JnlLineDim table, but with no errors. And the error only happens when an "explicit" change to JnlLineDim is done in TEST2.

Because Navision made silly mistake when they added the SQL interface and Microsoft never fix anything, they just paper over the cracks.

Because 'UpdateNoLocks' doesn't. Navision made what has turned out to be a silly mistake when they added the SQL interface. Whenever you write to the database the runtime does the equivalent of a LOCKTABLE on the specific table. With the Native DB this is required because it's the only way to lock anything but with SQL the changed records will be locked anyway and consistency will be preserved. All the LOCKTABLE does is cause every subsequent READ from the database to place locks that block sessions that try to modify records that are even BESIDE the ones this session just looks at.

The reason to use read locks in a database is so you can lock things you are going to 'increment' or alter in a way that depends on the current value. The SQL Serializable and REPEATABLE READ locking levels are designed for just this, using them for an entire transaction (as the runtime does) is only a reasonable idea when you absolutely must have only committed data and don't care about multi user performance.

The way to get best performance is to reduce the locking to the minimum that enforces the consistency of the data. UpdateNoLocks promises this, and is almost there, all that would need is to get rid of the implicit LOCKTABLE on any modify and add a method of locking ONLY the next statement on a table (eg: use "LOCKTABLE(FALSE)" as the syntax; it would be safe on earlier clients.) This would allow for the manual locking that UpdateNoLocks promises.

It turns out that Navision would need almost no locking above the basic provided by SQL for this, plus a little more to prevent deadlocks (Mostly, both, because of the Indexes views). If the required changes include a fix for number series (easy) and Entry Numbers (maybe autoincrement numbers, possibly a variation on a Number series) it turns out that Navision could support thousands of users on ordinary hardware. After all the processing power available to the classic client would scale out to every single PC you own.

This gives Microsoft a problem; Axapta. Right now a Navision system can support maybe 20 users actively posting without going to extreme measures like effectively putting the database onto a ramdisk. That leaves a huge gap for Axapta. If Navision could support 300 users on any old POS hardware Axapta would be dead, dead; dead. And nothing of value was lost.

PS: This message seems to have diverted at the end, but, I'll let it stand.