8

My scenario is as followed, I have a single Parent account with about 600 child accounts. Every child account has a single Opportunity with 2 or more lines. I have a monthly batch job to create records in a Custom Object (Opp_Revenue__c) for each Opportunity Line Item. This batch runs for 100 oppportunities at a time. Opp_Revenue__c has a master-detail relationship to Account, the lines in that get created in the batch are all detail lines to the Parent account. However, also the Opportunities get updated (those are related to the child records).

Starting from last month, I got the UNABLE_TO_LOCK_ROW error. I only get the problem on a single iteration of the batch. The system is unable lock rows only of type account, but both the parent and about 60 child accounts are in the list.

The org in which this problem occurs has had Private sharing for accounts enabled from the start. There used to be a single Sharing rule, based on a custom text field on Account to share certain records with a community user. Since last month, some sharing rules were added to restrict access to certain public groups based on the accounts standard type field.

I'm 100% sure that no people were even logged in to Salesforce during the execution of the batch. There were no other Apex jobs or scheduled reports running at the same time. My batch does not call any future methods or queueable classes and does not schedule any other Apex job or batch.

My questions are; could the locking issue in a batch be caused by the addition of the sharing rules? Am I correct in the assumption that this problem can not be caused by interference between two iterations of the same batch? I have read a lot about the error and there could be a lot of causes for this, but almost none of the articles are about batches.

|improve this question
  • Are there any owner changes as a result of the batch logic? Any deleted records? (You mention creating the new custom objects and updating the Opps) – Brent Bow Mar 13 '17 at 18:41
  • Also, are there any roll-up summary fields on the Account object related to Opps? – Brent Bow Mar 13 '17 at 18:42
  • There are no new owners and no roll-up summaries. Also, no deletions take place. Basically, new records get inserted into the custom object and the related opportunities get updated to reflect that. – rael_kid Mar 13 '17 at 18:51
  • Using FOR UPDATE keyword in query helps to achieve a lock a client end to prevent this locking issues . – user2815826 Mar 17 '17 at 10:27
10
+100

The issue is some combination of data skew and (potentially) ownership skew. Furthermore, sharing rules might lengthen the time of parent record locks, leading to increased chances for row locks.

Data Skew

Because all records of both objects are related to the (single) Parent Account, the update of each Opportunity and the insertion of each Opp_Revenue__c will briefly lock the Parent Account in order to maintain DB integrity and to recalculate sharing. There is a risk that an update to a child record will fail because a previous child record is still holding the lock on the Parent Account.

Remedy: Divide the single Parent Account into several and evenly distribute the child Accounts between them. Group the Opportunity batches by limiting the scope to one (grand)Parent account at a time.

SF Developer Blog: Data Skew Row Locks

Ownership Skew

If there is a default/"generic" owner for many/all of these records, and they have a nested (not top-level) role in the Role Hierarchy, sharing recalculations for Opportunities and Opp_Revenue__c will take longer to complete, increasing the chances of row locks.

Remedy: Either re-assign the Accounts to different individuals, remove the skewed owner's role, or place the skewed owner in their own role at the top of the hierarchy without child nodes. Also make sure any sharing rules used to grant visibility to skewed records are as restrictive as possible by granting access specifically to users who need it.

SF Developer Blog: Ownership Data Skew Row Locks

Sharing Rules

As stated off-handedly in the first section entitled "Problem Diagnosis" of this SF Developer Blog, "Lydia also doesn’t realize that the sharing configuration that she and Mike set up prior to the data load is further contributing to the poor loading performance which is then magnifying her lock contention problems." Although it doesn't mention sharing rules explicitly, it seems to make sense to me that including certain sharing rules that call for many sharing records to be created might lengthen the time of row locks.

Remedy: Change the OWD sharing defaults for the Account object, or grant "view all" or "modify all" permissions via permission sets or at the profile level for those users who need access to these records, if feasible.

Additional Resource: SF Record Locking Cheatsheet

NOTE: If you are using the Bulk API rather than batch apex, disable parallel loading.

It still strikes me as odd that row locks are an issue in your case, since common wisdom says this typically only becomes a skew issue when there are >10k child records or >10k records of the same object owned by a single owner...

|improve this answer
  • Suppose, instead of splitting the parent account, I'd introduce a new level in the hierarchy. So about 10 new sub parents, to which the current childs are related. The 10 sub parents will all be a child of the current parent (but there will be no other records related to that parent in this case). Would the grandparent still be locked? The problem is; the grandparent gets an overview (VF PDF) of all revenues of his child accounts, so in the end, all of the Revenue records should in some way be related to the ultimate parent. – rael_kid Mar 14 '17 at 6:36
  • Unfortunately, yes. If the Opp_Revenue__c records are still going to be details of the single (grand)parent account, the row lock contentions will still be an issue (since their insertion is the most likely culprit). As you can see under the "Custom Configuration Locking Behavior" section in the Record Locking Cheat Sheet, the insertion of any Detail comes with a high risk of creating a lock contention on its Master record. – Brent Bow Mar 15 '17 at 4:28
  • Can you relate the Opp_Revenue__c children to the grandparent's child accounts, and edit the VF page to sum the child accounts' Opp_Revenue__c amounts on the fly, rather than persisting this figure in the grandparent record? If not, it seems that the grandparent's revenue total might have to be calculated in a separate asynchronous process (e.g. with a periodic batch) in order to avoid trigger-context lock contentions. Thoughts? – Brent Bow Mar 15 '17 at 4:32
  • I meant to have the revenue records created below the newly created "sub"parents. So, current situation; A has 600 children, each of which generates a bunch of revenues which are direct children of A. A will get an overview of all of his own child records (the PDF), this is a separate process. Proposed alternative; A has 10 children B1 - B10, each of which has 60 children. Each of the 60 children generates a bunch of revenues for their direct parent (B1 - B10). A gets an overview of all his children's revenues (still in a separate process). – rael_kid Mar 15 '17 at 6:09
  • Sounds like this should do it, @rael_kid. I misunderstood which accounts would parent the revenue records in your proposal. – Brent Bow Mar 15 '17 at 7:04
3

You absolutely can get this error by having 2 successive iterations in a batch try to update the same group of parents in a hierarchy. This is one of the worst parts about having data skew and why there is 1 parent account in your error message.

The Opportunity and Opp_Revenue__c are causing a lock row in the Account hierarchy, since there is no guarantee you batch is segmented across 2 distinct Account Trees. This could even happen within 1 batch since you are updating 2 different objects that result in the same account hierarchy locking (in practice I find this much less likely).

A hackey way that might fix this is to order by account.parent in your soql query locator. Could also reduce your batch size. These will reduce the likelihood of locks but aren't true fixes. True fix would be to segment by distinct sets of accounts.

I have never seen sharing rows cause this issue.

|improve this answer
  • 1
    Because there is only 1 relevant Account.Parent in rael_kid's scenario, the total batch scope, so ORDER BY batch chunking (the hackey way) will not work without implementing the true fix. Thoughts on why the addition of new sharing rules seems to be correlated (and perhaps the cause of) these recent batch failures? – Brent Bow Mar 14 '17 at 4:45
  • I think the issue is the calculation of the parent rollup figure. @rael_kid is there a trigger on Account to do this? If so the solution is to remove this trigger so it wont chain and lock rows when the child Accounts update. Instead, move it all into your logic when inserting the Opp_Revenue__c. This will remedy the problem because all Account Revenues will be updated in a single pass. – Matthew McKinley Mar 15 '17 at 16:05
  • Matthew, from my understanding the child accounts never get updated in @rael_kid's scenario. The Opp_Revenue__c records are direct details of the Parent account, not related through the child accounts with any sort of rollup. – Brent Bow Mar 15 '17 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.