Updating Multiple Entries in a Single ActiveRecord Transaction in Rails

Question

How can I update / save multiple model instances in one snapshot using a transaction block in Rails?

I would like to update the values ​​for hundreds of records; The values ​​for each entry are different. This is not a mass update of the situation for a single attribute. Model.update_all (attr: value) is not suitable here.

MyModel.transaction do things_to_update.each do |thing| thing.score = rand(100) + rand(100) thing.save end end 

save seems to give it its own transaction, rather than delivering updates to the surrounding transaction. I want all updates to be sent in one big transaction.

How can i do this?

Answer 1

Say that you knew that you want things with identifiers 1, 2 and 3 to have points, 2, 8 and 64 (as opposed to random numbers), you could:

 UPDATE things AS t SET score = c.score FROM (values (1, 2), (2, 30), (4, 50) ) as c(id, score) where c.id = t.id; 

So, with Rails, you should use ActiveRecord::Base.connection#execute to execute a block similar to the above, but with the corresponding interpolated value string.

Answer 2

I'm not sure, but maybe you are confusing multiple transactions with multiple requests.

The code you posted will create a single transaction (for example, if an exception occurs, all updates will be discarded), but each save will result in a separate update request.

If it is possible to upgrade using SQL rather than Ruby code, this is probably the best way.

Answer 3

I think you just need to change the save method to save! If any of the updates fails, the method saves! throws an exception. When an exception occurs in a transaction block, the transaction cancels the entire operation (rollback)

 MyModel.transaction do things_to_update.each do |thing| thing.score = rand(100) + rand(100) thing.save! end