Number Sequence auto-cleanup causing blocking


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.

clip_image001

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.

Comments (3)

  1. There is another benefit for Non Continous Number Sequence: you can use the Pre Allocation feature as well. This is really helping when high volume of Number Sequences are consumed.

    Regards,

    Bertrand

  2. It is true there are a number of options to improve the auto-cleanup function - that are being explored in future versions. This post is describing a potential problem in the current versions, and how to work around it.

  3. Logger says:

    Do you plan to fix auto-cleanup function ? For example, bound the number of NumberSequenceList record processed in one auto-cleanup try. It reduce time of lock.

Skip to main content