Concurrency issue on Batches

Question

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.

Answer 1

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.

Answer 2

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