MySQL Cookbook, 2nd Edition

Using Transactions in Ruby Programs

Problem

You want to perform a transaction in a Ruby DBI script.

Solution

Use the standard DBI transaction support mechanism. Actually, Ruby provides two mechanisms.

Discussion

The Ruby DBI module provides a couple of ways to perform transactions, although both of them rely on manipulation of auto-commit mode. One approach uses a begin/rescue block, and you invoke the commit and rollback methods explicitly:

begin
  dbh['AutoCommit'] = false
  dbh.do("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'")
  dbh.do("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'")
  dbh.commit
  dbh['AutoCommit'] = true
rescue DBI::DatabaseError => e
  puts "Transaction failed"
  puts "#{e.err}: #{e.errstr}"
  begin           # empty exception handler in case rollback fails
    dbh.rollback
    dbh['AutoCommit'] = true
  rescue
  end
end

Ruby also supports a transaction method, which is associated with a code block and which commits or rolls back automatically depending on whether the code block succeeds or fails:

begin
  dbh['AutoCommit'] = false
  dbh.transaction do |dbh|
    dbh.do("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'")
    dbh.do("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'")
  end
  dbh['AutoCommit'] = true
rescue DBI::DatabaseError => e
  puts "Transaction failed"
  puts "#{e.err}: #{e.errstr}"
  dbh['AutoCommit'] = true
end

With the transaction method, there is no need to invoke commit or rollback explicitly yourself. transaction does raise an exception if it rolls back, so the example still uses a begin/rescue ...