Mysql – Rails transactions

activerecordmysqlruby-on-railstransactions

I am trying to use ActiveRecord::Base.transaction. I figured that rollback doesn't work by default using Rails 1.2.6 and mysql 5.0. Playing with it a little bit more I found out that autocommit is not set to 0 in mysql connection.

Questions:

1) How do I disable autocommit in rails for all connections?

2) Will it have some negative impact on the other code that doesn't have to be transactional?

Best Solution

If you have a mix of code that needs explicit transactions and code that can rely on autocommit, perhaps you shouldn't disable autocommit for all connections. You're on the right track wondering if this will impact other code. If you disable autocommit, but the other code doesn't know it has to commit to have its work committed, then that'll be a problem. Uncommitted work is rolled back when the connection closes.

You should be aware that the default storage engine for MySQL is MyISAM, which doesn't support transactions at all. When you make changes to a table that uses MyISAM, the changes are effectively committed immediately, regardless of your explicit requests to begin and finish transactions, and regardless of the state of autocommit. So you won't be able to roll back no matter what, unless you created your tables using the InnoDB storage engine (or other transaction-safe storage engines such as BDB).

It's unnecessary to disable autocommit mode to use transactions in MySQL. Simply begin a transaction explicitly. The following SQL statements will be part of a transaction until you commit or rollback that transaction, regardless of the value of autocommit on your current connection.

http://dev.mysql.com/doc/refman/5.0/en/commit.html says:

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.