25

I am looking at a solution to providing a guaranteed contiguous sequence of numbers. This cannot be achieved via autonumber fields as numbers in the series are consumed by failed validations etc. - not to mention the problem with tests burning the sequence as well (though this can be mitigated by asking SFDC to activate a beta feature to enable rollbacks to roll back the sequence, at the expense of some performance).

One idea is to use a custom setting. Custom settings values are held in an application cache to minimise DB access. If a custom setting is updated via DML, is the value replicated into all instances of the cache immediately?

My concern is that where there is multiple concurrent access to this number, at the point of update the cache could be stale for at least some requests.

Has anyone solved this problem satisfactorily?

5

As has been stated auto numbers won't give this and implementing it in the cloud where locks and such resources are rightfully less available. So here is another suggestion you might want to put to your business analyst...

"Secondary Numbering System". While I'm not sure of your root requirement here, if your client wants to be sure to see this information for governmental or audit reasons. Why not consider adding a custom field that represents a second numbering sequence that can be updated by a scheduled job every night, week or month. You can put controls in place to prevent this being tampered with and ensure the reports only include records with this applied. Further more you also get the flexibility of implementing your own system, with your own starting point, reset as required and sub-sequences if needed.

Hope this helps give some food for thought! Enjoy!

  • That is an interesting idea... it might be the only way of ensuring a unique, contiguous sequence by funnelling the generation through a single job. Thanks for the suggestion. – AlexF Nov 13 '12 at 11:20
  • Nice glad you think so, I often find when you peel far enough back to the root requirement on things other interesting approaches surface! Good luck! :) – Andrew Fawcett Nov 13 '12 at 18:28
3

I have dealt with code that gets a value from a custom setting, uses it, increments it and then stores it back from a trigger. The issue is that it is not an atomic operation and all that it takes is two users accessing it about the same time to fail.

trigger MyObjectTrigger on My_Object__c (before insert) {
    MySettings__c mySettings = MySettings__c.getOrgDefaults();
    Double myAutoNum = mySettings.My_Custom_Auto_Number__c;
    for (My_Object__c myObj : Trigger.new) {

        myRec.My_Auto_Number_Field__c = myAutoNum;

        myAutoNum = myAutoNum + 1;
    }
    mySettings.My_Custom_Auto_Number__c = myAutoNum
    update mySettings;
}
  1. You can get the UNABLE_TO_LOCK_ROW error if two users are trying to update the custom setting at the same time.

  2. You can get a situation where two users get the same auto-number.

One way to protect against duplicates is to mark the object's field as unique. This will prevent the insertion when two values are the same, because the second user's record will fail to insert. That may be a "good enough" solution depending on what it is that you are trying to accomplish.

If it is good enough, make sure that your unit tests do not share the same custom setting. That is, make sure that each one of them is API version 24+, don't use @SeeAllData=true, and that they all create and use their own custom setting. Salesforce runs the unit tests on deployment in parallel, so if any of them share a custom setting they can fail nondeterministically.

  • Agreed - the problem is making the increment atomic... I am looking for a solution which is not susceptible to gaps in the sequence or duplicates at all. Thanks for the reply. – AlexF Nov 12 '12 at 14:33
  • 3
    Locking can be obtained by forgetting about the syntax of accessing cust. settings with getInstance() etc. Treat them as sObjects and use FOR UPDATE at the end of the SELECT. Of course that wastes queries, makes caching useless etc... – eyescream Nov 12 '12 at 15:00
  • hey peter, im trying to implement this solution too. how did you create the custom setting? – tony danza Mar 19 '14 at 16:09
  • 1
    @tonydanza - A hierarchy custom setting. Eyescream's advice about using SOQL and the FOR UPDATE is a good idea. – Peter Knolle Mar 19 '14 at 23:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.