Facing record lock issue on Parent when loading millions records

Question

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?

Answer 1

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.