RecVersion - Dynamics Axapta and OCC (Optimistic Concurreny Control)

Definition

Optimistic Concurrency Control (OCC) helps increase database performance. Pessimistic Concurrency Control locks records as soon as they are fetched from the database for an update. However, Optimistic Concurrency only locks records from the time when the actual update is performed.

Pessimistic concurrency was the only option available in Microsoft Axapta 3.0 (now a part of Microsoft Dynamics). You can now choose which concurrency model to use—optimistic or pessimistic.

RecVersion is a 32-bit signed INTEGER. But it will never get a negative value assingned through the AOS.

Advantages

• Fewer resources are used to hold the locks during the update process.
• Records are locked for a shorter length of time.
• Records remain available for other processes to update if they have been selected from the database but haven't yet been updated.

Disadvantages

The disadvantage of using OCC is that the update can fail if another process updates the same record. If the update fails, it must be retried. This can lead to a reduction in database performance.

How to catch Update Conflicts

Update Conflicts can be handled by catching the UpdateConflict and UpdateConflictNotRecoveredevents.

This pattern is used across AX API's for retrying the update conflicts and if it exceeds the maximum retry count then UpdateConflictNotRecovered exception is thrown.

e.g. AOT\Classes\PurchTableType\delete

Update Conflicts and recversion field
Update conflicts are detected by the kernel. It checks the value of the recVersion system field on the table at the time the record is selected for update. This value is matched to the value of the field when the record is subsequently updated. The default value of recVersion is 1. This is changed to a random value when a record is updated.

Let’s assume we have a table with data like this

Column A	Column B
A	1
A	2
B	3
B	4
C	5
D	6

We create a non unlike index on Column A.

Now we have 2 independent AX clients with both want to update a record.

User 1 : select forupdate table where ColumnA =='A' && ColumnB == '1';
User 2 : select forupdate table where ColumnA =='A' && ColumnB == '2';

When SQL processes the query it will use the non unique index on column A. Next it will do a page scan on all find records to search for ColumnB. Because we are doing an update all records in that page are locked.
so User1 locks also the record with ColumnB = '2'

User 2 has to wait till user 1 is ready. This called page locking.

In Ax 4.0 a new option Optimistic concurrency is introduced. This option allows that user 2 can continue.

OCC in ax 4.0 is done by adding a new collumn to every table. The name of this column is recversion.
So in above case user 2 wants to update the record in the same page. When the update is executed, the system checks if the recversion of the record in the database is equal to the recversion on the moment the records was selected. If that was the case. The update succeed.