UNABLE_TO_LOCK_ROW, unable to obtain exclusive access to this record or 200 records - Error even after using FOR UPDATE

Question

I have a trigger and I am calling a queueable job from trigger. Sometimes I get this error:

Update failed. First exception on row 0 with id a0VN00*********; first error: UNABLE_TO_LOCK_ROW, unable to obtain exclusive access to this record or 200 records:

Below is my query before updating the recods:

list<MyObject> lst = [select id, Account_Id__c from MyObject where 
                            Account_Id__c in:setofAccountID FOR UPDATE];    

I have referred to Can anybody explain the UNABLE_TO_LOCK_ROW error? and used FOR UPDATE. Still, I get the error.

Answer 1

You'll want to read this answer for more information, but the gist is that just because you use FOR UPDATE, does not mean you will eliminate this error. Instead, if the lock does not get acquired after approximately ten seconds, the transaction throws an exception. If you absolutely must try to obtain a row lock, you can do so in a do-try-catch-while loop (or alternatives):

SObject[] results;

do {
  try {
    results = [select ... from ... for update];
  } catch(QueryException e) {
  }
} while(results == null);

You may also want to limit the query to a number of retries, but usually your loop won't need to go more than once or twice before you acquire the row lock for your records.

I would personally only consider repeating a row lock in asynchronous code; if you can't get a row lock in a trigger or other real-time context, just return a friendly error so the user can try again later.