UNABLE TO LOCKROW when inserting contacts on a single account through batch apex

Question

I'm trying to create bulk contacts on a single account. I followed the batch apex approach since I will be inserting over 1000 contacts on a single account at once. I triggered the batch apex from after insert of a staging object and perform the operations. Since the batches will be running in parallel mode only one batch is getting executed and the rest of the 4 batches in this case(1000 records - 5 batches) are unable to insert the records with this error message. The Salesforce documentation specifies in case of bulk API we need to set the operations in series mode but I am unable to set it for batch apex. Is there any way that I can do to insert these contacts in series in batch apex context.

I set the batch size going into the trigger to 1000 just to check if it works but it didn't help.

Pseudo code

 trigger on staging object (after insert)
 { 
    database.execute(new batchapex(trigger.new)); 
 } 

batch class execute method 
{ 
list<contact> clist = new list<contact>(); 

for(staging object s: scope) 
{ 
contact c = new contact(); 
c.email = s.email; 
c.accountid=s.accountid; clist.add(c);

} 

insert clist; 

} 

I believe apex is splitting the records of 200 on calling batch apex. And 4 instances of batch apex are getting created if I try to insert 800 records. If the batch apex does not run in parallel mode why do 4 instances get created?

Answer 1

Batches are processed in parallel mode by default in Bulk API. This enables faster loading of data. However, sometimes parallel processing can cause lock contention on records. The alternative is to process using serial mode. The same can happen with Batch Apex as well. You can ensure your batch jobs are run in serial order when you encounter row lock issues with Batch Apex. This can be achieved using chaining of batch jobs

"you can start another batch job from an existing batch job to chain jobs together. Chain a batch job to start a job after another one finishes and when your job requires batch processing, such as when processing large data volumes. Otherwise, if batch processing isn’t needed, consider using Queueable Apex."

Another solution to this problem based on this answer is to add exclusion logic to your batch processes, so that they don't hit the same records in the same batch. One batch process could include in its query, for example, criteria to exclude records that are going to be updated by the other batch process that day, provided you can ensure that it will pick up the records on the following day's run.

Here are some great resources that will help determine exactly which records are being locked and how to handle row locks in general

Record Locking Cheat Sheet

Maximizing Parallelism and Throughput Performance

Managing Lookup Skew in Salesforce to Avoid Record Lock Exceptions

Note that you can also reach out to support and have them run the Row Lock Dashboard/Enable Row Lock diagnostics if you have issues identifying the root cause and the fix

Answer 2

You are running five batch jobs, each of which contains one batch. Each call to Database.executeBatch() creates a batch job, one per trigger invocation. If you insert 1,000 records in a transaction, you will have five trigger invocations of 200 records each, yielding five batch jobs.

These batch jobs run in parallel with one another. Given that you seem to be inserting 1,000 Contacts on the same Account, I don't think it's very surprising that the parallel jobs are row locking with one another.

If you need to insert this many records into your staging object in a transaction, you should adopt a more scalable batch processing approach. The Apex flex queue can only hold 100 jobs, so stacking up separate batches is not a sustainable strategy even if you were to solve the problem with row locks.

You might consider running a single batch job on a schedule to pick up all staging objects not yet processed and convert them, which will ensure you don't run in parallel and also be more efficient with your flex queue. This would be a quick and relatively easy change to what you've already built; you'd simply need a field on each staging object you can query against to determine if it has been processed. Alternately, you might try a solution similar to that developed by Dan Appleman in his Advanced Apex Programming that uses Queueable chains instead of a batch class.

Note that the Bulk API and Batch Apex are completely separate. The Serial Mode/Parallel Mode distinction present in the Bulk API does not apply to Batch Apex, although you must always think about issues of parallelism any time you work in Asynchronous Apex.