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?
The true answer likely lies with what database you are using.
To my knowledge the only RDBMS that supports true nested transactions is MSSQL, for all the rest rails tries to emulate nested transactions using save points. Here is a SO Post I wrote a while back. I can confirm this works exactly as expected on MSSQL but others have commented that it does not work correctly for PostgreSQL.