Surprises with Nested Transactions, Rollbacks and ActiveRecord

Lately I acquired a new hobby. I went around and asked experience Rails developers, whom I respect and value a lot, how many users the following script would create:

	User.transaction do
	User.create(name: 'Kotori')
	User.transaction do
	User.create(name: 'Nemu')
	raise ActiveRecord::Rollback
	end
	end

view raw

nested_transactions.rb

hosted with ❤ by GitHub

The result should be the same on pretty much any database and any Rails version. For the sake of argument you can assume Rails 5.1 and Postgres 9.6 (what I tested it with).

So, how many users does it create? No one from more than a hand full of people I asked got the answer right (including myself).

The answer is 2.

Wait, WHAT?

Yup you read that right. It creates 2 users, the rollback is effectively useless here. Ideally this should create one user (Kotori), but as some people know nested transactions isn’t really a thing that databases support (save for MS-SQL apparently). People, whom I asked and knew this, then guessed 0 because well if I can’t rollback a part of it, better safe than sorry and roll all of it back, right?

Well, sadly the inner transaction rescues the rollback and then the outer transaction happily commits all of it. 😦

Before you get all worried – if an exception is raised and not caught the outer transaction can’t commit and hence 0 users are created as expected.

A fix

So, what can we do? When opening a transaction, we can pass requires_new: true to the transaction which will emulate a “real” nested transaction using savepoints:

	User.transaction do
	User.create(name: 'Kotori')
	User.transaction(requires_new: true) do
	User.create(name: 'Nemu')
	raise ActiveRecord::Rollback
	end
	end

view raw

nested_transaction_requires_new.rb

hosted with ❤ by GitHub

As you’d expect this creates just one user.

Nah, doesn’t concern me I’d never write code like this!

Sure, you probably straight up won’t write code like this in a file. However, split across multiple files – I think so. You have one unit of business logic that you want to run in a transaction and then you start reusing it in another method that’s also wrapped in another transaction. Happens more often than you think.

Plus it can happen even more often than that as every save operation is wrapped in its own transaction (for good reasons). That means, as soon as you save anything inside a transaction or you save/update records as part of a callback you might run into this problem.

Here’s a small example highlighting the problem:

	class User < ApplicationRecord
	attr_accessor :rollback
	after_save :potentially_rollback
	def potentially_rollback
	raise ActiveRecord::Rollback if rollback
	end
	end

view raw

my_user.rb

hosted with ❤ by GitHub

	User.transaction do
	User.create(name: 'Kotori')
	User.create(name: "someone", rollback: true)
	end

view raw

rollback_in_save_in_nested.rb

hosted with ❤ by GitHub

As you probably expect by now this creates 2 users. And yes, I checked – if you run create with rollback: true outside of the transaction no user is created. Of course, you shouldn’t raise rollbacks in callbacks but I’m sure that someone somewhere does it.

In case you want to play with this, all of these examples (+ more) are up at my rails playground.

The saddest part of this surprise…

Unless you stumbled across this before, chances are this is at least somewhat surprising to you. If you knew this before, kudos to you. The saddest part is that this shouldn’t be a surprise to anyone though. A lot of what is written here is part of the official documentation, including the exact example I used. It introduces the example with the following wonderful sentence:

For example, the following behavior may be surprising:

As far as I can tell this documentation with the example has been there for more than 9 years, and fxn added the above sentence about 7 years ago.

Why do I even blog about this when it’s in the official documentation all along? I think this deserves more attention and more people should know about it to avoid truly bad surprises. The fact that nobody I asked knew the answer encouraged me to write this. We should all take care to read the documentation of software we use more, we might find something interesting you know.

What do we learn from this?

READ THE DOCUMENTATION!!!!

Setting up PostgreSQL for Ruby on Rails on Linux

So every once upon a time I run into the situation, that I have a newly set up machine and have to configure my system again to use PostgreSQL and play nicely with Ruby on Rails. And I don’t want to have to google the set up every time, hence this post (and of course to help people with similar problems). Why PostgreSQL? Well many people like it and you need it for instance for deploying on heroku and your development environment should be as close to your production environment as possible.

What I do here is a way that works for me on my Linux Mint Debian Testing machines. Be aware that this is my development set up – I don’t use this in production.  I’m no PostgreSQL expert so, this just works for me and I hope that it will for other people as well :-). Suggestions/Improvements are welcome as always.

Let’s get started! So at first we have to install PostgreSQL:

sudo apt-get install postgresql

After we’ve don this we need to create a user in PostgreSQL. So we use the user account of PostgreSQL to create a user with sufficient rights. I just take my own account and grant it sufficient rights with PostgreSQL. Don’t forget to substitute my username with yours!

tobi@speedy ~ $ sudo su postgres
[sudo] password for tobi:
# the behavior of createuser seems to have changed in recent postgresql versions
# So now do the following (-d says allowed to create databases):
postgres@speedy /home/tobi $ createuser tobi -d # replace tobi with your user acc name
# In older versions it used to work like this:
postgres@speedy /home/tobi $ createuser tobi # (substitute with your username)
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n

At this point it would be good to install the pg gem – which is the adapter for the PostgreSQL database. So simply add

gem 'pg'

to your Gemfile. Also make sure to replace existing database gems – like sqlite. Now run:

bundle install

to update your dependencies. That should go smoothly, otherwise you are probably missing dependencies.

Now you still need to modify your config/database.yml. Most of all you need to change the adapter to “postgresql”. Here is the development part of my database.yml for reference:

development:
  adapter: postgresql
  database: ArbitaryDatabaseName
  pool: 5
  timeout: 5000

Make sure that the different environments (development, test and production) have got different databases (meaning the database property should be different). Otherwise they will influence each other and you don’t want that.

In order to finish the set up you need to create all the databases, which should work flawlessly by now:

rake db:create:all

When this is done you should be able to “rake db:migrate” your database as you are used to (and be sure to do so).

I hope this post helped you with setting up PostgreSQL for Ruby on Rails on Linux. If something didn’t work for you, you feel that a step is missing or you just have a useful tip – please feel free to comment!

Setting up PostgreSQL with TravisCI

TravisCI is an awesome free continuous integration system that just takes your github repositories and then runs all your tests – it is pure awesomeness and easy as cake.

However when testing a web application you also have to setup a database – they have got good docs for that, but still I ran into problems.

What was my problem? In their description the database name is “myapp_test” and I believed that it would not matter and it could be anything like “Tracketytrack Test” I wanted. I got proven wrong. Apparently it has to be “myapp_test”. Also for some reason I had to add a manual call to “rake db:migrate” – I may check into this later. For your reference, here the relevant parts of my .travis.yml as a gist.

And here they are as well for your reference:

postgres:
  adapter: postgresql
  database: myapp_test
  username: postgres
before_script:
  - "psql -c 'create database myapp_test;' -U postgres"
  - "rake db:migrate"