Top picking pattern to avoid long SQL-Blocking chains in MSDyn365FO

In the past we often had the need to build up some que and de-que algorithm within Microsoft Dynamics 365 for Finance and Operation. What we normally use in such scenarios is a simple SQL table to manage the que entries. The problem with this solution is, when many processes try to que and de-que, you will end up with high locking contention. The solution to reduce the locking contention is the so called “top picking pattern” which has been described in the linked site:

One key factor of the “top picking pattern” is that the de-que algorithm always runs on ttslevel 0 because it will set an exclusive lock on the que table to pick the topmost entry from the que and releases the lock directly after the picking. The processing of the picked entry happens afterwards in a separate transaction.

Our best practice for implementing the “top picking pattern” is to add only one (non-unique) index to the que SQL table which has only one boolean field with name Proceed. With this implementation pattern we are able to optimize the write performance as much as possible.

What are your experiences with que and de-que algorithm within MSDyn365FO? 

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s