An updated version of this blog (November 2015), can be found .
The primary failing of every auto-number solution for that I have come across is the ability to ‘guarantee’ a unique number under heavy load across multiple servers, particularly in a environment. Depending on the solution you look at, you will find various attempts to solve this problem, but all appear to have limitations, in some cases even with a document explaining how to ‘correct’ duplicate values.
With the ability in for 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 , purely within the context of Dynamics CRM 2011.
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.
Happy Numbering!
Great article shared here. It's helping me a lot for Auto-Numbering Using Transactions.
Will auto-numbering using transaction and locing a record till its execution is completed be achieved using custom workflow?
Please help.
Hi Ken,
I tried implementing your suggestion but I found that I am getting SQL timeouts/deadlocks when I perform load testing. In my load tests I am running two processes which each create 50 records which trigger my AutoNumber plugin but after creating about 20-30 records each, they run into an SQL timeout.
Would you be able to offer me some suggestions?
My code looks like this:
var autoNumberLock = new Entity("new_autonumberlock");
autoNumberLock.Id = Guid.Parse("{30805184-6371-e511-9416-001dd8b71f89}"); //hard-coded for the sake of testing
autoNumberLock.Attributes.Add("new_dummylockfield", Guid.NewGuid().ToString());
Service.Update(autoNumberLock);
//AutoNumber code
The hardcoded guid should make sure the first operation is an update, which should prevent that transaction from deadlocking. Is this the first service call you make in the plugin? It's very important that the first step in the overall transaction is the update, this serves to serialize the operations of the plugin. Are there other plugins also being triggered in the pipeline? Any hints from SQL Server on the blocking side? You can contact me directly at firstinitial + lastname @greenbeacon.com
This is a side effect of step 2a in the post, initially I did claim this could be subject to deadlock issues when using an entity retrieve. Essentially, to address deadlocking issues, you have to make sure the FIRST operation you perform on the sequence entity is the ‘update’ of the dummy field. The act of ‘retrieving’ the record first puts read locks on the table which will lead to deadlocking when we try to ‘update’ the same entity as the second step.
What you need to do is provide a mechanism that allows the plugin to obtain the GUID (id) of the sequence record without actually locking the record with a read request. You could take any one of multiple different approaches to achieve this.
1. Provide the GUID in the plugin step configuration (in the secure/unsecure text via XML, or just a string) – this can be a challenge when adjusting GUID’s to different environments (Dev/Test/Prod,etc)
2. OR Create a web resource (XML or similar) that is ‘read’ by the plugin to determine the GUID to update, and in each environment, configure that web resource to have the correct GUID/Sequence mappings.
3. OR Create an ‘index’ entity for the sequences. In the plugin, first ‘Read’ the ‘index’ entity to get the linked GUID of the actual sequence record (instead of actually reading the ‘real’ sequence record). Then use that retrieved GUID as the key to issue a ‘dummy’ update to the actual sequence as the first call against the actual sequence entity. You could use a simple workflow to auto-create the ‘index’ records every time you create/update a sequence. The ‘index’ entity basically just contains the sequence name, and the id of the associated sequence entity for updating.
I have tested and used approach 3 to remove deadlocking.
-----------------------------------------------------------------
"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.
"
-----------------------------------------------------------------
I tried implementing your suggestion but I found that I am getting SQL timeouts/deadlocks when I perform load testing. In my load tests I am running two processes which each create 50 records which trigger my AutoNumber plugin but after creating about 20-30 records each, they run into an SQL timeout.
Would you be able to offer me some suggestions?
My code looks like this:
var autoNumberLock = new Entity("new_autonumberlock");
autoNumberLock.Id = Guid.Parse("{30805184-6371-e511-9416-001dd8b71f89}");
autoNumberLock.Attributes.Add("new_dummylockfield", Guid.NewGuid().ToString());
Service.Update(autoNumberLock);
//AutoNumber code
Tested the below scenario and it failed with DEADLOCK error.
Created a new workflow which just creates a Contact with Last Name
a) Primary Entity - Contact
b) Available to Run - On Demand
Selected 250 Contacts --> Run this workflow
Regards
Srinaath
Thanks for the info! One question about the update lock, does assigning the new GUID value create the lock and then the OrganizationService.update(sequenceEntity) call release it? Or does the update method create the lock and the plugin completion releases it?
Thanks!
Since plugins execute as 'part' of the database transaction, no database locks are issued until an actual 'update' statement is transmitted to the database, which happens when you issue the 'service.update(entity)' call. The transaction is not 'complete' until the CRM Event Pipeline says it is complete, which is not necessarily at the end of YOUR custom plugin, as there could be other additional plugins or operations that also execute as part of the same transaction. At the time the 'operation' is complete, all transactional locks will be released. For more information you can refer to