ProblemYou want to perform a transaction in a Ruby DBI script. SolutionUse the standard DBI transaction support mechanism. Actually, Ruby provides two mechanisms. DiscussionThe 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. |