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.

it can't be both exactly sequential and not have potential errors from lock timeouts. — superfell, Mar 27 '13 at 19:29
@superfell: It doesn't have to be exactly sequential, just unique. — Paul Sasik, Mar 27 '13 at 21:18
any reason you would just use an auto-number field for this? — Ralph Callaway, Mar 27 '13 at 21:32
@Ralph: Good question and the answer is that we need to share ID creation among several different objects. If it was just the Account that needed an ID we would indeed just go with a custom auto-number. — Paul Sasik, Mar 28 '13 at 14:21
Sounds like whatever that id represents should be it's own object with it's own autonumber, and all the other objects should be related to it. — Ralph Callaway, Mar 28 '13 at 17:13
@PaulSasik did any of the answers below solve your issue? If so can you please accept one of them? — Ralph Callaway, Apr 07 '13 at 19:26

Ralph Callaway · Accepted Answer · 2013-03-27T22:41:21.603

14

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.

edited Mar 27 '13 at 22:41

answered Mar 27 '13 at 21:35

Ralph Callaway

24,529
17
112
190

1

Support can turn off autonumbering gaps caused by unit tests?! +1 for that little tidbit! – jkraybill Mar 28 '13 at 23:47

Daniel Ballinger · Answer 2 · 2013-03-27T19:53:53.760

12

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.

edited Mar 27 '13 at 19:53

answered Mar 27 '13 at 19:32

Daniel Ballinger

102,288
39
270
594

6

actually you're holding the lock for however long your account insert transaction takes (which could be significant if there are lots of other triggers and/or doing bulk inserts), and are serializing all account inserts via a single lock. – superfell Mar 27 '13 at 19:42
@superfell, whoops, yes, good point. I'll update the answer to reflect this. – Daniel Ballinger Mar 27 '13 at 19:46
@DanielBallinger: This is a trigger in a transactional process, never in bulk load. Your answer sounds solid but I'm worried about the timeouts and deadlocks comment. Timeouts occur but would a small number of simultaneous requests be handled by this method. e.g. two accounts created at "exactly" the same time? – Paul Sasik Mar 27 '13 at 20:58
@PaulSasik superfell has mentioned in another question that the blocked threads will wait "a short amount of time (in the order of 5-10 seconds)". It really depends on what else is happening in the transactions. E.g. Something waiting for a web service callout to complete could cause sufficient delay. – Daniel Ballinger Mar 27 '13 at 21:14
On second through, web service callouts might be a bad example as you can't directly call them from triggers or before completing the DML operations. – Daniel Ballinger Mar 27 '13 at 21:15
If you can have gaps, just use an auto-number field. – superfell Mar 27 '13 at 21:45

score 5 · Answer 3 · answered Mar 27 '13 at 20:29

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.

How to atomically increment a custom field?

3 Answers3

Linked

Related