How to solve Table Locking Issues in Business Central
Table Locking is a very common problem in Microsoft Dynamics 365 NAV / Business Central. If the problem is understood correctly and the right technique is used, Locking Errors can be solved very easily. This post explains different techniques that can be used to solve Locking errors.
What is a Transaction?
Transaction is a unit of work which contains one or more operations.
The following are the some of the events that creates a new Transaction in Business Central:
Saving a record in a Page - Logic written in the OnInsert, OnModify, and OnRename triggers.
Executing Action Button in a Page – Logic written in Run Object’s OnRun trigger, and OnAction trigger.
On Execution of a Web Service – Logic written in Codeunits, XMLports, and Report
Transaction is automatically closed (committed) on completion of executing the logic. When a COMMIT statement is executed, the current Transaction is closed, and a new Transaction will begin. When an ERROR statement is executed, the current Transaction is terminated and all the changes made by the Transaction are rolled back.
About Locks
Database Locks are for your good only. Like a parent protecting siblings fighting with each other, Database Lock protects the data from being overwritten by the other Transactions executing at the same time.
When a Transaction tries to access the data that is being Locked by another Transaction, SQL Server blocks that Transaction. And when a Transaction is blocked for more than 10 seconds (Lock Time-out), it will be terminated automatically with the "Table was Locked by another User" error.
There is a setting called Lock Time-out in Alter Database / Advanced tab. Default value for the Lock Time-out is
10 seconds. Lock Time-out feature can be turned off for long running processes by calling Database.LockTimeout(false) function in the CAL / AL code.
In Business Central / Dynamics NAV, the SQL Server uses Row Level Locking, which means two Users can modify the data in the same table simultaneously but cannot modify the same data (rows).
Note: LockTable, FindSet (ForUpdate: true) statements also apply Row Level Locks.
Row Level Locking is not effective for Ledger table
Most of the Ledger Entry tables in Business Central / Dynamics NAV has "Entry No." as the Primary Key. To generate Primary Key in most of the places, following algorithm is used.
LedgerEntry.LOCKTABLE;
IF LedgerEntry.FINDLAST THEN
NextEntryNo := LedgerEntry.”Entry No.” + 1
ELSE
NextEntryNo := 1;For some reason this algorithm is used everywhere. This algorithm will not allow "Row Level Locking" to work effectively. When this type of code is being executed simultaneously, only first Transaction gains access to the last row of the table. All other Transactions which need the last row will be Blocked until the first Transaction finished.
Note: Wherever possible, it is recommended to use the Auto increment property to generate Primary Key (Entry No.) instead of using the above algorithm.
Common Errors
Business Central / Dynamics NAV throws the following type of errors related to this topic.
Another user has modified the record
A record was locked by another user
The activity was deadlocked with another user
Another user has modified the record
This error message is related to concurrency control, and it has nothing to do with Database Locks.
Business Central / Dynamics NAV maintains a Timestamp column in each table in the SQL Server to maintain row versions. Whenever a record is being Inserted or Modified, system will automatically update a new version number in the Timestamp column. And when a record is being modified, system will check the Timestamp of the current record with the previously saved Record. If it doesn’t match, the system will throw the given error message.
It is very very rare that it happens because another user has actually modified the record. It is usually because of the bad code.
Business Central: The changes to the Customer record cannot be saved because some information on the page is not up-to-date.
Dynamics NAV: Another user has modified the record for this Customer
The following code generates "Another user has modified the record" error message:
procedure ModifyCustomer()
var
Customer: Record Customer;
begin
Customer.FindFirst();
SetName(Customer);
SetEMail(Customer);
Message('Name: %1, E-Mail: %2', Customer.Name, Customer."E-Mail");
end;
local procedure SetName(Customer: Record Customer)
begin
Customer.Name := StrSubstNo('Customer - %1', Format(Random(10000)));
Customer.Modify();
end;
local procedure SetEMail(Customer: Record Customer)
begin
Customer."E-Mail" := StrSubstNo('cust.%1@test.com', Format(Random(10000)));
Customer.Modify();
end;In the above code, the SetEMail function is not aware that the row version of Customer record has changed in the SetName function, and it is trying to change the old version of the Customer record. Concurrency Control doesn't allow to modify the record of an older version.
This problem can be fixed by changing Customer parameter to reference type (var Cust: Record Customer), so that all these functions are working on a single record variable instance.
local procedure SetName(var Customer: Record Customer)
begin
Customer.Name := StrSubstNo('Customer - %1', Format(Random(10000)));
Customer.Modify();
end;
local procedure SetEMail(var Customer: Record Customer)
begin
Customer."E-Mail" := StrSubstNo('cust.%1@test.com', Format(Random(10000)));
Customer.Modify();
end;A record was locked by another user
When a Transaction is blocked for more than 10 seconds (Lock Time-out), it will be terminated automatically with the following error.
Business Central: The operation could not complete because a record in the Sales Line table was locked by another user. Please retry the activity.
Dynamics NAV: The operation could not complete because a record was locked by another user
If the below code is executed in two clients simultaneously, it will generate the above error.
procedure First()
var
SalesLine: Record "Sales Line";
Window: Dialog;
x: Integer;
begin
Window.Open('#1##########');
Window.Update(1, 'Waiting ...');
SalesLine.FindSet();
repeat
x += 1;
Window.Update(1, StrSubstNo('Processing - %1', SalesLine."No."));
SalesLine.Modify();
Sleep(1000);
until (SalesLine.Next() = 0) or (x > 15);
Window.Close();
Message('Execution completed successfully.');
end;
This code is going to be executed for more than 20 seconds. 2nd client will exceed the Lock Timeout limit, and it will get the Locking error.
The below code can be executed in two clients simultaneously without any Locking errors:
procedure Second()
var
SalesLine: Record "Sales Line";
Window: Dialog;
x: Integer;
begin
Window.Open('#1##########');
Window.Update(1, 'Waiting ...');
SalesLine.FindSet();
repeat
x += 1;
Window.Update(1, StrSubstNo('Processing - %1', SalesLine."No."));
SalesLine.Modify();
Sleep(1000);
Commit(); // To generate a new Transaction
Sleep(10); // Allow blocked Transactions to get priority
until (SalesLine.Next() = 0) or (x > 15);
Window.Close();
Message('Execution completed successfully.');
end;There is a big difference between the first and the second code, though only two lines of code is different. The Code written in the first function creates a Transaction at FindSet() statement and it is going to last till the end for more than 20 seconds. Whereas the Code written in the second function is releasing the Transaction in each iteration, though it is creates a Transaction at FindSet() statement.
Transaction that takes more than 10 seconds can cause locking errors. To avoid locking errors, Transaction should be finished as quickly as possible.
The activity was deadlocked with another user
This error occurs when two transactions are blocking each other.
If LockingSequence1 and LockingSequence2 functions from the below code are executed simultaneously in two different clients, one of the Transactions will be terminated with the deadlock error.
procedure LockingSequence1()
var
SalesHeader: Record "Sales Header";
SalesLine: Record "Sales Line";
begin
Counter1 += 1;
SalesLine.LockTable();
SalesLine.FindFirst();
Sleep(5000);
if (Counter1 > 1) then
Message('The code with the "Sales Line, Sales Header" locking sequance has been executed more than once.');
SalesHeader.LockTable();
SalesHeader.FindFirst();
Counter1 := 0;
Message('Executed successfully.');
end;
procedure LockingSequence2()
var
SalesHeader: Record "Sales Header";
SalesLine: Record "Sales Line";
begin
Counter2 += 1;
SalesHeader.LockTable();
SalesHeader.FindFirst();
Sleep(5000);
if (Counter2 > 1) then
Message('The code with the "Sales Header, Sales Line" locking sequance has been executed more than once.');
SalesLine.LockTable();
SalesLine.FindFirst();
Counter2 := 0;
Message('Executed successfully.');
end;
Note: I have just noticed, if deadlock occurs in Business Central, it is automatically reattempting the failed transaction.
Deadlock errors can be fixed by correcting the sequence of Table Locking. The above code can be fixed by following the same Locking sequence in both functions.
Though it is not a good practice, the below code will fix the problem by forcefully locking the tables in the needed sequence.
procedure LockingSequence2Fixed()
var
SalesHeader: Record "Sales Header";
SalesLine: Record "Sales Line";
begin
Counter3 += 1;
SalesLine.LockTable();
SalesLine.FindFirst();
SalesHeader.LockTable();
SalesHeader.FindFirst();
Sleep(5000);
if (Counter3 > 1) then
Message('The code with the "Sales Line*, Sales Header" locking sequance has been executed more than once.');
SalesLine.LockTable();
SalesLine.FindFirst();
Counter3 := 0;
Message('Executed successfully.');
end;Job Queues
Job Queues can be used to solve Locking Errors. The Transactions that can be executed in the background, can be queued to Job Queue. Job Queue Entry table holds the parameters in order to execute the Transaction and Job Queue processes the Job Queue Entries sequentially.
Locking Errors only occur, when more than one Transactions are executing simultaneously. No Locking Errors would occur, if they are executed sequentially. If the Transactions are delegated to Job Queue to process through Job Queue Entry, the Locking Error problem is almost solved.
Code Samples in Action
You can download and try the source code from GitHub.
Conclusion
This post is covering some of the problems related to Table Locking issues, but not all of them. There are various other factors that impacts performance and creates locking issues. The key thing to resolve Locking Errors is by optimizing the time to complete the Transactions. This can be done by optimizing the Code, the SQL Queries, the SQL Indexes, and the process.
Happy Coding!!!