Updating record from Ruby on Rails to Salesforce

Question

I'm using a Ruby gem that utilizes the Salesforce Bulk API and a few sidekiq workers to update records within Salesforce and am running into a

UNABLE_TO_LOCK_ROW error.

The issue stems from the fact that I'm spinning up sidekiq jobs of 200 records each and then sending those to Salesforce concurrently. Since Salesforce doesn't know the extent of the update, they sometimes collide.

When I try to increase the batch size from 200 to 1,000 or 10,000, the URI becomes too large for the HTTP request

(Databasedotcom::SalesForceError: Bad Message 414 reason: URI Too Long)

Has anyone implemented something similar with success?

The code is below:

def self.import_all_customers(batch_size: 200)
    customer_ids.each_slice(batch_size) do |ids_batch|
      Worker.perform_async(ids_batch)
    end
  end

Worker:

Salesforce.bulk_client.update("Object__c", objects_to_create) unless objects_to_create.empty?

Answer 1

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:

You call

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)
end

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

The 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.

Answer 2

I had been in a similar situation very recently and was able to resolve it by following some of the guidance as provided in the help documentation which is referenced here.

---

OVERVIEW

UNABLE_TO_LOCK_ROW is caused when Salesforce services place a lock on a record, whenever a record is being updated or created. This is to prevent any other process/operation to cause any conflicting updates on the same record.

When a record is being updated or created, we place a lock on that record to prevent another operation from updating the record at the same time and causing inconsistencies on the data.

These locks normally last for a few seconds and when the lock is released, other operations can do whatever processing they are supposed to do on the record in question. However, a given transaction can only wait a maximum of 10 seconds for a lock to be released, otherwise it will time out.

There is very little you can do here to fix anything in your code, but only identify what has been causing this issue and then try alternate approaches. As you have mentioned that you are using Bulk API and that is executes in parallel, that is most likely to cause this issue. Excerpt from the same documentation link:

Inserting or updating records through the Bulk API can cause multiple updates on the same parent record at once, because the batches are processed in parallel.

---

ISSUE SOURCE

From comments:

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 ..

You do have a high chances of getting into this scenarios based on the Focus and Risk of Lock Contention area as found on this cheat-sheet (a screenshot above).

---

PATH TO RESOLUTION

The simplest way to resolve this is to load the records in serial mode instead of parallel OR group such records which can cause locking in separate batches.

To prevent this, you can do either of the following:

• Reduce the batch size
• Process the records in Serial mode instead of parallel, that way on batch is processed at a time.
• 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.

The same documentation has a link to this article, where you can find more on this issue and how to effectively manage this.

---

REFERENCES

1. Unable to lock row
2. Managing Locks to Maximize the Benefits of Parallelism
3. Force.com Record Locking Cheatsheet
4. Record Locking Cheat Sheet