How to Implement Robust Auto-Numbering Using Transactions in Microsoft Dynamics CRM

(https://www.greenbeacon.com/insights/how-to-implement-robust-auto-numbering-using-transactions-in-microsoft-dynamics-crm/)

Author’s Note – This was updated in November 2015 to include asynchronous numbering (by request), additional deadlock prevention commentary, and testing against CRM 2015

The primary failing of every auto-number solution for Microsoft Dynamics CRM that I have come across is the ability to ‘guarantee’ a unique number under heavy load across multiple servers, particularly in a CRM Online environment. Depending on the solution you look at, you will find various attempts to solve this problem.  However, all appear to have limitations, and in some cases, the solutions even include a document explaining how to ‘correct’ duplicate values.

Implementing Robust Auto-Numbering Using Transactions in Microsoft Dynamics CRM Online or On-Premise

With the ability (starting in CRM 2011) for Dynamics CRM plugins to execute within the database transaction, we are now able to (in a supported fashion) generate unique number sequences regardless of load and across multiple CRM servers by leveraging the transactional locking behavior of SQL Server, purely within the context of Microsoft Dynamics CRM.

The core solution is similar to any basic auto-number implementation you may find out there. But certain modifications are now added to ensure unique numbers (serialization of transactions by leveraging SQL locking behavior) as well as to limit the potential of deadlocks (store sequence name/GUID cross-reference in a web resource – or other approaches)

So the ‘enhanced’ diagram looks like:
(NOTE: The blue outline represents the newly added portions of the process)

• Create a ‘sequence’ entity to store the various number sequences for your different entities. For example, sequence name (Customer, Opportunity, Invoice, etc) and a ‘sequence’ field to store the current sequence value. (You could also implement prefix/suffix/etc, but that is outside the topic covered here). If you want to share a sequence between multiple entities, just have the plugin use the same sequence name in the Number Request that is created.
•  Create a ‘Number Request’ entity to store the requests for numbering that are being made. You will want to introduce some process to clean these records up over time. (plugin/workflow/bulk delete/etc)
• Register a plugin (CRM Plugin 1) on pre-create of all entities you want to be auto-numbered. This plugin can be registered synchronously or asynchronously, so the user does not have to wait for the numbering to complete.
• In CRM Plugin 1, whenever a new entity is created and triggers the logic, create a new Number Request entity record, containing all the needed information about which record needs to be numbered (entity type, record id, sequence name, field to populate, etc)
• Register a plugin (CRM Plugin 2) on pre-create of the ‘Number Request’ entity. This plugin must be synchronous to preserve desired transaction behavior. Keep in mind that if CRM Plugin 1 was set to run asynchronously, it’s still ok for CRM Plugin 2 to be synchronous, and the user still will not have to wait for the numbering to complete.
• Now, in CRM Plugin 2, implement our ‘real’ numbering logic that leverages DB transactions –  check the sequence entity for the current entity’s latest value, use it as the id for the newly created entity record, then increment the sequence value for the current entity type in the sequence entity.

The ‘catch’ with Microsoft Dynamics CRM has always been that there was not a robust way to guarantee that two entities won’t get the same sequence value, because there is no supported locking mechanism to ‘guarantee’ in all environment types that only one plugin execution will access the sequence value at a time. Prior to CRM 2011, the best solution I have come across was to use a database call to an external DB in the plugin that atomically reads and updates the sequence value for use in the plugin in a stored procedure transaction, but that requires the creation of a custom database, etc, which I would prefer to avoid by using an internal CRM construct, particularly for CRM Online.

The difference is, now that we have the ability to register our plugin in the CRM database transaction, we can do the following in CRM Plugin 2:

1. In our sequence entity, create a new ‘dummy’ or ‘lock’ field. This field will be used by the plugin to ‘lock’ the underlying database record which will enforce sequential access to the associated sequence tables that implement the Sequence entity.
2. Inside the plugin [CRM Plugin 2 from the diagrams] (registered as a pre-create operation):
   • First retrieve the GUID of the sequence record we want to use. This could be done with a web resource storing the values for each of the sequence names and their corresponding GUID (this or another approach needs to be used to prevent deadlocks caused by getting the GUID with an SDK retrieve before the record is locked), or could be obtained by an SDK ‘retrieve’ to search for the sequence record for the current entity type. [strikethrough because this is shown to introduce deadlocks under high load]
   • Now, UPDATE the sequence record’s ‘dummy’/’lock’ value. At this point NO ONE CAN MODIFY THAT RECORD except the current plugin instance – the database will have an update lock owned by the CRM transaction. Since the FIRST operation we perform on the sequence record is an update, we have serialized the process.
   • Since we know that we have ‘locked’ our sequence record, use the CRM SDK to
     • Retrieve the current sequence value via a retrieve using the record’s GUID. We cannot retrieve the current sequence value until AFTER locking the record.
     • Assign the retrieved id to the plugin entity in the context.
     • Increment the id in the sequence entity record.

This will use the database’s built-in locking behavior to ensure that only one transaction will be reading/updating any given sequence at a time, thereby ensuring the uniqueness of your numbering scheme. Furthermore, if your CRM transaction is rolled back, the sequence will NOT be advanced, which will ensure you don’t have ‘gaps’ in the sequence due to numbers being assigned to failed entity creates.

Once the CRM database transaction completes (Entity Creation) that entity’s sequence will be available to the next plugin executing.

My ‘proof’ was to insert a Thread.Sleep(5000) just after the record lock in Step 2b above. If the database does NOT block access, two new Accounts saved at the same time will take the same amount of time (approximately) as they are executed in parallel. If the database IS blocking as expected with the transaction, there should be a 5 second gap between the first Account create and the second (due to the fact that the second account has to wait until the first save is COMPLETE before it can continue. We did observe a 5 second delay between saves, and thus that the expected database locking does occur, and our numbering solution is correct.