About locking and blocking in Dynamics AX and how to prevent it
The locking of records is necessary to ensure transactions are processed accurately and with a high level of concurrency. Unfortunately the more records are locked the higher is the chance other transactoins are getting blocked resulting in peformance reductions on the one hand and end user frustration on the other hand.
Dynamics AX and Microsoft SQL Server have built in functionalities that help identfiying and reducing locking and their effect the blocking. However not all of the features are self-explaining so I thought it is a good idea to give a little overview on some of them.
Concurrency models: OCC vs PCC in Queries
The concurrency model in Dynamics AX controls how records are locked when a select forupdate is executed.
| Pesimistic Concurrency Control (PCC) | Optimistic Concurrency Control (OCC) |
| Dynamics AX 3.0 supports only the pessimistic concurreny model in that a select forupdate results in a SQL statement which aquires an update lock.
This will cause all selected rows to be readable but you can’t update them or aquire any new update lock anymore. The update lock is held until the transaction is commit. |
Dynamics AX 4.0 supports and primarily uses the optimistic concurrency model in that a select forupdate results in a SQL statement with no lock.
Here all selected rows can be read and updateded besides the rows that were updated already. In order to detect an update conflicts the RecVersion field is checked by the Dynamics AX Kernel. |
|
while select forupdate custTable
// SELECT … WITH (UPDLOCK) |
while select forupdate custTable
// SELECT … WITH (NOLOCK) |
In both concurrency models the exclusive locking time is the same. The below table visualizes this.
The combination of the olive bars and teal bars below reprsent the selected dataset, every record that is retunred by your select forupate call. The teal bars are the updated, exclusively locked rows, the olive bars are the so far not updated rows. Over the time the amount of updated rows increases until at the end all rows are updated and the transaction is committed.
| All rows processed | ||||||
| 5/6 rows processed | ||||||
| 2/3 rows processed | ||||||
| 1/2 rows processed | ||||||
| 1/3 rows processed | ||||||
| 1/6 rows processed | ||||||
When using OCC the positive effect is obvious: There are much more rows available for updating (the olive bars). When we would have PCC the olive bars would not be available for updating, or in the words another process / tranaction would be blocked until all rows are processed. So when using OCC the chances for blocking is much smaller.
Read Committed Snapshot Isolation (RCSI)
When a record is exclusively locked also the reading is not possible (blocked) which can have a negative impact on the end user experience. Therefore it is recommended to enable the Read Committed Snapshot Isolation (RCSI) for the Dynamics AX database. RCSI is available on Microsoft SQL Server 2005 and higher. RCSI can help reducing locking and blocking also with Dynamics AX 3.0.
If you are not sure if RCSI is already enabled you can execute the following SQL query command to check (1 means on, 0 means off):
select name, is_read_committed_snapshot_on from sys.databases
RCSI is creating a version store in the TempDB to allow the reader to read from the version store instead of the exclusively locked row. As soon as you activate RCSI for performance reasons you should be sure to have the TempDB stored physically on your own dedicated disk. Please also make sure you have as many TempDB files as physical CPU cores exists on the SQL Server, to prevent contention within the TempDB.
Preventing locking and blocking and their implications on the transaction log
When looking at the following examples please remember one thing: For every ttscommit the Microsoft SQL Server transaction log is written to!
Not every write is an immediate physical write but definitely a logical write. The transaction log writer of Micosoft SQL Server is writing sequentially, this means it cannot be parallelized in any way. So the less transactions are opened the faster the changes are processed in general, but the longer the exclusive locking time will be.
The fast execution is because you are only writing one time to the transaction log of Microsoft SQL Server. At the same time you work in a very save way as you fully align with the concurrency model you are using.
static void UpdateCreditMax(Args _args)
{
CustTable custTable;
;
ttsbegin;
while select forupdate custTable where custTable.creditMax == 0
{
if (custTable.balanceMST() < 10000)
{
custTable.creditMax = 50000; custTable.update();
}
}
ttscommit;
}
{
CustTable custTable;
;
ttsbegin;
while select forupdate custTable where custTable.creditMax == 0
{
if (custTable.balanceMST() < 10000)
{
custTable.creditMax = 50000; custTable.update();
}
}
ttscommit;
}
Again please be aware that only this example has a low transactional workload as there is only one write to the transaction log file happening!
Example 2: Most roundtrips, short locking time
This example is (was) mainly usefull on Dynamcis AX 3.0 and causes a transaction overhead. This example makes not very much sense on Dynamics AX 4.0 or higher due to the changes in the concurrency model.
static void UpdateCreditMax(Args _args)
{
CustTable custTable;
CustTable updateableCustTable;
;
{
CustTable custTable;
CustTable updateableCustTable;
;
while select custTable where custTable .creditMax == 0
{
if (custTable.balanceMST() < 10000)
{
ttsbegin;
select forupdate updateableCustTable where updateableCustTable.AccountNum == custTable.AccountNum;
updateableCustTable.creditMax = 50000;
updateableCustTable.update();
ttscommit;
}
}
}
Example 3: Mix between Example 1 and Example 2
This example is mainly useful on Dynamics AX 4.0 and higher but causes an transaction overhead. In the select statemet below you could replace optimisticlock also with forupdate, but in this case you would not enforce optimistic concurrency.
static void UpdateCreditMax(Args _args)
{
CustTable custTable;
;
{
CustTable custTable;
;
while select optimisticlock custTable where custTable.creditMax == 0
{
if (custTable.balanceMST() < 10000)
{
ttsbegin;
custTable.creditMax = 50000;
custTable.update();
ttscommit;
}
}
}
Some comments on Example 2 and Example 3
Both examples are very effective in regards to locking and blocking. Overall these examples will perform more slowly than the Example 1 and there is also the chance of running into a last writer wins scenario. Also you cannot use them if you need all of the updates to be done or cancelled as a single transaction.
You should evaluate these examples only if you think that locking / blocking is an issue for you and if you are sure that you can accept the risks of partly bypassing your concurrency model.
When you are using this approach and run into performance issues on your Microsoft SQL Server you should have a look at the DMV SYS.DM_OS_WAIT_STATS. More precise look for the value WRITELOG.
select * from sys.dm_os_wait_stats order by wait_time_ms desc
If WRITELOG is very high compared to the other wait stats you should reduce code following Example 2 and 3 as much as possilbe and replace it with the code from Example 1. In general the mentioned DMV can also give you a good overview if you are suffering from a transactional overhead or if you have an issue on the drive where the log file resides.
Update_recordset is a very effective way to update multiple rows at once
The examples in the last section were about based update operations which have been very common in Dynamics AX 3.0. With Dynamics AX 4.0 you should use however set based update operations where ever possible. This is especially effective as Microsft SQL Server is basically working set based and not line based.
There are a lot less roundtrips between the Dynamics AX Kernel and the database if you are using the update_recordset command for updating multiple rows instead of the while select forupdate X++ equivalent.
