Understanding Database Transactions in Rails

This article was originally written by Kingsley Silas on the Honeybadger Developer Blog.

When you're in charge of a Rails app, there are a couple of areas where you really don't want to have problems:

• Data Integrity - Does the data in your database make sense?
• Database Performance - Do your queries return in a reasonable amount of time?

Database transactions (and their ActiveRecord counterparts) are powerful tools for avoiding these problems. Properly used, they can speed queries and ensure data integrity.

This article will introduce you to the concept of database transactions. It will also show you how to use them in Rails; but just as importantly, it will help you decide when to use them.

One cool thing about transactions is that they're actually implemented by the database. You can use them anywhere you use Postgres/MySQL/etc. Even if you're not a Ruby/Rails developer, the techniques you learn here will come in handy.

What Is A Transaction?

Transactions are typically used when you need to ensure data integrity, even if your web app crashes in the middle of a request.

From the Rails API docs

Transactions are protective blocks where SQL statements are only permanent if they can all succeed as one atomic action. The classic example is a transfer between two accounts where you can only have a deposit if the withdrawal succeeded and vice versa. Transactions enforce the integrity of the database and guard the data against program errors or database break-downs. Basically, you should use transaction blocks whenever you have a number of statements that must be executed together, or not at all.

A Classic Example

To illustrate why transactions are useful, let's consider a possible scenario.

Imagine that we are building a banking system that lets people transfer money. When John Doe (in Chicago) sends \$200 to Jane Doe (in London), we perform several DB operations.

1. Check that John Doe has \$200 in his account.
2. Debit the total amount from his account.
3. Convert \$200 to Jane Doe's preferred currency, which is pounds.
4. Credit Jane Doe's account.

An expensive problem

Implemented naively, our code might look like this:

sender.debit_account(amount) if sender.sufficient_balance(amount)
credit_amount = convert_currency(amount, recipient)
perform_transfer(recipient, credit_amount, sender)

What happens if our program crashes during the currency conversion? It's a crucial spot:

• After money has been taken out of John's account
• ...and before it's added to Jane's?

The money vanishes into thin air.

We need some way to ensure that errors along the way cause the database to roll back to avoid committing incomplete data. Either all of the database actions are performed, or none of them are.

Wrapping the above queries in a database transaction is your best bet for achieving data integrity.

In Rails, it might look like this:

Transfer.transaction do
  sender.debit_account(amount) if sender.sufficient_balance(amount)
  credit_amount = convert_currency(amount, recipient)
  perform_transfer(recipient, credit_amount, sender)
end

Now, even though we might be creating and updating records, the supposed changes are not reflected in the database until the transaction is completed successfully.

States of Database Transaction

Transactions have a well-defined life cycle. At any point in time, your transaction will be in a specific state.

Source: https://www.tutorialspoint.com

• Active: Our DB operations are being executed.
• Partially Committed: Our DB operations have completed successfully, but changes have not been committed to the database, and cannot be accessed outside of the transaction.
• Committed: Our DB operations have completed successfully and saved any changes to the DB.
• Failed: Some error has occurred, which caused the DB to stop the transaction. The DB has not been rolled back at this point.
• Aborted: The DB has been rolled back after a failure, and the transaction is done.

Transactions in ActiveRecord

We previously saw that our bank transfer code looked like this:

ActiveRecord::Base.transaction do
  sender.debit_account(amount) if sender.sufficient_balance(amount)
  credit_amount = convert_currency(amount, recipient)
  perform_transfer(recipient, credit_amount, sender)
  transfer.update_status
end

We're calling the transaction method on the ActiveRecord::Base class and passing it a block. Every database operation that happens inside that block will be sent to the database as a transaction.

If any kind of unhandled error happens inside the block, the transaction will be aborted, and no changes will be made to the DB.

ActiveRecord::Base#transaction

In the above example, I am calling the transaction method on the ActiveRecord::Base class. You might find yourself doing this in controller or service code.

Every ActiveRecord model has a transaction method. Imagine that you have a Transfer class that inherits from ActiveRecord. The following works:

Transfer.transaction do
  ...
end

my_model_instance#transaction

Every instance of your ActiveRecord models, also has its own transaction method.

transfer = Transfer.new(...)
transfer.transaction do
  ...
end

And, because the transaction method is an ordinary Ruby method, we can reference it in our model definitions:

class Transfer < ApplicationRecord
  def perform(...)
    self.transaction do
      ...
    end
  end
end

How to Abort Transactions

To manually abort a transaction and prevent any of its changes from being written to the DB, you can use the ActiveRecord::Rollback method.

ActiveRecord::Base.transaction do
  @new_user = User.create!(user_params)
  @referrer = User.find(params[:referrer_id])
  raise ActiveRecord::Rollback if @referrer.nil?
  @referrer.update!(params[:reference_record])
end

Exceptions During Transactions

Any unhandled exception that occurs during the transaction will also cause it to be aborted. There are two common ways to raise these exceptions:

• Using ActiveRecord methods ending with an exclamation-mark: save!, update!, destroy! etc.
• Manually raising an exception

In ActiveRecord, when a method name ends with an exclamation-mark (also called a "bang"), it will raise an exception on failure.

Let's say we have a transaction that involves creating a new user account, while also updating the record of another user (the referrer):

ActiveRecord::Base.transaction do
  @new_user = User.create!(user_params)
  @referrer.update!(params[:reference_record])
end

The create! and update! methods will raise an exception if something goes wrong.

If we were to use the create and update methods (without the exclamation mark), they would indicate a failure via their return value, and the transaction would keep running.

Of course, we could always check the return value ourselves and "manually" raise an exception if we wanted to:

ActiveRecord::Base.transaction do
  @new_user = User.create(user_params)
  raise ActiveRecord::RecordInvalid unless @new_user.persisted?
  ...
end

It doesn't matter what kind of exception you raise. Any exception class will abort the transaction.

Don't forget to rescue the exception if you need to.

def create_referrer_account
  ActiveRecord::Base.transaction do
    ...
    raise ActiveRecord::RecordInvalid if @referrer.nil?
  rescue ActiveRecord::RecordInvalid => exception # handle error here...
  end
end

Nested Transactions

The transactions we've seen so far only allow you to work with a single database. Most Rails apps only use one database, so that works out nicely.

If you need to ensure data integrity across multiple databases, you can do so by nesting ActiveRecord transactions.

In the example below, imagine that the User and Referrer models point to different databases.

User.transaction do
  @new_user = User.create!(user_params)
  Referrer.transaction do
    @referrer.update!(params[:reference_record])
  end
end

If any parts of the inner transaction fail, it will cause the outer transaction to be aborted.

Beware, though, that nested transactions can be tricky to get right. It's best to avoid them if you can.

Transaction Tradeoffs

In programming, as in life, very few things are free. Transactions give us an excellent way to ensure data integrity, but they have a few possible drawbacks:

• Performance - Using a transaction will consume more resources on the DB server than the raw queries.
• Complexity - When overused, transactions can make your code more brittle and harder to understand.

For example, when you use a transaction in Rails, it ties up one of your DB connections until all the code in your transaction block finishes running. If the block contains something slow, like an API call, you could tie up a DB connection for an unreasonable amount of time.

ActiveRecord::Base.transaction do
  User.create!(user_params)
  SomeAPI.do_something(u)
end

The key to using transactions well is to use them only when you really need them.

Conclusion

With great power comes great responsibility.

Transactions give developers the power to write SQL statements in the right way. This power has a great responsibility attached to it - one that should not be abused by sprinkling transactions everywhere.

In addition to what we have learned, here is a checklist I came with for when I use transactions:

1. Do I need to handle more than one SQL statement?
2. Do I need the SQL statement to succeed together?

While you make use of transactions, ensure you're raising and rescuing the errors as we saw above.