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?