4

I have two batch classes that run concurrently. Let's call them Job A and Job B.

Job A inserts records in object A. And Job B in object B. Object B has a lookup to Object A.

Because this lookup is mandatory, and because we do not know whether we'll have the record in Object A populated before the record in Object B is saved, we run a quick check and create a "shell" Object A record (Name and ExternalID are the only two fields populated), expecting that Job A will eventually upsert the records and fill in the blanks.

Problem is, we're getting an error when saving Object A records: duplicate value found. I've been able to diagnose that the only records that fail with this error are those ​being created by the current batch in Job B.​. That is, those records processed in previous batches of Job B get upserted properly. Obviously, for those Object B records created after this failure there is no need for the "shell" records, therefore there is no fault.

The error is consistently on Job A, even though both jobs do upserts. This makes sense because batches of Job B upsert the shell records at the very beginning, whereas in Job A we have to wait until the end of the batch...

This sounds like a concurrency issue... but I can't figure out how to get around it. Help!

PS: There are good business reasons for the parallel process, we already know this is not a problem if we process Job B after Job A finishes.

  • I'll add code samples upon request... but I don't think they'll add much value to the chat – Sebastian Kessel Apr 14 '16 at 18:34
  • Dan Appleman has a chapter in his 3rd Edition of Advanced Apex Programming that covers Concurrency along with methods for dealing with it, including various ways of testing for it as well. I'd highly recommend you refer to it. You can get the kindle version of the book for less than $10 or a discount on the paperback if you go to www.advancedapex.com. – crmprogdev Apr 14 '16 at 18:42
  • I have the 2nd Edition with me... reading it now – Sebastian Kessel Apr 14 '16 at 18:46
  • 1
    I don't know if he covered it in his 2nd edition or not. I went from 1st to 3rd. – crmprogdev Apr 14 '16 at 18:47
4

You should be using row-locking mechanisms in both jobs so that you can ensure that the A records do or do not exist before attempting to perform the main logic:

// Job A
public void execute(Database.BatchableContext context, Data[] scope) {
    // Build values for External Ids from scope, then...
    A__c[] records = [SELECT External_Id__c FROM A__c WHERE External_Id__c IN :externalIds FOR UPDATE];
    // We are now assured that all A__c objects that were in-flight from Job B are present
    // Do more stuff here
}

// Job B
public void execute(Database.BatchableContext context, Data[] scope) {
    // Build values for External Ids from scope, then...
    A__c[] records = [SELECT External_Id__c FROM A__c WHERE External_Id__c IN :externalIds FOR UPDATE];
    // We've now waited for in-flight A__c records, but we also need to
    // insert new A__c records as soon as possible, so build shell A__c
    // records, then...
    insert newARecords;
    // Now, Job A can wait for in-flight shell records.
    // Finish doing B__c records here.
}

There's still a small risk of collision, so you might want to put the entire mess into a do-while loop with a try-catch block in the middle:

do {
    try {
        A__c[] records = [SELECT ... FOR UPDATE];
        insert newARecords;
        break;
    } catch(QueryException e) {
        // Timeout on row locks
    } catch(DmlException e) {
        // We need to retry our insert because of duplicates
    }
} while(true);

You will probably only need this technique if inserting your A__c records takes more than ~5 seconds or either execute function may take longer than 5 seconds, since that's the approximate lock timeout. You might also consider using batches smaller than 200 rows if they're taking too long.

  • 1
    I don't think they are locking... Likely this is a factor of the Job B's upsert "taking the externalIDs" but not committing... so Job A sees the potential dupe but can't do anything about it. However, it's an interesting approach. Will try it out – Sebastian Kessel Apr 14 '16 at 18:59
  • @SebastianKessel I rarely have a use for locking statements myself, but I'd be interested in knowing if it solved your problem. Best of luck with that. I know that locking issues is usually quite painful in larger orgs. I had to refactor some code just this week to accommodate some locking errors of our own, in fact. – sfdcfox Apr 15 '16 at 6:01
  • Hey, Turns out this didn't quite fix it (hence my non acceptance of the answer). As far as we were able to determine, this was a problem with the internal "upsert" query (the one that determines whether to insert or update). In the end, what we did is save the failed records and retry job A with those records only... It worked perfectly, though it is not as elegant as I would've liked. PS: Both Jobs are rather long running (10+ secs per batch), likely why For Update didn't fix it. – Sebastian Kessel Apr 15 '16 at 14:58
  • 1
    Well, that works, too. You can post that as your own answer to your question if you'd like. – sfdcfox Apr 15 '16 at 15:24
  • Not a bad idea... let it be a cautionary tale. :) – Sebastian Kessel Apr 15 '16 at 15:25
0

So, turns out that @sfdcfox's solution didn't quite fix it (hence my non acceptance of the answer). As far as we were able to determine, this was a problem with the internal "upsert" query (the one that determines whether to insert or update).

In the end, what we did is save the failed records and retry job A with those records only... It worked perfectly, though it is not as elegant as I would've liked.

PS: Both Jobs are rather long running (10+ secs per batch), likely why For Update didn't fix it

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.