Recipe 15.5. 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 block for error detection.