Exception in @Future Method - System.QueryException: Record Currently Unavailable

Question

I am facing error in one of future method (calling from trigger handler). This error occurs when integration event submit these record in bulk but when i am doing this from UI i do not get this error. I am trying to recreate scenario but it work fine when i do from UI. Can anyone please help me on this.

Failed to invoke future method 'public static void getOpportunity(Set, Set)' on class 'OpportunityCloning_Handler' for job id '7070e00004gqK5G'

caused by: System.QueryException: Record Currently Unavailable: The record you are attempting to edit, or one of its related records, is currently being modified by another user. Please try again.

Class.OpportunityCloning_Handler.getValidOpportunity: line 498, column 1
Class.OpportunityCloning_Handler.CreateOpportunity: line 553, column 1

My Future method is

    @future (callout=true)
    public static void getOpportunity(set<id> oppId,set<String> quoteIds){
        CreateOpportunity(oppId,quoteIds);
    }


public static void CreateOpportunity(set<id> oppId,set<String> quoteIds){ 
    list<Opportunity> Opportunities1 =  getValidOpportunity(oppId, quoteIds);  // This has query on Opp and related Object with FOR UPDATE

}

public static list<Opportunity> getValidOpportunity(set<id> oppId,set<String> quoteIds)
    {
        list<Opportunity> Opportunities1 = null; 

        if(quoteIds == null || quoteIds.isEmpty())
        {
            Opportunities1 = 
                [select id,Account__c,Account__r.Unique_Identifier__c,Account__r.bitLink__c,Account__r.eEnrollment__c,Account__r.segment__c,
                 (Select P_Location_Contact__c,P_Location_Contact__r.P_Contact__c, P_Location__c,
                  From Q_Opportunity_Producer_Filters__r) 
                 (SELECT Id, Comment__c, Type__c FROM Comments__r WHERE Type__c = 'Comments for ASS')
                 from Opportunity where (id IN: oppId) AND  RecordType.Name != 'Pre Sale'
                 AND (Quote_Status_changed_by__c ='Manual' OR Quote_Status_changed_by__c ='Integration Event')AND IsASSCreated__c = false FOR UPDATE];    
        }else{
            Opportunities1 = 
                [select id,Account__c,Account__r.Unique_Identifier__c,Account__r.bitLink__c,Account__r.eEnrollment__c,Account__r.segment__c,
                 (Select P_Location_Contact__c,P_Location_Contact__r.P_Contact__c, P_Location__c,
                  From Q_Opportunity_Producer_Filters__r) ,
                 (SELECT Id, Comment__c, Type__c FROM Comments__r WHERE Type__c = 'Comments for ASS')
                 from Opportunity where (id IN: oppId OR QuoteID1__c IN:quoteIds) AND  RecordType.Name != 'Pre Sale'
                 AND (Quote_Status_changed_by__c ='Manual' OR   Quote_Status_changed_by__c ='Integration Event' ) AND IsPSSCreated__c = false FOR UPDATE];    
        }


        return Opportunities1 ;
    }

Trigger calling getopportunity method :

try{
            list<Integ_Exceptions__c > lstErros = new list<Integ_Exceptions__c >();
            set<Id> OppId = new set<id>();
            set<String> QuoteIds = new set<String>();

            for(Opportunity opp : newRecords){

        //Code-Logic

            }
            if((OppId!=null && OppId.size()>0) && sObj_Utility.isopptyCreated == false) {
                OpportunityCloning_Handler.getOpportunity(OppId,QuoteIds);
            }
            if(lstErros.size() > 0)
                Database.insert(lstErros ,false);
        }
        catch(Exception e){
            Integ_Exceptions__c err = new Integ_Exceptions__c( Area__c = 'AMM Creation', 
                                                    Message__c = e.getMessage() +'\n' + e.getStackTraceString() );
            insert err ;  
            }
       }

    }

Answer 1

You appear to have a different species of lock contention than other questions we've seen recently about implicit lock contention, leading to UNABLE_TO_LOCK_ROW errors. What you have is a situation where two elements of your application are attempting to establish an explicit lock using a SOQL FOR UPDATE clause at the same time, resulting in one of the lock attempts timing out before the other is released.

When you establish that lock, it persists until your transaction closes. Other transactions seeking to lock the same object can time out with a QueryException if the lock is held too long:

If you attempt to lock a record currently locked by another client, your process waits for the lock to be released before acquiring a new lock. If the lock isn’t released within 10 seconds, you will get a QueryException.

Based on the fact that you are establishing these locks in an @future method called from a trigger, to me the most likely theory that occurs is that said @future method is being called twice, and the two enqueued jobs are being executed by the system at close enough to the same time to contend with one another for the locks.

Why might the job be enqueued twice? Well, there's a number of reasons why the same trigger can be fired multiple times for the same records. Based on your note that you observe this issue only with bulk data loads, it could be that your trigger code is poorly bulkified and fires the future method more than once for the same inbound record set. Or your bulk input may result in a different code path being executed that's more vulnerable to a recursion issue.

Many of the possible paths also involve some species of trigger recursion, either from explicit DML operations your code executes or from a Process or Workflow Rule that runs later in the Order of Execution and causes the trigger to run a second time. We don't know, because we don't even see how you call this method from the trigger, but that's the first culprit I would be looking at myself.

Example

Here's an example I created in a Trailhead Playground org. This is a great example because I messed it up - I set out to show you how a future method would get called twice, and I actually got it called four times because of trigger recursion I didn't realize was there.

Trigger

trigger AccountsTrigger on Account (after delete, after insert, after update, after undelete, before delete, before insert, before update) {
    if (Trigger.isBefore) {
        TestQ244395.runFuture();
    }
    
    if (Trigger.isAfter) {
        TestQ244395.runFuture();
    }
}

Here, we call an @future method twice, once in a trigger before event and once in an after event. These are enqueued in the same transaction, but each will execute in its own, separate transaction.

Class

public class TestQ244395 {
    @future
    public static void runFuture() {
        System.debug('In future method');
    }
}

Note that this doesn't demonstrate lock contention - I'm just aiming at showing one route whereby your future method could be called more than you think it will be.

Log Entries

Here's what the log entries look like after I run an Execute Anonymous with this code:

insert new Account(Name='TestQ244395');

What I wanted to show you was two FutureHandler log entries as a guide-post to identifying if this is really the source of your issue. Instead, I got four entries. Why? Let's look at the executeAnonymous entry.

I'm not going to include the whole log for brevity, so here's a screen shot filtered by CODE_UNIT so we can see where Salesforce starts executing each code unit.

Notice here the critical line

21:05:56:158 CODE_UNIT_STARTED [EXTERNAL]|Workflow:Account

followed by

21:05:56:636 CODE_UNIT_STARTED [EXTERNAL]|01q41000001d3z6|AccountsTrigger on Account trigger event BeforeUpdate|__sfdc_trigger/AccountsTrigger

21:05:56:661 CODE_UNIT_STARTED [EXTERNAL]|01q41000001d3z6|AccountsTrigger on Account trigger event AfterUpdate|__sfdc_trigger/AccountsTrigger

I forgot that this org had a workflow rule that fired on Account creation. When the workflow rule went off, it performed an update that resulted in firing my trigger again - meaning that I got a total of four future methods firing.

This is where you'd start approaching your issue, I think, to determine whether my surmise that this is what's happening is correct or not.