The SQL transaction is a decent mechanism for handling complex database operations and keeping data consistent. What's its job?

In a nutshell, every operation under the transaction block has to be successful. What if something went wrong? Then every operation is rolled back. In other words, SQL transaction makes operations atomic, so they're treated as an indivisible whole.

"Do everything, but when something is wrong, do nothing"

A classic example is sending and withdrawing money. If a crappy ATM doesn't withdraw your money, you should keep your bank account balance unchanged. That's where the transactions shine.

In a Rails world, we could describe a transaction as:

ActiveRecord::Base.transaction do
  me.send_money_to!(id: "Mr-Robot", amount: 100)
  # 🐛 - "I can break it!"
  mr_robot.credit!(100)
end

In the example above, I send $100 to Mr. Robot. It subtracts 100 from my balance and adds it to Mr Robot's account. Thanks to the transaction, if 🐛 activates at the 2nd line, it'll roll back the action above and give me back my 100 bucks. I won't lose the money! But Mr. Robot won't get them too, though.

To trigger rollback, an error has to be raised. Remember to use methods raising exceptions, prepended by convention with "!"

Active Record transaction -> SQL transaction

Rails transaction is nothing fancy. Under the hood, it uses SQL transaction block. So it's just BEGIN, SQL operations, and at the end COMMIT (successful case) or ROLLBACK (well, less successful case).

BEGIN
-- do some SQL stuff
-- ... and here

COMMIT -- when everything was fine!
-- when not, then...
ROLLBACK

Database operation and API call - easy with the transaction, but…

Let's look at another application for Rails transactions. It's pretty trivial but shows that atomic actions are not reserved just for database operations.

Let's suppose we want to subscribe a user to a newsletter. We want to activate the user's subscription and call a third-party service to add the user to a mailing list.

ActiveRecord::Base.transation do
  user.confirm_subscription!

  response = MailerApi.add_user_to_mailing_list(
    user: user, list_id: "newsletter-123"
  ) # 🐛 - "Will screw up this at some point, promise!"

  raise ActiveRecord::Rollback if response.error?
  # The API call went wrong? Rollback!
end

Calls to 3rd party services are particularly vulnerable. In the case above, when something went wrong with the API call, it triggers rolling back by ActiveRecord::Rollback exception. Remember, a transaction with "safe" actions (not raising errors, without !) is totally useless.

… there's a cost

Be aware, that the transaction block is performed as a whole by a database. This means it keeps the database connection open and blocks the database for the whole transaction's lifetime.

That's why the example above it's not the best in terms of performance and concurrency! 🙉

Anyway, make sure to keep your transaction blocks as lean as possible.