Implication of ActiveRecord::Base.transaction on multiple tables

Question

I'm curious about how Rails with a MySQL database handles table/row locking on transactions that involve multiple tables.

I have a situation where I need to update many records at once, but I also have to update several related records. I need to make sure that all records are all updated, and if any update fails, the changes are rolled back.

I considered an ActiveRecord::Base.transaction block for this, but a coworker asked about table locking on the involved models.

Here's a super stripped down example of my code:

Change.transaction do
  Change.all.each do |change|
    new_item = Item.new
    new_trait = Trait.new
    new_trait_value = TraitValue.new
    new_trait.options << new_trait_value
    new_item.traits << new_trait
    new_trait.save
  end
end

Notes:

1. There could be upwards of 15,000 Change records
2. There are currently about 24,000 Item records
3. There are currently about 38,000 Trait records
4. There are currently about 380,000 TraitValue records
5. Initial tests show that this is a VERY long running task - measured in minutes, not seconds.

So in this scenario, are all four tables locked? Or is only the Change table locked?

If all four tables are locked, and the process takes minutes to complete, then using transactions may not be appropriate for my situation, as I don't want to prevent other users from adding/updating Item records.

Answer 1

I'm strongly recommend you activerecord-import gem or write a raw sql queries