mfp: Number Sequence auto-cleanup causing blocking

[Blog bot]

Источник: http://blogs.msdn.com/b/mfp/archive/...-blocking.aspx
==============
Recently a customer experienced a daily time timeout in their warehouse operations. It occurred around 10.30 every day and lasted for a few minutes. Meanwhile all warehouse operations were blocked. 

It turned out that the culprit was a number sequence configuration. One of the many features in the AX number sequence framework is to automatically clean up unused numbers for continuous number sequences.   This feature is great for number sequences that are infrequently used.  However, for the high volume number sequences this can cause blocking problems.

When generating a new number the auto-cleanup feature will test to see if it is time for clean-up, and if it is it will commence the clean-up right away –  – and the clean-up can take minutes.  Meanwhile SQL will hold locks prevent anyone from accessing the same number sequence.

Here is a setup of a number sequence that daily will run the auto clean-up, and potentially lock the system meanwhile.



And here is a job to detect similar issues in your installation:  

static void FindNumberSequencesCausingLocksDuringCleanup(Args _args)
{
    utcdatetime currentSystemTime = DateTimeUtil::getSystemDateTime();
    NumberSequenceTable ns;
    while select ns
        where ns.Continuous == true &&
              ns.CleanAtAccess == true &&
              ns.LatestCleanDateTime
    {
        if (DateTimeUtil::getDifference(currentSystemTime, ns.LatestCleanDateTime)
            > ns.CleanInterval*3600)       
         {
            info(strFmt("Every %1 hour %2 will lock during cleanup, last time: %3",
                  ns.CleanInterval,
                 ns.NumberSequence,
                 ns.LatestCleanDateTime));
        }
    }
}

Options to consider:

1. Does this number sequence need to be continuous at all?  Non-continuous number sequences are must faster, and do not require clean up!
2. Is the automatic clean-up required to run automatically? It can also be run manually from the Number sequences form for example outside peak hours.

==============
Источник: http://blogs.msdn.com/b/mfp/archive/...-blocking.aspx