UNABLE_TO_LOCK_ROW error

Question

I'm getting this error on before insert:

Developer script exception from Organization : Chat_copyUserAgentToNewCase : Chat_copyUserAgentToNewCase: execution of BeforeInsert caused by: System.DmlException: Update failed. First exception on row 0 with id 500w000001lAWqnAAG; first error: UNABLE_TO_LOCK_ROW, unable to obtain exclusive access to this record or 1 records: 500w000001lAWqnAAG: [] Trigger.Chat_copyUserAgentToNewCase: line 14, column 1

 Trigger Chat_copyUserAgentToNewCase on LiveChatTranscript (before insert) {
    Map<Id, Case> cases = new Map<Id, Case>();
    for(LiveChatTranscript trans: Trigger.new) {
     if(trans.CaseId != null && trans.UserAgent != null) {
        Case c = new Case(Id=trans.CaseId,User_Agent__c=trans.UserAgent);
        cases.put(c.Id, c);
    }
}
update cases.values();

}

I read this post and the chosen answered suggests to use FOR UPDATE, the problem is that I don't query the cases because I loop over Trigger.New.

Answer 1

Even though you have a record ID, if you try an update, a lock must be acquired first. Using FOR UPDATE forces the system to wait while it can acquire a lock on the record. It is recommended that you query any records you intend to update with FOR UPDATE before attempting a DML on the record. Also, I generally recommend updating "related" records (parents or children) using after triggers, so that standard system validations have a chance to fully execute before the records are saved to the database.

Trigger Chat_copyUserAgentToNewCase on LiveChatTranscript (after insert) {
    Map<Id, Case> cases = new Map<Id, Case>();
    for(LiveChatTranscript trans: Trigger.new) {
     if(trans.CaseId != null && trans.UserAgent != null) {
        Case c = new Case(Id=trans.CaseId,User_Agent__c=trans.UserAgent);
        cases.put(c.Id, c);
    }
}
if(!cases.isEmpty()) {
    Case[] locks = [SELECT Id FROM Case WHERE Id = :cases.keySet() FOR UPDATE];
    update cases.values();
}

Answer 2

It is not about querying through SOQL.

You are updating cases based on trans.CaseId. Now salesforce is trying lock Case record at the time of update. Here you have multiple LiveChatTranscript records which are connected to same parent cases, so for each insertion of LiveChatTranscript record system tries to lock the parent case, but the same transaction, the same record is lock by others.

Since, it is unable to hold the lock to update same cases, it is giving UNABLE_TO_LOCK_ROW error.

Hope it makes sense!