Robot Has No Heart

From the I-want-my-evening-back department, differences in behaviour when
setting isolation levels between MySQL 5.1, 5.5, and postgres. Documenting here
for my poor future self.

In postgres and MySQL 5.1, the following is the correct ordering:

1
2
3
4
5
6
7

ActiveRecord::Base.transaction do
  ActiveRecord::Base.connection.execute(
    "SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE"
  )

  # ...
end

On MySQL 5.5 with mysql2 gem, no error will be raised, but the isolation
level will not be set correctly. If you run the same commands in a mysql shell,
you see an error informing that the isolation level cannot be set after the
transaction has started.

Ok well, let’s move it outside then:

1
2
3
4
5
6

ActiveRecord::Base.connection.execute(
  "SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE"
)
ActiveRecord::Base.transaction do
  # ...
end

That works on 5.5, but fails on postgres.

Here is an 13 minute long screencast in which I show you how to go about tracking down a deadlock in a ruby on rails application. I make two assumptions:

1. You are using MySQL
2. You know the difference between shared and exclusive locks (in short: a shared lock allows other transactions to read the row, an exclusive blocks out everyone)

(If embedding doesn’t work or the text is too small to read, you can grab a high resolution version direct from Vimeo)

This is only one specific example of a deadlock, in reality there are many ways this can occur. The process for tracking them down is always the same though. If you get stuck, read through the innodb documentation again. Something normally jumps out. If you are not sure what ruby code is generating what SQL, the query trace plugin is excellent. It gives you a stack trace for every single SQL statement ActiveRecord generates.

This is a small taste of the type of thing I cover in my DB is your friend training course. July through September I am running full day sessions in the US and UK. Chances are I’m coming to your city. Check it out at http://www.dbisyourfriend.com

UPDATE: This code is too old. Use db2fog instead. It does the same thing but better.

Here is some code I wrote over the weekend – db2s3. It’s a rails plugin that provides rake tasks for backing up your database and storing it on Amazon’s S3 cloud storage. S3 is a trivially cheap offsite backup solution – for small databases it costs about 4 cents per month, even if you’re sending full backups every hour.

There are many scripts around that do this already, but they fail to address the biggest actual problem. The aws-s3 gem provides a really nice ruby interface to S3, and dumping a backup then storing it really isn’t that hard. The real problem is that I really hate system administration. I want to spend as little time as possible and I want things to Just Work.

A script is great but there’s still too many things for me to do. Where does it go in my project? How do I set my credentials? How do I call it?

That’s why a plugin was needed. It’s as little work as possible for a rails developer to backup their database, so they can get back to making their app awesome.

db2s3. Check it out.