AX: Support for pessimistic locking

Let’s skip an introduction and jump right to a code:

while select forUpdate myTable
{
    ttsBegin;
    myTable.MyField = 'something';
    myTable.update();
    ttsCommit;
}

I meet such a code really often and it works very well under certain circumstances. Specifically, it requires optimistic record locking to be active. In such cases, Dynamics AX just check whether a record is selected for update and that the update() method is called in a transaction – that’s fulfilled and everything works.

But if the table uses pessimistic locking, the update fails with the following run-time error:

Cannot edit a record in MyTable.
Cannot call NEXT, update(), or delete() on buffer where data is selected or inserted in another transaction scope.
Calls to NEXT, update(), or delete() must be performed on the buffer on the selection transaction level, or within the same transaction (TTS) scope.

That happens because pessimistic locking needs to place a lock on the selected record and it must happen in the same transaction, which is obviously not complied here.

Optimistic locking is available since Dynamics AX version 4 (therefore the example mentioned above won’t work in Axapta 3 or older) and it’s the default method of record locking. However, I said the default, not the only one – pessimistic locking can be activated in following ways:

1. For a single query (select pessimisticLock myTable or myTable.concurrencyModel())
2. For a single table (property OccEnable = No)
3. Globally for the whole AX (Administration > Setup > System > Concurrency model configuration (AX4, AX2009), or System administration > Setup > Database > Select concurrency mode (AX2012))

In that moment the code mentioned above fails. In other words, the code works only in a specific configuration of Dynamics AX and ends with a run-time error otherwise.

The question is – is it necessary to write database queries to work also with another configuration of locking?

Statement no. 1: Configuration of locking is the standard part of Dynamics AX and we shouldn’t arbitrarily limit the existing functionality. Pessimistic locks can help to resolve excessive number of write conflicts in some parts of application etc.

Statement no. 2: A change of locking method in an existing application is utterly exceptional and it’s not worth to support it, because it can have unnecessary performance implications.

Annotation to statement number 2 – in our case, we could either select every record for an update individually:

while select forUpdate myTable
{
    ttsBegin;
    myTable.reread(); //reread record in the same buffer
    myTable.MyField = 'something';
    myTable.update();
    ttsCommit;
}

or to change the transaction logic to consider the whole cycle as one atomic operation:

ttsBegin;
while select forUpdate myTable
{
    myTable.MyField = 'something';
    myTable.update();
}
ttsCommit;

The first approach significantly increases number of database queries, the second one requires the change of transaction logic and it potentially locks a large amount of records.

My opinion can be summarized as follows:

1. It’s reasonable to expect that nobody will change the way of locking across the whole system and therefore it’s not necessary to support pessimistic locking for all tables (if you know a counterexample, I would be glad to broaden my horizons).
2. For every single table, decide whether it should support pessimistic locking and document if so. There won’t be many such tables and a lot of implementation really doesn’t need any. On the other hand, sometimes it’s a critic feature.
3. If a table should support pessimistic locking, create and test it with OCCEnabled se to No at first.
4. Expect that any solution doesn’t support pessimistic locking unless the opposite is proven.

It’s the fault to completely ignore pessimistic locking – and it happens quite often.