Rollback entire transaction within nested transaction

Question

I want a nested transaction to fail the parent transaction.

Lets say I have the following model

class Task < ApplicationRecord
  def change_status(status, performed_by)
    ActiveRecord::Base.transaction do
      update!(status: status)
      task_log.create!(status: status, performed_by: performed_by)
    end
  end
end

I always want the update and task_log creation to be a transaction that performs together, or not at all.

And lets say if I have a controller that allows me to update multiple tasks

class TaskController < ApplicationController
  def close_tasks
    tasks = Task.where(id: params[:_json])

    ActiveRecord::Base.transaction do
      tasks.find_each do |t|
        t.change_status(:close, current_user)
      end
    end
  end
end

I want it so that if any of the change_status fails, that the entire request gets rolled back, from the Parent level transaction.

However, this isn't the expected behavior in Rails, referring to the documentation on Nested Transactions

They give two examples.

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

Which will create both Users "Kotori" and "Nemu", since the Parent never see's the raise

Then the following example:

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

Which only creates only "Kotori", because only the nested transaction failed.

So how can I make Rails understand if there is a failure in a Nested Transaction, to fail the Parent Transaction. Continuing from the example above, I want it so that neither "Kotori" and "Nemu" are created.

Wormhole

Answer 1

You can make sure the transactions are not joinable

User.transaction(joinable:false) do 
  User.create(username: 'Kotori')
  User.transaction(requires_new: true, joinable: false) do 
    User.create(username: 'Nemu') and raise ActiveRecord::Rollback
  end 
end 

This will result in something akin to:

SQL (12.3ms)  SAVE TRANSACTION active_record_1
SQL (11.7ms)  SAVE TRANSACTION active_record_2
SQL (11.1ms)  ROLLBACK TRANSACTION active_record_2
SQL (13.6ms)  SAVE TRANSACTION active_record_2
SQL (10.7ms)  SAVE TRANSACTION active_record_3
SQL (11.2ms)  ROLLBACK TRANSACTION active_record_3
SQL (11.7ms)  ROLLBACK TRANSACTION active_record_2 

Where as your current example results in

SQL (12.3ms)  SAVE TRANSACTION active_record_1
SQL (13.9ms)  SAVE TRANSACTION active_record_2
SQL (28.8ms)  ROLLBACK TRANSACTION active_record_2

While requires_new: true creates a "new" transaction (generally via a save point) the rollback only applies to that transaction. When that transaction rolls back it simply discards the transaction and utilizes the save point.

By using requires_new: true, joinable: false rails will create save points for these new transactions to emulate the concept of a true nested transaction and when the roll back is called it will rollback all the transactions.

You can think of it this way:

• requires_new: true keeps this transaction from joining its parent
• joinable: false means the parent transaction cannot be joined by its children

When using both you can ensure that any transaction is never discarded and that ROLLBACK anywhere will result in ROLLBACK everywhere.

Zacatecas

This is super interesting, I'll need to test it out. Is joinable documented anywhere? – Wormhole

It is inferred more than documented https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/DatabaseStatements.html#method-i-transaction if you look at the source you can see that if the current transaction is not joinable the statement will be executed in a new transaction. – Zacatecas

Tested this solution in rails 5.2 but user in the outer transaction (Kotori) is still created. – Carriecarrier

@GerardoS are you sure? Did you construct it exactly as shown? – Zacatecas

@Zacatecas I did, and actually digged into the source code to understand it. I updated my answer with the findings. – Carriecarrier

@GerardoS I think I ran the above against sqlserver adapter. Maybe this works because Sqlserver supports true transactions. What adapter did you use? – Zacatecas

@Zacatecas I tried it with postgres, but yeah, I think it may work different with any external adapter, specially sqlserver because of the native nested transactions. – Carriecarrier

Ya, just tested this with Postgres, and it doesn't work – Wormhole

Answer 2

It seems that rails docs are not straightforward or easy to understand in this section.

Transactions are meant to silently fail if an ActiveRecord::Rollback is raised inside the block, but if any other error is raised, the transactions will issue a rollback and the exception will be passed on.

Looking into the rails docs first example:

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

An ActiveRecord::Rollback is being raised in the inner transaction, and therefore it should avoid the creation of the users, but as all database statements in the nested transaction block become part of the parent transaction and the ActiveRecord::Rollback exception in the nested block does not carry up a ROLLBACK action to the parent transaction, both users are created. As I wrote before, ActiveRecord::Rollback exceptions will be intentionally rescued and swallowed without any consequences, and the parent transaction won't detect the exception.

If we take the same example, but we raise a different exception:

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

This will work as expected. The transactions are nested and joined correctly in only one connection (this is default behaviour), therefore, Nemu and Kotori won't be created. It also doesn't matter where the error is raised, if it is raised in the parent or child transactions it will still rollback all statements.

We can also achieve a different result by creating real sub-transaction by passing requires_new: true to the inner transaction.

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

This would treat each transaction separately and if an exception is raised in the inner transaction the database rolls back to the beginning of the sub-transaction without rolling back the parent transaction. Therefore the example above would only create the Kotori user.

The documentation gives just a bit of information about the 2 options that we can pass to the transaction method: joinable and requires_new.

This options help us create real sub-transactions and treat nested transactions as individual database connections, which therefore helps us avoid dependancy between parent-child transactions when intentionally raising an ActiveRecord::Rollback exception. Each option is intended to be used depending on the nested hierarchy level of the transaction.

joinable: default true. Allows us to tell the outer transaction if we want the inner transaction to be joined within the same connection. If this value is set to false and the inner transaction raises a rollback exception it wont affect the outer transaction.

requires_new: default nil. Allows us to tell the inner transaction if we want it run in a different connection. If this value is set to true and a rollback exception is raised, it wont affect the parent transaction.

So, this two options are meant to be used to run transactions in individual database connections depending on the nested hierarchy you can control.

Unfortunately not all database support real nested-transactions but Active Record emulates nested transactions by using savepoints, so the behaviour should be the same at least with postgres and MS-SQL.

Extra: it seems that the rails team is already working towards updating this functionality https://github.com/rails/rails/pull/44518

Carriecarrier

How often does Rails internal code raise ActiveRecord::Rollback? I guess I'm less worried about my code doing it, and more so something that Rails does when it hits an error – Wormhole

It's very rare, but rails internal code shouldn't be a concern. If rails raises this error, it also handles it properly. There shouldn't exist cases were rails source code uses this exception and code fails silently. – Carriecarrier

@GerardoS In your first example code, User.create(username: 'Nemu') actually still creates Nemu and the inner transaction will not be rollbacked, as also described in Rails docs: api.rubyonrails.org/classes/ActiveRecord/Transactions/… ... I quote "Reason is the ActiveRecord::Rollback exception in the nested block does not issue a ROLLBACK. Since these exceptions are captured in transaction blocks, the parent block does not see it and the real transaction is committed." – Nittygritty

Hey @Jay-ArPolidario thanks for your correction! I updated the answer with the correct behaviour. – Carriecarrier