Bulk API Interface
The way you're using the Bulk API is going to cause some species of problem, and it's likely that it's exacerbating issues with lock contention that might otherwise be occasional at worst.
The mode of operation of the Bulk API is that you open a job, submit (usually quite large) batches of records against that job, and then kick it off. The Bulk API then goes off and does its thing, in parallel or serial mode as configured.
What you're doing is reimplementing batching in Ruby before the point where you create the Bulk API job. So rather than running a single job with multiple batches of 200 records (in either serial or parallel mode), you're actually running many jobs, each with a single batch of 200 records. Setting serial or parallel mode won't do anything at all for you here because your batches aren't in the same job. Additionally, you're unnecessarily burning through your 10,000 job limit on use of the Bulk API.
If you dig into the gem, what's happening is this:
Salesforce.bulk_client.update("Object__c", objects_to_create) unless objects_to_create.empty?
which executes this method
def update(sobject, records, get_response = false, send_nulls = false, no_null_list = , batch_size = 10000, timeout = 1500)
do_operation('update', sobject, records, nil, get_response, timeout, batch_size, send_nulls, no_null_list)
Note the defaulted
batch_size parameter, which is the actual Bulk API batch size being used here. You're actually passing 200 records into a 10,000 record Bulk API batch!
do_operation implements the open job/add batches/close job sequence:
job.create_job(batch_size, send_nulls, no_null_list)
operation == "query" ? job.add_query() : job.add_batches()
response = job.close_job
add_batches method splits the list of
records you passed to
update into segments
batch_size long and submits each segment as a batch to the Bulk API.
Since this whole sequence gets executed by each of your async worker processes for each 200 record batch... bad things happen, and you get a ton of extra lock contention from running tiny batches in parallel.
So as upshot, what I'd recommend you do is rather than trying to implement batching yourself, just pass the full list of records to update to the
update method on your Bulk API gem. You can optionally pass it a
batch_size parameter (it defaults to 10,000) to further tune if you need to, but start out with the default and see how it goes.
It's not clear why you're receiving "URI too long" errors when you change your Ruby batch size. The Bulk API does not accept input records in the URI, so this should not be affected by your actual Bulk API batch size as set by the gem.
I recommend you refactor this code first and try for a more idiomatic usage of the Bulk API before undertaking more aggressive interventions to address lock contention, although as described below you have some inherent issues in your data model that make lock contention significantly more likely.
Data Loading and Record Lock Contention
I think Jayant's answer is the right one on this subject; I just wanted to expand a little based on your comment because it sheds some really important light on why you're getting lock contention:
... we're updating a lot of child records (Apps) that have a Parent account. Many of these will share the same generic parent account. There are some roll ups and triggers but nothing over the top.
Use of a generic parent account suggests you probably have a parent-child data skew situation. Data skew doesn't cause lock contention as such, but it can dramatically increase its likelihood by multiplying and concentrating the situations where a small number of specific records (the generic parents) must be locked.
Even if the data volume on that single generic account is not above the standard data-skew threshold of 10,000, you have a situation where child updates must lock the parent account: the presence of roll-up summary fields.
The Record Locking Cheat Sheet is extremely helpful in identifying such cases. It's a PDF; on the second page you'll see
Record with a roll-up summary field | Locks: Master record(s) | Risk of Lock Contention: High
Basically, it sounds like you're combining three things that are high-risk for lock contention (parent-child data skew, large volume data loads, and a data model with a predilection for parent record locks) and you got it in spades.
There's a number of strategies to attack this problem from different angles.
- Try running the Bulk API in serial mode, as Jayant suggests. This fixes the lock contention by simply eliminating parallelism.
- Sequence the input records by parent Id. If a very large percentage of the child records have the same generic parent (more than your batch size), this may not eliminate the lock contention, though. It's dependent on the distribution pattern of parent ids through your input data set.
- If your generic-parent records are a huge percentage of your data side, you might try pulling those records out and running them in a separate job, in serial mode, while running a single job in parallel mode for the other records.
- Reduce the data skew by distributing child records across multiple generic parents in a sort of round-robin pattern.
- Make sure nothing else in your database is touching these records or the generic parents during the load.