What is a database transaction?

A database transaction is a mechanism by which you can perform several queries atomically. Said differently, a database transaction wraps multiple SQL commands and makes sure either all succeed or nothing changes. During so, it also prevents any other connection to the database from performing any write so that your database state cannot be corrupted while it executes the transaction.

How to perform one in SQL

In SQL, it comes down to wrapping your SQL queries with the BEGIN and COMMIT commands:

BEGIN;
INSERT INTO ...;
INSERT INTO ...;
INSERT INTO ...;
COMMIT;

How to perform one with ActiveRecord

With ActiveRecord, there are multiple ways to do so. For now, you can consider that they all work the same way:

ActiveRecord::Base.transaction do
  # ...
end

class User < ApplicationRecord
end

User.transaction do
  # ...
end

class User < ApplicationRecord
end

user = User.first
user.transaction do
  # ...
end

Does ActiveRecord sometimes use transactions without you explicitly asking?

Yes, often. For instance, each time you call one of the following methods on a record:

  • destroy
  • save
  • save!
  • touch
  • update
  • update!
  • with_lock

Or when you call the following class methods on a model:

  • create_or_find_by
  • create_or_find_by!

This list is not exhaustive. When you need to be sure, you can take a look at ActiveRecord's source code.

What if my models are backed by different databases?

A transaction happens within a single connection for a single database. Although you can't have a transaction covering multiple databases, you can have one per database.

class Book < ApplicationRecord
  connects_to database: { writing: :books, reading: :books_replica }
end

class Order < ApplicationRecord
  connects_to database: { writing: :orders, reading: :orders_replica }
end

Book.transaction do
  Order.transaction do
    # Dummy example that does not handle errors properly ;)
  end
end

Can I have nested transactions?

Yes... and no. ActiveRecord will allow you to perform nested transactions, but your database probably won't. To understand this better, let's talk SQL for a bit.

Nested transactions in SQL

Most databases (as far as I know) do not allow nested transactions but they do provide a way to reach the same goal: savepoints.

How NOT to write nested transactions

This would not be allowed:

BEGIN;
  INSERT INTO table VALUES (1);
  BEGIN;
    INSERT INTO table VALUES (2);
  COMMIT;
  INSERT INTO table VALUES (3);
COMMIT;

How to write nested transactions

You'd instead have to write this:

BEGIN;
  INSERT INTO table VALUES (1);
  SAVEPOINT my_nested_transaction;
    INSERT INTO table VALUES (2);
  RELEASE SAVEPOINT my_nested_transaction;
  INSERT INTO table VALUES (3);
COMMIT;

How to rollback a transaction

As you already know, a transaction is started with the BEGIN command and changes get persisted when the COMMIT command is executed. When you want to discard them instead, you execute the ROLLBACK command instead of COMMIT.

BEGIN;
  INSERT INTO table VALUES (1);
  INSERT INTO table VALUES (2);
  INSERT INTO table VALUES (3);
ROLLBACK;

With the above example, none of the 3 inserts is committed. This would result in no change in the database.

How to rollback a nested transaction

It's not much harder: Instead of rolling back the whole transaction, you rollback to a specific savepoint. It cancels all changes made since that savepoint.

BEGIN;
  INSERT INTO table VALUES (1);
  SAVEPOINT my_nested_transaction;
    INSERT INTO table VALUES (2);
  ROLLBACK TO my_nested_transaction;
  INSERT INTO table VALUES (3);
COMMIT;

With the above example, the values 1 and 3 are inserted but not the value 2.

Reminder of some SQL commands

  • BEGIN: Starts a transaction

  • COMMIT: Commits the current transaction (actually persists the changes)

  • ROLLBACK: Discards all changes made during the current transaction

  • SAVEPOINT savepoint_name: Creates a state to which you can rollback using ROLLBACK TO SAVEPOINT

  • ROLLBACK TO SAVEPOINT savepoint_name: Discards all changes made since a savepoint

  • RELEASE SAVEPOINT savepoint_name: Destroys a savepoint. It does not discard the changes made since nor does it commit them, it just destroys the savepoint, which means you simply cannot rollback to it anymore.

As always, I strongly advise you to read the documentation of your database to get a better understanding of how it works. Click here for PostgreSQL 15's SQL commands.

A word about rolling back

There's just one thing that still bothers me. I can't quite figure out why one would decide to write a transaction where they make changes and savepoints and then roll them back. Why not just avoid writing the unwanted changes in the first place?

Excellent question! I asked myself the exact same thing when I first read things like this:

BEGIN;
  INSERT INTO table VALUES (1);
  SAVEPOINT my_nested_transaction;
    INSERT INTO table VALUES (2);
  ROLLBACK TO my_nested_transaction;
  INSERT INTO table VALUES (3);
COMMIT;

Why not just write the following?

BEGIN;
  INSERT INTO table VALUES (1);
  INSERT INTO table VALUES (3);
COMMIT;

Well, for a reason that's really simple: we rarely generate the full SQL query all at once before sending it to the database server. We execute SQL queries one by one and the result of each query determines what we do next.

Let's use this example to make this more concrete:

ApplicationRecord.transaction do
  buyer = User.find(123)
  product = Project.find(789)
  order = buyer.orders.create(product: product, state: :unpaid)
  buyer.balance.debit!(amount: product.price, order: order)
  product.seller.balance.credit!(amount: product.price, order: order)
  order.update!(state: :paid)
end

Each step of the way, SQL queries need to be performed in order to decide what to do next.

  • We need to actually debit the buyer's balance before we can credit the product seller's balance.
  • We need to actually credit the product seller's balance before we can mark the order as paid.
  • We need to actually have debited the buyer's balance, credited the seller's balance, and marked the order as paid before we can decide whether we want to persist the changes or discard them.

Each ruby instruction above results in (at least) one SQL query sent to the database. We send the COMMIT or ROLLBACK statement only when we reach the very last line of the code, when we finally know what we want to do.

Nested transactions with ActiveRecord

This is where things start to get tricky. ActiveRecord has a strange behavior that is documented yet not intuitive. Before going any further, I advise you NOT to use nested transactions unless you really need them and you're SURE you understand how they behave.

Okay, so how can we perform nested transactions with ActiveRecord? The first thought that comes to mind is this:

Book.transaction do
  Book.create(title: 'The Pillars of the Earth')
  Book.transaction do
    Book.create(title: 'Perfume: The Story of a Murderer')
  end
end

It works. Not as you might expect it to, but it works.

In this example, ActiveRecord lies. It presumes that it is creating Perfume: The Story of a Murderer in a nested transaction, but it's not. In the above example, ActiveRecord builds only one single transaction, which means that if the creation of the book Perfume: The Story of a Murderer fails, it's not the presumed nested transaction that gets rolled back alone, it's the outer transaction that gets rolled back, and thus The Pillars of the Earth does not get committed either.

Either all commands succeed and everything gets committed, or nothing changes as if because you did not really create a nested transaction.

If you know what you're doing, you can force ActiveRecord to make sure a new transaction (or a savepoint, depending on the database you use) is created. The transaction method accepts a requires_new named attribute (default value: false)

Book.transaction do
  Book.create(title: 'The Pillars of the Earth')
  Book.transaction(requires_new: true) do
    Book.create(title: 'Perfume: The Story of a Murderer')
  end
end

With this, you have an outer transaction and a nested transaction/savepoint. If a rollback is raised within the nested transaction block, the creation of Perfume: The Story of a Murderer gets canceled, but not the creation of The Pillars of the Earth.

Book.transaction do
  Book.create(title: 'The Pillars of the Earth')
  Book.transaction(requires_new: true) do
    Book.create(title: 'Perfume: The Story of a Murderer')
    raise ActiveRecord::Rollback
  end
end

# 'The Pillars of the Earth' is created,
# 'Perfume: The Story of a Murderer' is not.

Now that you have a good understanding of how transactions and savepoints work in SQL, and how ActiveRecord lets you make them, let's see how to handle errors.

Errors management

Rolling back database changes does not cancel everything else!

When a transaction is rolled back, no change in the concerned database gets persisted. However, if you scheduled some async job during your transaction, if you made non-idempotent API calls, if you changed something on your filesystem or on another database (redis, ...), etc., these things do NOT get canceled. The same goes for the dirtiness of your records instances: the changes in the database get canceled, but the records' attributes that may have been modified during the transaction (in memory) do not get "restored" to their original state.

Make sure your error management logic takes this into account.

What triggers a rollback?

Any exception that is raised within the transaction block. Here are some examples:

  • Something wrong happens at the database level.
    • Examples: violation of a unique constraint, breaking data integrity by using an invalid foreign key, etc.
    • Exception raised: ActiveRecord::StatementInvalid
  • Something wrong happens at the ActiveRecord model level
    • Examples: calling .find with an inexistent id, calling #save! on a record with failing validations, ...
    • Exception raised: ActiveRecord::RecordNotFound, ActiveRecord::RecordInvalid, ...
  • You deciding, for whatever reason, to manually trigger a rollback
    • Exception to raise: ActiveRecord::Rollback
  • Some error happens within the transaction block even though it has nothing to do with your database nor with the transaction
    • Example: reading a file that does not exist
    • Exception raised: Errno::ENOENT

What happens when a transaction fails?

Basically, any exception that's not caught within a transaction block will result in:

  1. An ActiveRecord::Rollback being raised within the transaction block.
  2. The original exception, if any, being propagated by the transaction block itself.

How to detect that a rollback happened outside of the transaction block?

It depends on what caused the rollback.

If it was triggered by an exception other than ActiveRecord::Rollback, then it's simple. As said previously, the transaction will propagate this exception outside and you can use the rescue keyword to handle it.

If it was triggered because you manually raised ActiveRecord::Rollback, then nothing can let you know except yourself. In this case, using a boolean flag or any similar logic can do the trick:

transaction_succeeded = false
ApplicationRecord.transaction do
  # do your thing...
  raise ActiveRecord::Rollback if valid_reason?
  transaction_succeeded = true
end

if transaction_succeeded
  # be happy
else
  # be happy nonetheless :)
end

How to handle errors in/around a failing transaction?

There isn't one unique right way of handling failing transactions. Sometimes you want to catch the exceptions within the transaction block because the logic you're implementing requires it, sometimes you want to let them rollback the transaction and then catch them from the outside. You get to decide how you handle errors but there is one thing you MUST NOT do. You must not rescue ActiveRecord::StatementInvalid.

I'll cite the documentation:

Warning: one should not catch ActiveRecord::StatementInvalid exceptions inside a transaction block. ActiveRecord::StatementInvalid exceptions indicate that an error occurred at the database level, for example when a unique constraint is violated. On some database systems, such as PostgreSQL, database errors inside a transaction cause the entire transaction to become unusable until it's restarted from the beginning.
Source: https://api.rubyonrails.org/classes/ActiveRecord/Transactions/ClassMethods.html

I'll add that rescuing from ActiveRecord::Rollback is not something I recommend. You want to handle the cause of the error, not its consequence.

Here are some more examples of ways you can handle errors within and/or around transactions:

url = "https://some.random/interesting_article.html"
article = Article.new(url: url)

ApplicationRecord.transaction do
  begin
    scrapped_article = ArticleScrapper.call(url)
    article.title = scrapped_article.title
    article.tags = scrapped_article.tags.join(", ")
    article.content = scrapped_article.content_to_text
    acticle.scrapping_status = :complete
  rescue SocketError => e
    article.scrapping_status = :unreacheable
  rescue Nokogiri::XML::SyntaxError => e
    article.content = "Caught exception: #{e}"
    article.scrapping_status = :partial
  end
  article.save!
end

if article.persisted?
  render json: article.to_json,
         status: :created
else
  head :unprocessable_entity
end

ApplicationRecord.transaction do
  buyer = User.find(123)
  product = Project.find(789)
  order = buyer.orders.create(product: product, state: :unpaid)
  begin
    ApplicationRecord.transaction(requires_new: true) do
      buyer.balance.debit!(product.price)
      product.seller.balance.credit!(product.price)
      order.update!(state: :paid)
    end
  rescue ActiveRecord::RecordInvalid
    order.update!(state: :payment_failed)
  end
end

ApplicationRecord.transaction do
  buyer = User.find(123)
  product = Project.find(789)
  order = buyer.orders.create!(product: product, state: :unpaid)
  order.reload # <= Get database-generated UUID
  buyer.balance.debit!(product.price)
  product.seller.balance.credit!(product.price)
  order.update!(state: :paid)
  remote_order = ExternalShopApi.register_unique_uuid(uuid: order.uuid)
  raise ActiveRecord::Rollback unless remote_order.registration_accepted?
end

What happens if I call return within a transaction block?

In Rails 6:

It commits the transaction and returns from the calling method. This behavior is deprecated:

DEPRECATION WARNING: Using `return`, `break` or `throw` to exit a transaction block is
deprecated without replacement. If the `throw` came from
`Timeout.timeout(duration)`, pass an exception class as a second
argument so it doesn't use `throw` to abort its block. This results
in the transaction being committed, but in the next release of Rails
it will rollback.

In Rails 7:

It rolls back the transaction and returns from the calling method.

Final word

Transactions are a pretty handy tool, but ActiveRecord's behavior can be tricky to master. Read the documentation of ActiveRecord, read the documentation of your database about transactions as well as the available SQL commands, use tools only when you need to (not just because they look cool), and you should be alright.

Oh, and by the way, you should by now have a better understanding of the difference between the after_save and after_commit callbacks in your ActiveRecord models ;)

Thank you for reading!
Younes SERRAJ