Understanding ActiveRecord Nested Transactions

As it reads in rails documentation -transactions are protective blocks where SQL statements are only permanent if they can all succeed as one atomic action.- You can take advantage of this behaviour to ensure that a number of statements are executed altogether or not at all.

There is plenty of resources that talk about Transactions, but this one focuses only where it seems to lack clarification: nested transactions. The official documentation on this topic can be confusing and because of that, here are some insights to fully understand how nested transactions behave and how to use them.

What are nested transactions?

Taking the simplest definition found online: - A nested transaction is used to provide a transactional guarantee for a subset of operations performed within the scope of a bigger transaction. Doing this allows you to commit and abort the subset of operations independently of the larger transaction. -

Most databases don't support true nested transactions and the only database that supports them is MS-SQL.

In order to get around this problem, Rails transaction will emulate the effect of nested transactions, by using savepoints

When are nested transaction required or used?

Nested transactions can exist when we have models pointing to different databases. Each transaction evaluates and can create a single database connection, and rails does not distribute transactions across database connections, therefore, it is best if we nest transaction to imply the connection to different databases.

Another example that I encounter the most is when we are working with external libraries or gems that process specific code inside a transaction which we can not fully control and in consequence we wrap around them with another transaction.

More on distributed transactions

Quoting from rails source code: - A transaction acts on a single database connection. If you have multiple class-specific databases, the transaction will not protect interaction among them. One workaround is to begin a transaction on each class whose models you alter: -

 # https://github.com/rails/rails/blob/main/activerecord/lib/active_record/transactions.rb#L62

  Student.transaction do
    Course.transaction do
      course.enroll(student)
      student.units += course.units
    end
  end

From the rails source code: - This is a poor solution, but fully distributed transactions are beyond the scope of Active Record. -

How nested transactions behave?

Nested transactions are intuitive and represent the normal transaction behaviour, if one statement fails, all fail.

In detail, exceptions inside a transaction block will force a ROLLBACK that returns the database to the state before the transaction began and the exception will then be propagated to the parent transaction.

ActiveRecord::Rollback exception will trigger a database ROLLBACK when raised, but will not be re-raised by the transaction block and therefore will not be catched by the parent transaction. This exception is only meant to be used deliberately in exceptional situations.

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

Explaining the documentation examples.

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 exception 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.

Creating real nested sub-transactions

We can 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 also 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 treat nested transactions as individual database connections and therefore avoid dependancy between parent-childs transactions, also 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 an exception it wont affect the outer transaction.

User.transaction(joinable: false) do
  User.create(username: 'Kotori')
  # child transaction result wont affect the parent transaction
  User.transaction do
    User.create(username: 'Nemu')
  end
end

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

User.transaction do
  User.create(username: 'Kotori')
  # next transaction result wont affect the parent transaction
  User.transaction(requires_new: true) do
    User.create(username: 'Nemu')
  end
end

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

https://api.rubyonrails.org/classes/ActiveRecord/Transactions/ClassMethods.html
https://dev.mysql.com/doc/refman/en/savepoint.html
https://dotnettutorials.net/lesson/sql-server-savepoints-transaction/