Agenda

1. Introduction
2. Lock
3. Lock on insert
4. Example
5. Lock on update, delete
6. Example
7. Deadlock
8. Example deadlock on insert
9. Resolve deadlocks
10. Summary

Introduction

In this article you can find information about what Lock, Deadlock, Transaction, Optimistic concurrency mode, and Pessimistic concurrency mode are. Let’s begin.

As you may know the insert, update, and delete table operations are executed in scope of a transaction. Several records can be inserted, updated, or deleted in scope of one transaction.

Transaction mechanism allows rolling back all changes if transaction is aborted.

In Dynamics AX a developer decides when a transaction is started and when a transaction is committed with the help of the ttsbegin and ttscommit keywords.

Lock

To understand the deadlock, it is required to understand the lock. Let’s recall what lock is and when it occurs.

When a record is inserted, updated, or deleted, this record is locked until the transaction is committed.

What does lock mean?

When a record is locked, it means that the record is in a “draft” state. That is because at that moment it is unclear if the changes will be aborted or committed in the near future.

When User or process selects the locked record in the optimistic concurrency mode, the locked record will be returned with the original (before modifications) values.

When User or process selects the locked record in the pessimistic concurrency mode, the select statement will wait until the transaction is committed or aborted. In other words, the select statement is stuck.

In the Dynamics AX, the concurrency mode can be applied per table buffer or table:

• To specify mode for the table buffer, use the optimisticLock, pessimisticLock keyword in the select statement instead of the forupdate
• To specify mode for the table, use the OccEnabled table properties.

Lock on insert

Let’s assume the following:

• We have the CustGroup table with the CustGroup unique field.
• Some process inserts the “MyBestCustomer” customer group to the CustGroup table but has not committed the transaction yet.
• Another process:
  • Runs the select statement: select * from CustGroup where CustGroup. CustGroup == “MyBestCustomer”
  • If the record does not exist, the process inserts it: CustGroup. CustGroup = “MyBestCustomer”; CustGroup.insert();

I have several questions here.

• Should the select statement return the locked (draft) record or not?
• If the select statement doesn’t return the locked (draft) record, what happens with the second insert? Does it insert an identical draft record to the table or is it stuck?

Previously it was mentioned that the Dynamics AX has two modes to work with locked (draft) records: optimistic and pessimistic concurrency modes.

In the optimistic concurrency mode, the locked record is returned with the original (before modifications) field values.

In our case, the select statement returns nothing because the draft record is inserted and doesn’t have the original values.

In the pessimistic concurrency mode, the select statement waits until a transaction is committed or aborted. In other words, the select statement is hung. The select statement unhangs and runs when the first process commits the transaction.

In regards to the second insert, in the optimistic concurrency mode the select statement returns nothing so the insert statement is executed next (note that the first process has not committed the transaction). On this step, the second process is stuck. It is because the SQL cannot insert the same draft record.

The second insert statement unhangs and runs when the first process commits the transaction.

But if the first process commits the transaction, the second insert statement returns the “Duplication key” exception. If the first process aborts the transaction, the second insert statement inserts the record successfully.

Example

You can easily check this by running this job in two Dynamics AX clients.

static void Test_LockInsertJob(Args _args)
{
CustGroup custGroup;

ttsCommit;
}

Set the breakpoint to the ttsbegin; line.

In the first client, run the job in the debug mode and stop on the ttscommit; line. As a result, you insert the record and lock it but the transaction is not committed yet.

In the second client, run the job and make sure that it is stuck on the insert method. It unsticks with the “Duplicate key” error when you commit the transaction from the first client.

It is because the CustGroup table has the optimistic concurrency mode enabled (see the OccEnabled parameter on the CustGroup table).

Change the mode for the custGroup buffer to pessimistic by adding the pessimisticLock keyword to the select statement: select pessimisticLock * from custGroup.

Repeat the same operations and make sure that the select statement from the second client sticks until the first transaction is committed. But when the first transaction is committed, the select statement returns the record, no error is generated, and the code proceeds further.

Of course, it is possible that the select statement from the second client is executed before the record is locked by the first transaction. In this case the insert method from the second client also generates the “duplication key” and “update conflict” exceptions. But, the number of such cases is much lower.

In any case (the optimistic or pessimistic mode) you should embrace the transaction with the try-catch block and retry the transaction if the “duplication key” and “update conflict” exceptions are thrown.

When you develop a multithread batch architecture, take into account the pessimistic lock because it allows processing with the less number of the “duplication key” and “update conflict” exceptions.

From my experience when we use the pessimistic lock in the multithread batch architecture, the performance is increased up to 30%.

Lock on update, delete

There are no differences between the insert, update, or delete locks.

The updated and deleted records are locked until the transaction is committed.

In the optimistic concurrency mode, the locked record is read with the original values. But the insert and delete methods are stuck until the locked record is committed or aborted.

In the pessimistic concurrency mode, the locked record cannot be read and the select statement is stuck.

Example

Use the same example with small modifications:

static void Test_LockUpdateJob(Args _args)
{
CustGroup custGroup;

ttsCommit;
}

I assume that you already have the record for the Kyiv_Cust customer group in the CustGroup table.

Set the breakpoint to the ttsbegin; line.

In the first Dynamics AX client, run the job in the debug mode. Make sure that the Kyiv_Cust customer group record is selected, the Name field has an empty value. Then the Name field value is updated to “Customers from Kyiv”.

Stop debug on the ttscommit; line. As a result, you update the record and lock it but the transaction is not committed yet.

In the second client, run the job in the debug mode also. Make sure that the select statement is executed successfully, the Kyiv_Cust customer group is selected, the Name field has an empty value (the original value). Then, the Name field value is updated with the “Customers from Kyiv” string.

The job is stuck on the update statement.

It is because the CustGroup table has the optimistic concurrency mode enabled (see the OccEnabled parameter on the CustGroup table).

Change the mode for the custGroup buffer to pessimistic by adding the pessimisticLock keyword to the select statement: select pessimisticLock * from custGroup.

Repeat the same operations and make sure that the select statement from the second client sticks until the first transaction is committed. But when the first transaction is committed, the select statement returns the record, no error is generated, and code proceeds further.

Deadlock

Now that we understand what lock is, so it is time to understand what deadlock is.

It is possible to insert, update, or delete several records in scope of one transaction. In this case all these records are locked until the transaction is committed.

Deadlock occurs when two transactions try to make changes on the same records.

For example, the first transaction locks the AAA record and tries to lock the BBB record, while the second transaction locks the BBB record and tries to lock the AAA record.

In this case, the first transaction is stuck until the second transaction is committed while the second transaction is stuck until the first transaction is committed.

Deadlocks are automatically resolved by the SQL Server. As a result, deadlock exception is thrown for one of transactions (the one that led to deadlock).

Example deadlock on insert

Run this job in the first Dynamics AX client in the debug mode.


static void Test_DeadlockInsert(Args _args)
{
CustGroup custGroup;

ttsCommit;
}

I assume that you don’t have the Lviv_Cust, Odesa_Cust customer groups in the CustGroup table.

Set the breakpoint to the ttsbegin; line.

Stop debug after the first insert method. As a result, you insert the “Lviv_Cust” record and lock it, but the transaction is not committed yet.

In the second Dynamics AX client, run the following job in the debug mode:


static void Test_DeadlockInsertOpposite(Args _args)
{
CustGroup custGroup;

ttsCommit;
}

Note that the order of the customer group is opposite: first, the Odesa_Cust group, then the Lviv_Cust group.

Set the breakpoint to the ttsbegin; line.

Stop debug after the first insert method. As a result, you insert the “Odesa_Cust” record and lock it, but the transaction is not committed yet.

In the next step, the Lviv_Cust record is selecting. Since the CustGroup table has the optimistic concurrency mode, the select statement finds the record with the original value (this record was inserted and locked, so the record doesn’t have the original value, so the empty record is returned). The select statement is not stuck.

Then the Lviv_Cust record is inserted. Since the Lviv_Cust record is locked in the first transaction, the insert method is stuck.

Return to the first debug window and go step-by-step debugging. The select statement is not stuck and it returns nothing (The “Odesa_Cust” record is inserted and locked, this record doesn’t have the original value so the empty record is returned).

Then the Odesa_Cust record is inserted. This record cannot be inserted because the same record is already inserted and locked in the second transaction. So, the insert method is stuck.

The SQL server identifies that two transactions are stuck because of deadlock. As a result deadlock exception is thrown for the first transaction, and changes are aborted (reverted). The second transaction is committed successfully.

We make sure that deadlock can occur only when transactions have more than one inserted, updated, or deleted record.

For example, if you run the following code in batch in multithread, deadlocks are possible:

ttsbegin;

ttscommit;

But if you run the following code in batch in several threads, deadlocks are impossible:

while select table1
{
select forupdate table2 where …
table2.Field = …;

ttsbegin;
table2.update();
ttscommit;
}

Because in the first case, it is possible to have more than one modified record in one transaction.

Resolve deadlocks

To resolve deadlocks:

• Redesign the code to have one modified record per transaction.
• Or if it is impossible, then modified records in transactions must be sorted in one way.

Summary

Lock occurs when record is inserted, updated, or deleted.

Deadlocks can be resolved by applying sort order for modified records.

When you develop a multithread batch architecture, take into account the pessimistic lock because it allows processing with less number of “duplication key” and “update conflict” exceptions.

From my experience, when we use the pessimistic lock in the multithread batch architecture, the performance is increased up to 30%.