Question on MSSQL server table lock behaviour on INSERT

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?

Hi

Are you using Nav 5Sp1 or greater?

I think this behaviour can be explained if you consider the bulk insert feature.

Nav 5SP1 (and 2009…) don’t execute the “insert Into” when you call table.insert.

Help says:

Bulk Insert Constraints

If you want to write code that uses the bulk insert functionality, you must be aware of the following constraints.

Records are sent to SQL Server when the following occurs:

Records are not buffered if any of the following conditions are met:

You call some Validate like “Account No.” and Nav will not insert your dimension directly on “journal line dimension” table, but it fill a temp table (managed by “dimensionmanagement” codeunit). The temp table is flushed on “journalline.Insert(TRUE)” but you don’t call “Oninsert” table trigger. Then NAV will don’t insert anything on “Journal line dimension” table. Every internal call to “Journal line dimension” will not require a real bulk insert flush.

Ok, i hope i explained you that “journal Line” is not really inserted on SQL table and the dimensions are ignored. Without Insert, you don’t have locks.

Your second example introduce a different scenario. Your “JnlLineDim.INSERT;” will fill the internal “bulk insert” buffer, but when you will validate “Account No.”, nav will search the presence of dimensions. The Get/find will force nav to flush che “bulk insert” buffer to SQL.

I think this is the reason for your table lock behaviour.

I hope this will help you.

Bye

Matteo