An updated version of this blog (November 2015), can be found
The primary failing of every auto-number solution for
With the ability in
The core solution is similar for any auto-number implementation.
* Create a ‘sequence’ entity to store the various number sequences for your different entities. For example, entity name 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)
* Register a plugin on pre-create of all entities you want to be auto-numbered.
* In the plugin, whenever a new entity is created, 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 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. The best solution I have come across before now is 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.
The difference is, now that we have the ability to register our plugin in the CRM 2011 database transaction, we can do the following:
1. In our sequence entity, create a new ‘dummy’ 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 (registered as a pre-create operation):
a. 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 entity’s sequence (this would probably be the best for preventing deadlocks, but possibly overkill), or could be obtained by an SDK ‘retrieve’ to search for the sequence record for the current entity type.
b. Now, generate a new GUID, and UPDATE the sequence record’s ‘dummy’ value to be the new GUID. 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.
c. Since we know that we have ‘locked’ our sequence record, use the CRM SDK to
i. 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.
ii. Assign the retrieved id to the plugin entity in the context.
iii. 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.