Nested SQL transactions in Active Record – risks?

I am in a situation in which I am forced to use nested transactions:

I have several methods that save a bunch data, each of them needs to be wrapped in a SQL transaction, and they also have to call each other from time to time. So, necessarily, it happens that method_a, with its own transaction, calls method_b that in turn is wrapped in a transaction.

I tried to configure the methods, so that they could be called with or without transaction depending on the context, but turned out too complicated to maintain.

So, the big question is: how risky is it to leave it this way?

The most important thing is that, if any exception is raised anywhere in one of the methods, all the transactions are reverted up to the most external one.

I read that, if you explicitly raise a rollback exception, it behaves unexpectedly, like in this example

User.transaction do
  User.create(username: 'Kotori')
  User.transaction do
    User.create(username: 'Nemu')
    raise ActiveRecord::Rollback
  end
end

However, in my code, we never raise a rollback, so the transactions are there mainly in case of failures while saving into SQL (like if a constraint fails, etc). I have tested extensively, and everything is always reverted back to the most external transaction.

Is it safe to assume that this will always be the case, as far as I don’t use ActiveRecord::Rollback?

What about when the Rails server times out? What type of exception is raised in that case? Will all the open transactions be reverted automatically?

You need to sign in to view this answers