Safer SQL: Using ActiveRecord Transactions

Is there anything more satisfying than writing concise, perfect line of SQL and then watching it query your database exactly as you expected? Probably not. Writing — and subsequently watching! — an efficient database query is one of my favorite parts of building an application.

But if there’s one thing about software development that I’ve learned over the past few months, it’s this: projects can get out of control, rather quickly. You start off with your basic models, but then as you add one feature after another, things can start to get out of hand. At that point, your focus as a developer shifts and spreads out to various things. You can’t just care about how readable your code is; you also have to consider how efficient it is, and how different units of your application might be breaking other parts (hopefully not unbeknownst to you!). In other words, you have to consider how defensive and safe your code actually is.

Recently, while writing some background jobs and creating some service objects for a new feature, I realized the importance of executing and enforcing safe code. The good news is that there’s a really helpful, life-saving ActiveRecord transaction method that allows you to do exactly this. And the really great news? You get to watch your SQL queries execute safely as a result!

Database Handshakes

As our application grows, there are inevitably going to be different models that depend upon each other. To make things more complicated, those models are going to have to change as our Users take different actions, and that means that we’ll need to update different associated parts of our schema as a result. For example, let’s say that we have a background job that’s responsible for processing an Order on an User. This background job has a service class that takes care of saving an Order when it has been charged, billing the User, and adding a sale to an Vendor object, which will then be visible on the vendor’s dashboard panel/admin page.

if order.process
  user.charge
  vendor.add_sale
end

In other words, we’re basically transferring money from our User object to our Vendor objects. At first glance, this might not seem like a potentially “unsafe” set of queries, but here’s where things can get a bit tricky: imagine that our User’s credit card information is rejected, or for some reason, the charge cannot be created. In that scenario, we’d want to handle the errors and make sure that the add_sale method is not called on our Vendor object. However…we aren’t really safeguarding our code against this situation at all, are we?

But, fear not - it’s not a totally hopeless situation! In fact, we can take care of this problem pretty easily by using ActiveRecord’s transaction blocks.

The transaction method is defined in the Rails source code under the ActiveRecord::Transactions module. This method takes a block, and whenever it is invoked, the block that is passed to it will be executed inside of a database transaction. If, in the course of executing that block, an exception is raised, the database transaction will automatically be rolled back. No SQL will be executed, and no new data will be added to the database

Order.transaction do
  @order.process
  @user.charge
  @vendor.add_sale
end

So what does this mean, exactly? Well, now that we’ve wrapped the logic of our three methods inside of a transaction method, we can safely assert that in the case that any of these three methods raises an exception (in other words, fails for any reason), the entire process should fail.

The important piece of this is that we’ll never be adding or updating any data (or writing to our database) unless all of these methods are successful. It’s crucial for us to ensure that this is the case because we’d never want to call the add_sale method and write data to our vendor if the order didn’t successfully process, or if our user wasn’t successfully charged.

I like to think of these blocks as a “handshake” between your application and your database: your application and database have an understanding that one will hand off information to the other in a “transaction”, and when the deal actually goes through, they shake hands and make it official…or something more poetic. If that made no sense — or if you’re not a big fan of metaphors — here’s how the Rails documentation explains transactions:

“Transactions are protective blocks where SQL statements are only permanent if they can all succeed as one atomic action. Transactions enforce the integrity of the database and guard the data against program errors or database break-downs. So basically you should use transaction blocks whenever you have a number of statements that must be executed together or not at all.”

The Rules of Transacting

Because ActiveRecord transactions can be easily explained and simplified as a single method, it’s easy to forget the idiosyncrasies that make this method work. There are a few things to keep in mind so that we can get the most out of these blocks.

1. Opening database connections

A transaction opens up a single database connection. This means that when we call the transaction method, the method can only be invoked on the current database connection. This is important to remember if our application writes to multiple database at once; for example, if our Order and our Vendor data lived in two different databases, we’d need to nest our transactions:

Order.transaction do
  Vendor.transaction do
      order.process
      user.charge
      vendor.add_sale
  end
end

It’s generally a good idea to avoid nested transactions, mostly because the relationship between parent and child transactions can get complicated. This is especially the case because rollbacks are contained inside of their transactions blocks. I think that Mark Daggett explains this pretty well in his blog:

“ActiveRecord::Rollback does not propagate outside of the containing transaction block and so the parent transaction does not receive the exception nested inside the child. I find it easier to think of nested transactions like the child who dumps its contents into the parent container, leaving the child transaction empty. To ensure a rollback is received by the parent transaction you must add the requires_new: true. option to the child transaction.”

2. Different classes, one transaction

Because transactions are bound to database connections, we can mix different types of models inside of a transaction block. In fact, that’s exactly what we were doing when we wrote our initial transaction:

Order.transaction do
  @order.process
  @user.charge
  @vendor.add_sale
end

3. Class and instance methods

The great part about transaction is that it is available to us as both a class and an instance method for our ActiveRecord models. What does this mean, exactly? Well, the short answer is that we can write a transaction is lots of different ways, since we can invoke the transaction method on a class or an instance.

For example, we could have written this:

User.transaction do
  # methods we want to call go here
end

Or this:

Vendor.transaction do
  # methods we want to call go here
end

Or any of these:

@order.transaction do
end

@user.transaction do
end

@vendor.transaction do
end

And if we were writing a method inside of the Order, Vendor, or User classes, these options would have worked as well:

self.transaction do
end

self.class.transaction do
end

The key here is that the transaction can be called on any class that inherits from ActiveRecord::Base. Why is that the key? Well, you might remember that we initially started off wanting to write a transaction inside of our service object…right? In that case, we can’t use something like transaction do, because self is the service object class, which does not inherit from ActiveRecord::Base!

So, what do? Well, just call the transaction method onto ActiveRecord::Base directly! there’s a quick fix for that.

ActiveRecord::Base.transaction do
  # methods we want to call go here
end

When in doubt, we can always just call the transaction method onto the ActiveRecord::Base class directly to be sure that it will run.

4. Exceptions are the rule

There’s one golden rule of the transaction block: it will only rollback the transaction if an error is raised.

Why is this important? Well, calling something like save or destroy inside of a transaction will not raise an error; if something goes wrong, these methods will simply return false. Which means that our transaction block will continue, since there was no error raised!

Uh oh…how to fix? Just use the save! and destroy! methods instead! These are both ActiveRecord methods which raise an exception if they don’t execute successfully:

ActiveRecord::Base.transaction do
  @order.destroy!
  @user.save!
end

And that’s exactly what we need in this case, because we want the entire transaction to be closed if one or both of these methods are unsuccessful, and we want to tell the database that no data has changed. If we really, really wanted to use save instead of save!, we’d have to manually raise an error in the block for our transaction to work as expected.

Transactions Under The Hood

One of the interesting things about how transaction works under the hood has to do with how the save and destroy methods work. It turns out that Rails actually wraps the save and destroy methods in their own transactions! So, we were using ActiveRecord::Base.transaction all along, without probably ever knowing what was really happening! Pretty crazy, right?

There are a couple reasons that save and destroy are particularly curious. First, because they each occur in their own transactions, this means that we’ll never need to write a transaction block to update a single record. In fact, that’s exactly what Rails is doing for us when we call something like @user.save — it’s running a transaction block behind the scenes.

The second reason this is interesting is because of callbacks that are associated with these two methods. A callback hook like after_save is actually part of the same active transaction that was opened when we called @user.save. So, if we wanted our code to execute outside of Rails’ default transaction that wraps around save or destroy, we’d want to use callback hooks like after_commit or after_destroy. If we want something specific to happen when the save transaction succeeds, we’d have to use the after_commit callback, and if we want something specific to happen when the save transaction fails, we could use the after_rollback hook.

While reading about transactions and the save and destroy methods, I discovered another really interesting method called with_transaction_returning_status. The transactions source code is a bit of a rabbit hole, but if we spend some time reading through it, there’s a lot to learn.

In a nutshell, the with_transaction_returning_status is responsible for actually raising an ActiveRecord::Rollback. Somewhere inside of the black box of Rails magic, when we see an error caused by a save transaction that looks like this:

:001 > user = User.find(1)
:002 > user.save
  (0.2ms)  begin transaction
  User Exists (0.6ms)  SELECT * FROM "users" WHERE ("users"."id" = 1)
   (0.1ms)  rollback transaction
 => false

it’s actually a method like with_transaction_returning_status that’s responsible for causing that rollback to happen! Here’s a truncated example of the source code; we can see that this method defines a local status variable, sets and returns the status if the transaction is successful, and raises an error, if the status is not defined:

def with_transaction_returning_status
  status = nil
  self.class.transaction do
      # some Rails magic here that captures the
      # return value of a save or destroy method,
      # and then sets that to the status.

      raise ActiveRecord::Rollback unless status
  end
  status
end

The Rails documentation also briefly explains exactly what’s happening:

Executes method within a transaction and captures its return value as a status flag. If the status is true the transaction is committed, otherwise a ROLLBACK is issued. In any case the status flag is returned.

Transactions are all around us, it seems, from the most everyday methods to the more complicated ones that we write ourselves. They’re a great way of keeping our code safe while also defending against things like duplicate SQL queries.

tl;dr?

ActiveRecord’s transaction method takes a block, and will only execute the block and write to your database if no exceptions are raised.
You can defined the transaction method on any class that inherits from ActiveRecord::Base, and that transaction will open up a single new database connection.
Check out the Jeff Casimir helpful summary of transactions in his Gist. Or, if you’re looking for a challenge, head over to the Rails documentation on ActiveRecord transactions.

Words and Code

One writer’s journey from words to code.