History of inventory locking in DAX

It is not unusual to hear complaints about DAX performance from users of outdated versions (pre 4.0) of Dynamics AX. Something like “We have 4- CPU 2-core Xeon server with 16Gb of RAM, running DB Server with 5GB database, but creation of a single order line can take up to 5 minutes”. If we would run SQL Enterprise Manager or SQL Management Studio, we see the long queue of blocked locking requests to the InventSum table . Roughly speaking, this table contains information on an inventory on-hand quantity (as well as a reserved quantity, registered quantity, arrived quantity,etc) for given combination of an item number and an inventory dimension. This table is updated (again – in outdated versions) in the following way: On any update (or insert) to the inventory transactions table (inventTrans) the system finds and updates (or inserts) the record in inventSum table and then updates a corresponding quantity field in it. Naturally, after the update of the record, it remains blocked until end of the transaction, thus blocking both read and write access to this record from other database sessions.(Lets take dirty read mode out of consideration). If we does not use serials or batch numbers, we may accept as a first approximation that if we have changed inventory transaction, then until end of the transaction other users CANNOT make any inventory operations with the same item and the same warehouse id. There is quite simple reason for this: until end of transaction, available on-hand quantity is kind of stochastic value. Let’s imagine that on-hand data locking does not takes place. Say, We have 40 pieces of some item in inventory. Storekeeper now is posting a purchase packing slip for 800 pieces of the same item; Also, two sales managers simultaneously  are reserving 20 and 40 pieces of the item; Another storekeeper is posting sales packing slip for 15 pieces. A question arises – how many pieces of the item is available and may we reserve 60 pieces of the item  for some new sales order ? (BTW – It is assumed that both physical negative inventory and financial negative inventory is turned on for all stockable items).

Thus, invent on-hand locking problem does not arises from some brain-damaged design of negative inventory check. This problem is a result of the very idea of the transactional data update. If during transaction, a level of some critical resource is changed, then access to information on the level of the resource must be blocked for other sessions, just to prevent, roughly speaking ‘plane overbooking’ scenario, when the same resource has been sold twice. Even If we were to have some ultra-fast DBMS, which would allow us to calculate inventory level by summing-up inventory transactions themselves (without inventSum table), we would face the same problem anyway. We would not know – should we add quantities from unposted transactions to the inventory level value.

To understand the scope of the problem, let’s consider the following example:

Say storekeeper have commenced the posting of the purchase packing slip with 120 lines in a body. Receipt transactions for the first 40 lines has been successfully posted, thus blocking (both read and write) invent on-hand data for the items from the posted lines. Upon proceeding to 41th line, packing slip posting process has been blocked and now is waiting for availability of invent on-hand data for the item from 41st line, which is blocked by the salesperson number #1, who currently is posting 8 lines sales invoice. This salesperson, in turn, has blocked 6 invent on-hand records and now is waiting for the 7th record which is blocked by salesperson #2. This salesperson, in turn, is waiting for the availability of the on-hand record for some other item, which, in turn, is blocked by storekeeper #2 who is posting sales packing slip. Second storekeeper, in turn, is waiting for availability of the on-hand record which is blocked by salesperson #3. Basically, a normal flow of business is stopped for all four participants by the current operation of salesperson #3. After this operation will have been completed, the whole waiting chain will begin to advance – slowly but surely. And after a while, the posting process of the first storekeeper may advance to the 42th line of packing slip, but only if everybody will be lucky and It won’t turn out that the 8th line of first sales person would be blocked by salesperson #4. And we should not forget, that all the participants has blocked some on-hand records and  chances are high that out of our picture there are also some other blocked users. As a result – time required for given logistical operation can vary from several tenths of second to some 10 minutes. This time does not depends from AOS and DB servers performance. Simply, depending from some sporadic conditions, a resource queue may consist from one session or it may consist from ten sessions – it is a matter of luck. By the way, probably some of the readers had their Axapta’s DB connection closed unexpectedly  because of ‘Deadlock’. From time to time, It might happen that loops occur in a blocking graph, e.g salesperson #4, who is being waited by storekeeper #2, is actually blocked by the first storekeeper, which is both a beginning and end of locking chain. In this case, the system just closes the looping session aborting it’s active transaction. Most interesting part of the case is that if we  have a look at DB and AOS servers’ utilization, we would see that it is not high at all. This exactly the point which usually cause a lot of perplexity from system administrators. How does it happens – the servers are underutilized, but overall system performance is below all reasonable minimums ? A purchase of more potent DB server would not relieve the problem, because overall system performance depends from all the chains of the DB Server-Network-AOS Server-Network-User’s workstation chain. While the upgrade of the servers only sounds realistic, an upgrade of all the members of the chain definitely would be economically impossible.

To overcome this problem, Dynamics AX 3.0 introduced a mechanism called Inventory MultiTransaction System – IMTS (Strictly speaking it was included in version 2.5Sp6, but as far as I remember, it has been release after release of version 3.0).

In a first approximation, this mechanism works in the following way: On any update to inventory transactions, the system opens short-living DB transaction in a SEPARATE database connection and use this transaction to update the inventory on-hand record (inventSum table). Since this separate transaction is a short-living, chances for a lock congestion are low. Also, on any update to inventory transaction table, again in the separate database conection, the system writes to InventSumLogTTS table the log of inventory transaction update. Why this log is necessary ? Let’s think – what would the system do if the main transaction (which updates inventTrans, salesLine,inventJournalTrans and so on) is rolled back ? Naturally – the related changes to invent on-hand data must be rolled back also. But since these changes was made in the separate transaction, they would not be rolled back automatically. Because of this, Axapta 3.0 implemented special mechanism, which on rollback of the main transaction, runs through related records of inventSumLogTTS table and MANUALLY rollbacks the changes to the inventSum table (It subtract quantities from the log table from related records of the inventSum table, to be more precise). That is – because Axapta bypasses regular transaction mechanism, it MANUALLY, from application level code (InventSumTTSControl class) rollbacks the changes made outside of regular transaction scope. Thus, the inventSumLogTTS table serves as a DIY transaction log, which is used to log the changes to inventSum made outside of the main transaction.

Although technical locking problem, formally, has been resolved by this approach, the problem of UNCERTANTY of inventory on-hand quantity, still persists. E.G. we have a 5 pieces of the item on-hand, 8 pieces of the item is received in uncommitted transaction. Can we allow reservation of 10 pieces ?

In IMTS settings it was possible to specify two modes of the invent on-hand quantity check during inventory issue: Optimistic and Pessimistic one. In optimistic mode system treated all unfinished transactions as bounded to finish. In this mode, the system would have allowed to reserve 10 pieces from our case. It led to another problem, though. If, in our case, uncommitted receipt transaction would have been rolled back, we would have had 10 pieces reserved, but only 5 pieces on stock… Also there was the pessimistic mode, which treated all uncommitted transactions as bound to be rolled back. This mode would have resolved the case above. But also, it would have raised another problem which happened when several users were posting sales for the same item. Since unfinished inventory issue transactions from an another session was treated as nonexistent, the system allowed to issue the same inventory many times from multiple sessions running in parallel.

Besides these methodological problems, the system also had some unpleasant technical misfeatures. First of all – It seems that designers of the IMTS functionality had not been completely sure about stability of the manual rollback mechanism described above. It seems that sometimes, the system just was not been able to activate this mechanism and inventSum was left in erroneous state. To fix this, the designers invented a peculiar mechanism which ran periodically (the period between runs could be specified somewhere in IMTS parameters), through all records in inventSumLogTTS which had not been marked as committed (isCommitted field). Then, the mechanism tried to determine – was the corresponding transaction active or not. If transaction was not active, the system rolled back this change from inventSum table. Moreover, this not very stable and more heuristic algorithm was run from time to time from some completely random user session, upon commitment of some random inventory transaction. As a result, from time to time, some completely innocent situation, like posting of one-line inventory journal might took several minutes to finish, because system decided to check the inventSumLogTTS validity. There was also the second problem with this implementation, the pessimistic/optimistic mode setting was working for inventory on-hand quantity check, but most of reports and forms was still working in optimistic mode, simply fetching the quantity data from the inventSum table. As a result, It led to situations when an item was shown as available in inventory on-hand form, but attempt to sell this item would have been failed.

On my own projects (where I was PM or architect) I never have been using IMTS. When I was asking my friends about their opinion on IMTS, opinions vary a lot. Someone told that everything is working like a charm,, Others told something like “There are some issues. But it is better to recalculate on-hand data than tolerate locks”. Somebody told something like: “We tried it. Then turned off. Then disabled the form for turning on the feature and commented out the code to turn it – just for a case”. Shortly speaking – in version 3.0 effect of IMTS usage was ambiguous. By the way – I witnessed implementations in trade and logistics companies for 300-400 users, which were not using IMTS. Of course, mutual locking situations were upsetting the users, but it did not became a problem for the business as a whole, because these locking cases did not slow up the business processes significantly.

In version 4.0, developers of Dynamics AX made another attempt to resolve this problem. (And I consider this attempt as unconditionally successful). From previous experience it is clear that:

• We must not block inventory on-hand data for a long period – otherwise lock tree would grow.
• Update of on-hand data in separate session is not a solution. An attempt to bypass regular transactional mechanism creates more problem, than it solves.

After looking at the problem with the fresh eye, the developers have made a very simple conclusion: Since we can not abstain from locking inventory on-hand data, we should move on-hand data update to the very end of transaction, so the locks duration would not be too long. That have been done in the following way: The InventSum table is not being updated directly on every update to inventory transactions. The update log is written to inventSumDelta and inventSumDeltaDim tables instead. This log is updated in the main transaction, no extra session or transaction is opened. In the very end of the transaction, exactly before transaction’s commit, the following steps are being made:

1. All inventSum records to be updated are being locked in one atomic operation. It is impossible to lock 12 records out of 20 and then wait for availability of the 13th record. The system waits until availability of all records to be updated and then locks them all in one strike. No tree of locks, therefore.
2. The system runs through all inventSumDelta table and for every log records checks for negative inventory. If after application of log record to inventSum inventory would go negative, the system display an error message and rollback transaction.
3. After the check, the system updates corresponding record in the inventSum table and delete the log entry from the inventSumDelta table.

As a result:

1. There are no problems from the second connection and bypass of transactional mechanism. All updates are made through the main connection.
2. The problems related to tree of locks have gone. The inventory on-hand records are being locked in one atomic operation. It is not possible to lock some records and wait for another record to become available.
3. The level of mutual locking is brought to a possible minimum. All locks are set only at very end of a transaction. All operations performed after the locking are light, short and not resource consuming.

By the way, to accelerate the inventSum update operations, developers used direct SQL. (That is – queries are written in native SQL dialect and are sent to the database directly, thus bypassing Dynamics AX’s database layer). In case of MS SQL the potent, two-screen-sized query is sent, which uses lots of T-SQL extensions, absent from X++. In Oracle case, update of inventSum is performed by the axUpdateInventOnHand stored procedure, which is created on the first sync of database. Strictly speaking, usage of direct SQL is a violation of best practice guide, but I think that in this case, increase in performance is a good excuse for the violation.

If you want to have a more detailed look at new inventory on-hand update mechanism internals – have look at inventUpdateOnHand and inventUpdateOnHandGlobal classes.

Is is also worth to mention, that inventory check in the end of transaction does not cancelled inventory check during initial inventory transaction update. So if we try to sell an out-of-stock item, we would not need to wait until  end of transaction to find this.

The only disadvantage of the new blocking mechanism is a slight increase in the number of transactions being rolled-back. This might happen when in time of transaction’s update the item was available, but has been seized by some other user before the transaction has been committed. Actually – It does not creates significant extra load to database server, because in real life, distribution of the hard-to-get resources is usually handled in organizational way: Probable reservation conflicts are prevented by preliminary distribution of merchandize to arrive, by a negotiation.

In fact – The invent on-hand locking mechanism implemented in DAX 4.0 is a variation of the optimistic locking mode. In a classical approach to optimistic locking a database server follows the following rules:

• Data are not locked during reading
• On update of data, the database  checks whatever the data has been updated by another session after being read by current session. If this is the case, an error is raised and transaction is rolled back.

If exactly this approach were implemented for on-hand records update, it would lead to generation of lot of errors. That’s why in Axapta this check is customized to check not the fact of record being updated, but only check that remaining quantity is sufficient to perform the inventory operation.

It pays to mention that the optimistic locking mechanism has nothing in common with the optimistic way of invent on-hand evaluation from the old IMTS implementation. They are different kinds of optimism. 🙂 In the first case we optimistically estimate the probability of OUR transaction to be commited; That is – if we had enough item in stock during initial on-hand check, then we probably would have enough of it during the final check at transaction’s end. In the second case we optimistically estimate probability of OTHER’s transaction to finish; That is – if another session has posted and inventory receipt, then this session will probably commit it’s changes and item from this receipt will be available.

Dynamics AX 4.0 retained the InventSumLogTTS table, but now it is updated from the main transaction and is used only to keep track of changed inventory transactions for incremental coverage planning. (To allow the MRP procedure to find out – which inventory transactions has been changed and which corresponding net requirement should be coverage planned again).

It should be mentioned, that the developers did not manage to hide existence of InventSumDelta* tables from application level code completely. Some changes for reservation logic was required by introduction of the new mechanism. Now, substitution of inventory dimension values during a reservation and substitution of a pallet number in WMS module fetch the availability data from both the inventSum table and the inventSumDelta table. Let’s imagine a case, when we are trying to reserve some item, not specifying a batch number, but relying on the system’s feature to substitute available batch numbers into an inventory transaction. Let’s also assume that we have the same item in TWO lines of sales order. In the old version, the system just was running through inventSum records, picking available batch numbers. In the new version, inventSum is updated just on the transaction’s end. If the system were to pick available batch numbers in the old way, we would end up with the same batch numbers reserved for the both sales order lines. That’s why the developers had to fetch availability data from both inventSum and inventSumDela tables. This does not establish any performance r locking problems, but somehow breaks the conceptual integrity of the elegant idea.

In a conclusion, it worth to say that this mechanism cannot be backported to the version 3.0, because it relies on the application.ttsNotifyPrecommit() (called excatly BEFORE the transaction’s commit), which has been implemented in version 4.0, but was absent from earlier versions. In earlier versions, only methods application.ttsNotifyCommit(), application.ttsNotifyAbort() existed, which is called on AFTER transaction’s commit.