How to atomically increment a custom field?

Question

We are creating custom IDs for Accounts by incrementing an integer field in a custom class. The issue is that if two or more new Accounts are created in close sequence/simultaneously the following code will assign duplicated numbers rather than an incremented number for each:

public static void SetCustomId(Account account)
{       
    CustomObj__c customObj = CustomObj__c.getOrgDefaults();

    account.CustomAccountId__c = String.valueOf(Integer.valueOf(customObj.CustomCounter__c));
    customObj.CustomCounter__c = customObj.CustomCounter__c + 1;

    update customObj;
}

Is there a way in Apex to lock a process so that a shared resource is used sequentially? In Java, C#, C++ etc. this is pretty easily done via a critical section if it's a single process or a Mutex if multiple processes access the same resource. Is there anything like this supported in Salesforce? Solutions beside Apex code/triggers would be fine as long as they meet the spec.

EDIT: It is important that the object creates be sequential and not generate errors due to lockouts.

Answer 1

The simplest way to do this is to let the database do it for you. Create an auto-number field and you'll have a sequential id with some caveats (read the "Latest Comments from Product Management").

One note of caution is that by default records created in tests will increment the auto-number which will cause gaps. Contact support to turn this off.

Answer 2

The only mechanism I know of is FOR UPDATE in a SOQL query. See Locking Statements.

public static void SetCustomId(Account account)
{       
    CustomObj__c customObj =  
        [SELECT Id, CustomAccountId__c FROM CustomObj__c LIMIT 1 FOR UPDATE];

    integer currentCounter = Integer.valueOf(customObj.CustomCounter__c);

    account.CustomAccountId__c = String.valueOf(currentCounter);
    customObj.CustomCounter__c = currentCounter + 1;

    update customObj;
}

FOR UPDATE does however have the potential to create timeouts and deadlocks. This will manifest itself as a UNABLE_TO_LOCK_ROW error.

Here the risk seems minimal as you aren't holding the locked record for very long or locking on any other sObjects. UPDATE: See the comment from superfell, in isolation this lock is minimal, in reality it will be part of a larger transaction. The lock will be in place until the transaction completes or is rolled back.

One additional thought, if you are calling this from an Account insert trigger it should be bulkified to work with a collection of Accounts. Better to lock the custom setting once and increment it by the required amount than to lock and increment it multiple times.

Answer 3

Whilst not ideal, the way I have implemented this recently is was to use the mechanism that you have described in your original post, but simply make the CustomCounter__c field a Unique field on your object.

That way, you can rely on the platform to prevent you from creating duplicates and the error message that you receive will actually tell you that it is a duplicate. I have educated users to simply click Save again in this occurrence.

Not ideal, but an alternative to locking.