1

An external system is pushing data into my org using bulk API. There are about 2 million records. The target object is child__c. The child__c has a lookup to parent__c. When the external system loads the data I get an error for some records saying: UNABLE_TO_LOCK_ROW:unable to obtain exclusive access to this record. I have found the root cause of this at this article - https://help.salesforce.com/articleView?id=000229525&type=1 .

Now I am looking for the solution to over come this problem. Following are three things I found:

  1. Reduce the batch size - Batch size is already 2000 for 2 million record. Will this help?
  2. Process the records in Serial mode instead of parallel, that way on batch is processed at a time. - Sure , serial mode will resolve this problem but what is the impact on the time to insert the records then. Given the size is 2 million. How long it might take if the batch size is 2000 records.
  3. Sort main records based on their parent record, to avoid having different child records (with the same parent) in different batches when using parallel mode.- This does not seem to suit my solution since the external system have to make changes to the code.

Appreciate any suggestion from the community?

  • 2
    The three things you found are correct (well they can be, depending on your data). I do bulk api uploads all the time and if you order your upload by child__c.parent__c you should be fine (depending on the amount of dependent records). – Jesse Milburn Feb 14 '17 at 19:47
4

Your suggestions are all valid in there own right, but they do become dependent on your data.

I would lean towards #3 the most, as that is what I most often do (because it works with most of my data sets). The only time #3 would be bad is if you have 10's of thousands of records with the same lookup. This also doesn't pair well with number 1.

Consider 6000 child records per parent:

If you reduce your batch size. Now you are in greater risk of lock contention issues. Because you are trying to run 3 batches of 2000 at the same time that all have the same parent. You would be better leaving your batch size at 10000 records.

Now consider, you have 10s of thousands of child records per parent:

You are back in the same boat as you were. Now you are just running much larger batches that are going to have lock contention issues. If this is your scenario, you will more than likely have to run serially.

Like I said, the resolution is completely dependent on what your data looks like.

I know you feel that #3 isn't an option because you aren't getting the data ordered. But you can easily toss that into a local sql database and query it back out sorted. Among other options.

  • Thanks for the answer. Do you how long it might take to insert 2 - 3 million record if I go with serial processing. Also the 3rd option looks good but I have group the child object in one batch so that no child is part of another batch. So the filter would be to order by the parent and group the child in one batch only and not to let it go to the next batch for parallel processing , correct ? – SfdcBat Feb 14 '17 at 20:17
  • Using bulk api it kind of depends on how busy the server is and how many records you are locking. If it is only the one lookup, I would guess 2-3 million would take around 1.5-3 hours. However, if the server is super busy you could be waiting several minutes extra for each batch to even start processing. If it isn't busy they will run practically seamlessly. There is no SLA on when bulk api batches will process after you upload them (that I am aware of). – Jesse Milburn Feb 14 '17 at 20:24
  • your filter would just be an order by clause. – Jesse Milburn Feb 14 '17 at 20:25
  • Thanks. you mean I would create batches of records based on order by? – SfdcBat Feb 14 '17 at 20:27
  • I read this- Note the “this type of lookup relationship” in the first sentence. For optional lookup fields, you can avoid the locks by setting the Clear the value of this field option, which does more than just tell Salesforce what to do if your lookup record is deleted. When you set this option, whenever a record that has a lookup field to this lookup record is inserted or updated, Salesforce doesn't lock the lookup records; instead it only validates that the lookup values exist. To avoid unnecessary locks, it’s best to set this option for your lookup fields whenever possible. – SfdcBat Feb 14 '17 at 20:57

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.