This was probably a subject of discussion in various other blogs and forums. But I’ve found a very interesting approach to this matter by Martin Dráb long time ago.
Most developers already know the basics about transaction usage and how the mechanics behave while locking a record for an update instruction. But once in a while we can face different situations.
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.
This can actually happen in a fairly simple composition:
while select forUpdate myTable
{
ttsBegin;
myTable.MyField = 'something';
myTable.update();
ttsCommit;
}
We can come across such a code really often and it works very well under most 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 inside a transaction – that’s fulfilled and everything works.
But if the table uses pessimistic locking, the update fails with the run-time error we’ve shown.
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 and it’s the default method of record locking. However, It’s the default, but not the only one – pessimistic locking can be activated in following ways:
- For a single query (select pessimisticLock myTable or myTable.concurrencyModel();
- For a single table (property OccEnable = No);
- 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.
In my case, I 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 the number of database queries, the second one requires the change of transaction logic and it potentially locks a large amount of records.
In my case, myTable.reread() was enough to solve the problem. However, it’s reasonable to always analyze the big picture and decide if OCCEnabled feature should be used or not, or what alternative might be used for the greatest result.
We should never ignore pessimistic locking. I hope that this example was useful in order to help demonstrate and get you the insight about all scenarios we can stumble.
See you next time! 🙂
AXFORCE 