How do I avoid race conditions when importing a large batch and running future methods?

Question

I have an opportunity after delete trigger that undeletes and updates the trigger.old records in a future method. Part of this update is that it reassigns the AccountId field to an abstract account used to collect opp records that were deleted by users (in an effort to salvage deleted data for reporting purposes).

Several of these batches fail due to UNABLE_TO_LOCK_ROW error, though I don't know why. Each of these future batches is not updating the same record - they are only updating the opportunities in the each respective batch. That said, the problem seems to be that I'm assigning the same AccountId to each record in every batch. It's acting as though this necessitates an update to that account (maybe something to do with the opp related list for that account...?).

I've pinpointed that the problem is indeed the AccountId being added. So I've addressed the problem by locking the record 'for update' in the SOQL query. Although this leads to the famous 'the record you are attempting to edit is currently being modified by another user' error, so I've resorted to a no-no - that is, I've put this attempted query/record lock in a while loop until it's successful. Not sure what else I can do to check for the lock and re-try if it's still there.

HOWEVER, when deleting a batch of 50,000 records I always end up with between 3 and 15 batches failing, either because I get a QUERY_TIMEOUT (I'm not running any massive queries at all, one queries 200 records and another, the one mentioned above, queries the one account), or less commonly one batch will get a too many SOQL queries error. Which is strange, because looking at the logs, the highest number of queries of any of the successful batches is 35 out of the 200 allowed, average being 9-10. Not sure how one batch could possibly reach 201 queries...

Some code:

trigger tOppUndelete on Opportunity (after delete)
{
    List<Id> oppIds = new List<Id>(trigger.old.keySet());
    undeleteOpps.executeUndelete(oppIds);
}

public class undeleteOpps
{
    //requires future method to undelete in the same transaction as delete
    @future
    public static void executeUndelete (List<Id> oppIds)
    {
        database.undelete(oppIds);
        //ignore all the selected fields, I omitted the code where these were being used for simplicity
        string query = 'SELECT Id, Name, IsDeleted__c, StageName, AccountId, OwnerId, (SELECT Id, ContactId, IsPrimary, Role FROM OpportunityContactRoles) FROM Opportunity WHERE Id IN :oppIds';
        List<Opportunity> opps = database.query(query);
        Id deletedOpps = [SELECT Id FROM Account WHERE Name = 'Deleted Opps'].Id;

        for (opportunity o : opps)
            o.AccountId = deletedOpps;

        //below is what I added to overcome the UNABLE_TO_LOCK_ROW error
        boolean locked = false;
        while (!locked)
        {
            locked = true;
            try {account a = [SELECT Id FROM Account WHERE Id = :deletedOpps FOR UPDATE];}
            catch (exception e) {locked = false;}
        }
        update opps;
    }
}

A few questions:

1) Why am I running into race conditions when each future batch is updating unique records? I am not updating the account that's causing the issue.

2) Is there any way besides performing a query to check for whether a set of records is locked for update? I would love to eliminate these incrementing queries on each batch...

3) Are there any better suggestions to solve this problem than what I am currently using?

4) I can't imagine I'm the only one who faces the problem of mass inserts and race conditions due to a future method being employed. What methods and best practices does Salesforce provide to overcome this problem and ensure these kinds of operations can be performed successfully?

UPDATE: This phenomenon seems to only occur with opportunities. I re-created the exact same process out of curiosity for contacts, and I don't even get the UNABLE_TO_LOCK_ROW error in the first place. I can simply update the batch without a need for locking. Why?? Does moving an opportunity to an account affect some roll-up field on the account that triggers an account update or something? Looking over account fields I couldn't find anything like this...

Answer 1

See Avoiding Deadlocks. Note that it mentions that parents are locked before children, so the accounts are being locked so their sharing can be recalculated. Make sure you're using locks on everything, starting with the old accounts and the deleted opps account, then all of the opportunities, and then the line items.

Unfortunately, using the "try-query-catch-loop" pattern you've shown for locks is the only real solution we have if you expect a lock to last longer than five seconds. I've personally implemented this myself, and it works beautifully. Alternatively, you might consider holding off on performing your undelete logic when batching, and have the batch chain to a new batch to perform your undelete, move, whatever-you're-doing logic.