Updating a sql recordset
In the new version (DAX2012) approach remained the same from functional point of view, only technical details changed, so this information can be useful also for people using DAX2012.A bit of history As I wrote in my blog before, table began its life (somewhere in late releases Axapta 2.5) only as a tool to track a list of changes which was applied to invent Sum table in independent DB transaction.
Since now we have several millions of transactions, it will take some time.Since all explosion update process is run as a one big fat transaction, all updated records in invent Sum Log TTS will remain locked till end of this transaction and all other users, trying to schedule another production order, which happens to share any of direct or indirect subcomponents with order being re-scheduled, will have to wait till the transaction ends.Also, most probably, part of the records related to scheduled items were inserted by inventory closing in not yet committed transaction(s).I must mention, that in the end of transaction (commit or abbort), logging is re-enabled automatically, so in most cases you just need to disable logging and then do nothing.Practically speaking – to disable logging to invent Sum Log TTS during inventory closing/recalculation, you need insert a call to I made this change for two installations and I had quite positive results: Inventory recalculation/closing time dropped from days to hours (about 15-20 times) and all locking between production operations and inventory closing/recalculation have been gone.Since both installations use Weighted Average inventory model, routine inventory recalculation was causing some 2-3-5 millions of updates to Invent Trans table and, respectively, creation of the same number of records in invent Sum Log TTS table.
The worst part of it is that Invent Sum Log TTS table is locked in pessimistic mode.
But in complex queries, especially in queries with self-join, U-lock is placed in the very beginning of SQL Statement execution (when the page is being read for the first time) and kept until query reached a home-run stage, when all records to be updated are defined and the query started update phase itself.
Since our query in question has a join of 11 tables, even in the very best case scenario the system was taking a couple of minutes to evaluate all necessary info and proceed to update phase.
(I knew the place in the code before I went to a customer’s site). I found out that this statement placed U-lock over seemingly every page of the invent Trans (Or, at least, all pages with given dataareaid).
It was the following query from Invent Cost Non Financial Transfer Handler.update Invent Trans() method: update_recordset invent Trans setting Non Financial Transfer Invent Closing Rec Id = _invent Closing. Date Financial, Value Open = Invent Trans Open:: No where invent Trans. I never had a lot of experience with U-locks before.
Nevertheless Recently, I was invited to a customer who had started to use Inventory Closing functionality about a month ago. Every time when they were running inventory closing, the system locked all (or at least – most of the) invent Trans records, safely preventing users from any regular work with logistics module.