Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
AutoNumber Plugin - Record Locking Issues? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I am creating a custom entity to manage my auto numbering requirements.
    I've created a plugin that uses the Pre-Event to get the next number, update the AutoNumber entity, and then issue the number to the target entity being created.

    There are lots of posts on this subject, but none seem to cover record-locking when dealing with plugins. Is my plugin transaction safe (so that no two users are given the same number), or do I need to add a locking attirbute to the autonumber entity that is turned on whilst issuing the number and then turned off once complete, thus forcing other transactions to wait in turn.

    Many thanks

    Steve

    Tuesday, April 28, 2009 12:05 PM

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Good point Steve. I thought about the same problem and this is the solution I came up with. Try appending the logged in users initial to your autonumber? e.g. HNH0002 where HNH are users initials.

    If you don’t want the users identity to be displayed you could use simple encryption like adding 2 to all characters e.g. H=J, N=P

    So HNH will become JPJ. OR you could try some thing a bit more complex.

    This will overcome the locking problem as same user would not be logged into two computers at the same time. 

    Hope this helps.

    Hassan.

    Technical Consultant | http://hassanhussain.wordpress.com/
    • Proposed as answer by Hassan Hussain Tuesday, April 28, 2009 12:55 PM
    • Unproposed as answer by lemonje Wednesday, April 29, 2009 8:28 AM
    • Edited by Hassan Hussain Wednesday, April 29, 2009 12:19 PM
    Tuesday, April 28, 2009 12:55 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    This might not be fancy but I would actually create a nightly batch that retrieves all record that are missing an auto-number sorted by createon and simply fill them up. This avoids the need to lock the incremental process which invites all sorts of trouble.

    Blog: http://mscrm4ever.blogspot.com/ Website: http://gicrm.upsite.co.il/
    Wednesday, April 29, 2009 2:49 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Is your values retrieved form a field with a predefined value which incrments when used?
    Tiaan van Niekerk http://crmdelacreme.blogspot.com Skype:tiaan.van.niekerk1
    Wednesday, April 29, 2009 4:29 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    The way I've always done it is to use a separate SQL table in a separate database to keep track of the numbers, and use SQL stored procedures with appropriate SQL locking to get the next number. The SQL locking is a robust way to isolate transactions, and keeping the locking out of the CRM database or CRM platofrm minimises concurrency problems
    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
    Wednesday, April 29, 2009 5:13 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks Everybody for the feedback.

    I was a little surpirsed by some of the sugestions and nobody as yet has explained the transaction locking when pluggins update crm data!

    I'm looking for a 100% crm solution, using foreign databases that need to be kept in sync with crm is not an option for me, nor is reading or writing to flat files.

    I guess a plugin utilises transactions whilst its writing back to the crm database then I'm probably worrying about nothing, but if it doesn't then there is always a chance that my auto number entity could give out the same number to two callers, and this is what I need to prevent. If there is no transaction isolation protection then flipping a boolean flag on the entity would be an okay approach. I would just have to make sure that my pluggin was written in such a way that when encountering the flag in a positive state it waited a quarter of a second before trying again etc etc.

    If somebody could confirm that this approach is unneccessary I'd be delighted.
    Regards

    Steve
    Wednesday, April 29, 2009 8:35 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hey Steve

    I posted a plugin a while back, http://weblogs.asp.net/gayanperera/archive/2008/10/23/crm-4-incremental-numbering-for-any-entity.aspx, you can download the code from http://crmnumbering.codeplex.com/ it's got a fix for locking
    http://weblogs.asp.net/gayanperera/
    • Marked as answer by lemonje Wednesday, April 29, 2009 11:11 AM
    Wednesday, April 29, 2009 9:42 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Gayan

    Thanks for sharing this, it's a robust solution that keeps the data in the MSCRM database. From a data perspective, the only difference between this approach and my approach is that the in my approach the SQL table that tracks the incremental numbers is stored in a separate database, and is accessed directly via SQL, whereas you use the same data approach but the data is in a CRM entity in the MSCRM database, and is updated via the CRM platform.

    The practical differences are that locking at the SQL level blocks users for less time that locking the code in the platform, but an advantage of keeping the data in the MSCRM database makes life easier in disaster recovery scenarios, rather than having to ensure 2 databases are restored consistently. Strictly it would be possible to combine the advantages of the 2 approaches (keep the data in MSCRM, but lock at the SQL level), but that would be unsupported.

    David
    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
    Thursday, April 30, 2009 8:15 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi

    Here's another autonumber solution http://m-atif.blogspot.com/2011/12/auto-number-solution-for-dynamics-crm.html

    It can be cutomized according your needs.

    Might be useful for someone out there.

    never give up.
    Thursday, December 22, 2011 12:17 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    You have to take it one step further to make it really robust. Solutions that use locking Objects and mutexes are not going to work if you have multiple CRM servers, only a database lock is truly going to give you 100% sequential access.

    We previously used a method just like David Jennaway mentioned, an external DB with stored procs that handle the retrieve/update of the sequence atomically.

    However in CRM 2011 you CAN do it as a pure CRM solution - I suggest the following steps to make it 'safe' for concurrency.

    1. create a dummy field on your 'sequence' storage entity

    2. in the plugin, retrieve the guid of the sequence entity row you want for the current entity. ONLY the guid. You do not have a lock yet so the nextId value is not reliable.

    3. Update the 'dummy' field for that record, thereby locking that sequence in the DB.

    4. Now that it has a DB lock, read the next id for that sequence, and increment it.

    I just wrote this up today and waiting for the blog post to hit the airwaves...

    Ken

    Thursday, January 5, 2012 9:45 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Disclaimer: I work for North52, and we have both free (community edition) & paid options on our product.

    As part of N52 Formula Manager we have an auto number function which will resolve your issue & you don't need any coding. The best  bit is that there is a free community edition available so you can deploy to production environments for free. 

    Here's a link to a video showing how it works & the formatting of the number is completely flexible.

    http://www.youtube.com/watch?v=72aCs1a78BE

    Those looking for specifics on the algorithm we use can read the blog post below which is very similar to what we do internally.

    http://connect.greenbeacon.com/2012/01/technical-post-implementing-robust-microsoft-dynamics-crm-2011-auto-numbering-using-transactions/#comments

    John

    John Grace, Founder at North52 www.north52.com

    Saturday, September 8, 2012 12:24 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Gayan,

    Your solution looks pretty neat and I sure it will work perfectly on a single crm server deployment.

    However I have doubts if it is robust enough for a multi-server (load balancing) situation. Quote from msdn "Global mutexes, also called named mutexes, are visible throughout the operating system". I take it as it won't work stable enough for multi-server/multi-os deployment. Maybe that why Ken has pointed out that mutex implementation will not work for crm online.

    Having said that, I don't actually have the luxury to test it in a load-balancing crm environment.

    Cheers

    Wednesday, December 26, 2012 6:09 AM