0

We are facing an intermittent issue(UNABLE_TO_LOCK_ROW ) and found below code causing the issue. We tried to use "For Update" but it did not help.

Error: UNABLE_TO_LOCK_ROW, unable to obtain exclusive access to this record or 1 records: 01sxxxxxxx:

Map<Id, Quote> loanNewQuoteMap = new Map<Id, Quote>();
for(Quote newQuote : [SELECT Id, Name, OpportunityId, pricebook2Id
                      FROM Quote 
                      WHERE Id IN : quoteOptionalProductsMap.keySet()
                      LIMIT 50000
                      FOR UPDATE]
                      ){
                      
    Quote quoteCreation = new Quote();
    quoteCreation.pricebook2Id  = newQuote.pricebook2Id;
    quoteCreation.OpportunityId = newQuote.OpportunityId;
    loanNewQuoteMap.put(newQuote.OpportunityId, quoteCreation);
}                     
Database.insert(loanNewQuoteMap.values(), true); // causing unable to lock row

On further investigation we found that it is specially happening with one pricebook which is being used a lot. Could you please suggest whether adding the "For Update" in above SOQL will place a lock on Pricebook(pricebook2Id) or not. I was hoping that "for update" should resolve this issue.

Thanks,

5
  • This is an inherent limitation of concurrent access to Pricebook: ideas.salesforce.com/s/idea/a0B8W00000GdgLhUAJ/… (the idea is for tests but the same issue exists in a non-test context). If this one Pricebook is a standard one, using additional; custom Pricebooks that are copies of the standard one might make a difference.
    – identigral
    Jun 30, 2022 at 17:10
  • 1
    @identigral In m case, it is a custom pricebook. I have looked into the Idea you shared but scenario is different there. In my case, I am not getting this error at the time of pricebook entry. I am getting this at the time of Quote creation. Jul 5, 2022 at 8:12
  • 01s is a Pricebook prefix. Experiment with "distributing" the price data over multiple Pricebook records to reduce lock contention across clients.
    – identigral
    Jul 6, 2022 at 0:25
  • @identigral Yes, I agree locking is with Pricebook. After reviewing all the customizations around this, it seems creating multiple pricebooks will take time. Do you think a retry approach can be helpful? Jul 6, 2022 at 10:24
  • A timed single or 2x retry wouldn't hurt to try.
    – identigral
    Jul 6, 2022 at 17:57

1 Answer 1

0

Could you please suggest whether adding the "For Update" in above SOQL will place a lock on Pricebook(pricebook2Id) or not

FOR UPDATE will place a lock if the lookup relationship is configured to clear the value of the field if the lookup record is deleted. You can check if this setting is configured on the Pricebook lookup field.

Your problem sounds to be due to lookup skew which usually occurs when you have one catch all lookup record. As a general guideline, you should avoid have a single lookup used for more than or equal to 10000 records. Violating this guideline will increase the possibility of high lock contention, which seems to be happening in your case. To solve this problem, you should start looking into how to distribute the lookup skew i.e., revisit the data model/ current data to see if multiple pricebooks can be created (which can then be distributed to quote lookups).

If you have any code logic that take longer duration for processing the records, check if you can offload some them to async processing. This would reduce the duration for which the record locking is held.

4
  • lookup skew should not happen in this case as I have only 1000 existing quote against the pricebook(say xyz). clearing the lookup is also not possible as this is a standard relationship and not available for EDIT. Jun 30, 2022 at 12:40
  • If there is no lookup skew, then don't bother trying to distribute the lookup. Can you check if you can offload the quote creation to an async (so the possiblity of lock contention could be reduced)? This is with the assumption that there are too many processes running in parallel now.
    – arut
    Jun 30, 2022 at 12:51
  • Unfortunately, we cannot run this Quote creation process async due to below reasons - This process gets triggered from a custom UI so result needs to be available synchronously. - Quote creation process is a sub process of a main process and all these sub process need to run in predefined chain. Jun 30, 2022 at 14:18
  • Is there any heavy code logic running post quote creation? If yes, you can try to offload some of them to async (esp., the code which is not required to be executed for sending the immediate sync response to the user). Longer the transaction duration, the record locking may also holder longer.
    – arut
    Jul 1, 2022 at 5:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .