Lucas Uses Venmo, I Use Transactions

Transactions allow us to execute batches of SQL operations either in their entirety or not at all. While this may seem simple, it is an incredibly powerful tool that solves incredibly important real world problems.

What are SQL transactions?

A transaction is a group of related SQL statements that will either be committed or rolled back, depending on whether the statements within perform as expected. Generally, SQL statements are implicitly committed to the database. Transactions commit statements to the database explicitly, allowing several related statements to be committed all at once or not at all. What’s the benefit? If two statements must occur concurrently, for example a transaction between individuals, non-transactional SQL runs the risk of implicitly committing the first before the second fails. If the statements are wrapped in one transaction, this can’t happen.

Let’s look at an example. My friend Lucas and I go to a bar. He pays for the drinks and requests $6 (because NYC bars are exorbitantly pricey).

Venmo without transactions

1
2
3
4
5

UPDATE Users
SET balance = CASE name
WHEN 'Lucas' THEN balance + 6
WHEN 'Pat' THEN balance — 6
END

Most of the time, this will work fine. However, if the second operation throws an error (for example, if my balance is below $6 and cannot fall below 0), the first has already occurred. Venmo has just deposited $6 into Lucas’ account, but failed to withdraw it from mine. If Venmo failed to account for this, its users would surely be gifting each other money out of thin air all the time.

Venmo with transactions

1
2
3
4
5
6
7

BEGIN TRANSACTION
UPDATE Users
SET balance = CASE name
WHEN 'Lucas' THEN balance + 6
WHEN 'Pat' THEN balance — 6
END
COMMIT TRANSACTION

Venmo can avoid the ramifications of the edge case described above through a simple SQL transaction. By grouping the statements together in a batch, the deposit into Lucas’ account will only occur if the withdrawal of my account is successful and vice versa.

Transactions in Rails

The benefit of transactions is clear, but writing complex SQL statements throughout an app isn’t fun. Most Rubyists would prefer to rely on Rails (more specifically, ActiveRecord) for this sort of thing. Thankfully, ActiveRecord::Base has transaction processing baked right in!

Here’s how it works:

1
2
3
4

User.transaction do
  lucas.deposit(6)
  pat.withdraw(6)
end

Isn’t that easy? I know, this is a simple example, but thanks to some nifty programming transactions are extremely flexible. We used transaction as a class method on our User model, but it is available on ActiveRecord::Base itself and all classes and instances inheriting from it. You can even perform operations on instances of multiple ActiveRecord classes within a single transaction. The only exception to this would be if the classes have separate database connections.

Anti-patterns to avoid

Single record transactions

Wrapping a single database operation in a transaction is pointless. There are no other operations within the transaction that will experience complications should this transaction throw an error. Either your transaction is missing another operation or the transaction is superfluous.

Using “exception-less” methods

ActiveRecord will rollback a transaction if one of the methods within the transaction block returns an error. A common mistake is to use methods like save and destroy, which return a boolean false upon failure. This will not cause the transaction to rollback. The save! and destroy! methods will return an error upon failure and should be used instead.