Microsoft Dynamics AX 2012 locking concurrency

When we are writing a code that is updating or inserting records in the database, we need to be aware of Microsoft Dynamics AX 2012 locking concurrency and how the structure of our code could impact locking.

We are going to cover this scenario with following example X++ Code and SQL Script:

X++ Code Example (job can be used to see the locking behavior)

static void  LockExample(Args _args)
{
    CustTable custTable;
    ttsbegin;
    While select forupdate custTable where custTable.AccountNum == '001320'
    {
        custTable.BankCentralBankPurposeText = 'UpdatedText';
        custTable.update();
        print custTable.BankCentralBankPurposeText;
    }
    ttscommit;

Put a breakpoint on the While statement, on the Update, Print and on ttscommit.

Open SQL Server Management Studio, and run the following script at each breakpoint. Again, this script could be easily modified to include additional information from the SQL DMV’s

select t1.request_session_id,t1.request_type, t1.request_status,t1.resource_type, db_name(es.database_id),es.login_time,es.program_name,es.login_name,es.nt_user_name, es.open_transaction_count from  master.sys.dm_tran_locks t1
with (nolock) join master.sys.dm_exec_sessions es with (nolock) on t1.request_session_id = es.session_id where
es.program_name = ‘Microsoft Dynamics AX’
and es.login_name = ‘CORP\admin’
and es.open_transaction_count > 0

The locks are acquired on the Update statement but they are not released until the ttscommit. Because we specified forUpdate in our Select statement above, concurrency will default to that specified on the table. The table custTable has OCC Enabled set to Yes, so the Select uses Optimistic Concurreny. We can force the use of Pessimistic Concurrency by replacing the forUpdate like:

While select pessimisticLock custTable where custTable.AccountNum == '001320'
    {
        custTable.BankCentralBankPurposeText = 'UpdatedText';
        custTable.update();
        print custTable.BankCentralBankPurposeText;
    }

Try changing the job to run this way and this time put a breakpoint right after the Select. Notice the difference in the locking behavior. Pessimistic Concurrency Control locks records as soon as they are fetched from the database for an update.