The Difference between Optimistic concurrency control(OCC) and Pessimistic concurrency control(PCC).
- Optimistic concurrency control (OCC) helps increase the database performance.
- Optimistic concurrency control locks records only from the time the real update operation is started (i.e. during update process). So, the records are locked for a shorter duration of time and will be available for other process (for update process).
- OCC makes it possible for other processes to update the record even after the record is fetched. Here the disadvantage is if another process updates the same records which are fetched, then the update fails and retry will take more time which hits the performance.
- In OCC fewer resources are being used to hold the locks during the update process.
- Property used on table is OccEnabled. Defaulted to Yes.
- Pessimistic concurrency control locks the records immediately after they are fetched from the database for update.
- To set concurrency model for a select statement, just replace forupdate with the optimistic or pessimistic as shown below.
For Example:- Select optimistic CustTable
Select pessimistic CustTable.
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.
Example 1: Longest exclusive locking / blocking time but fasted execution
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;
;
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;
}
}
}
{
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;
;
while select optimisticlock custTable where custTable.creditMax == 0
{
if (custTable.balanceMST() < 10000)
{
ttsbegin;
custTable.creditMax = 50000;
custTable.update();
ttscommit;
}
}
}
{
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.
For more information see sys.dm_os_wait_stats.
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.
Creating & Using the find() methods in Dynamic AX 2012
Hi,
1. Create a table,
2. Use the 2 EDTs and make it one as index as unique by property AllowDuplicates : NO
3. Create a find method in the table using below code and patten
static TableName find(UsedEDT _UsedEDT ,
boolean _forUpdate = false,
ConcurrencyModel _concurrencyModel = ConcurrencyModel::Auto)
{
TableName tableName ;
if (_UsedEDT)
{
if (_forUpdate)
{
tableName .selectForUpdate (_forUpdate);
if (_concurrencyModel != ConcurrencyModel::Auto)
tableName .concurrencyModel(_concurrencyModel);
}
tableName .selectLocked (_forUpdate);
select firstonly tableName
index hint UsedEDTIndex
where tableName.UsedEDT == _UsedEDT ;
}
return tableName ;
}
/************************Using the find method **********************/
Using the display method with find() we can get that value by using below codes in the table.
display Name name()
{
return TableName::find(this.UsedEDT).name();
}
1. Create a table,
2. Use the 2 EDTs and make it one as index as unique by property AllowDuplicates : NO
3. Create a find method in the table using below code and patten
static TableName find(UsedEDT _UsedEDT ,
boolean _forUpdate = false,
ConcurrencyModel _concurrencyModel = ConcurrencyModel::Auto)
{
TableName tableName ;
if (_UsedEDT)
{
if (_forUpdate)
{
tableName .selectForUpdate (_forUpdate);
if (_concurrencyModel != ConcurrencyModel::Auto)
tableName .concurrencyModel(_concurrencyModel);
}
tableName .selectLocked (_forUpdate);
select firstonly tableName
index hint UsedEDTIndex
where tableName.UsedEDT == _UsedEDT ;
}
return tableName ;
}
/************************Using the find method **********************/
Using the display method with find() we can get that value by using below codes in the table.
display Name name()
{
return TableName::find(this.UsedEDT).name();
}
icon.
to generate an Instance Retention Graph.
