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.
Thanks
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.
You don't have to disable autocommit to use transactions. When you START a transaction autocommit setting doesn't make any difference, you have to COMMIT or ROLLBACK explicitly. Moreover disabling autocommit will impact your non-transactional code.
Not that I have any specifically not transactional code - it is mostly all ActiveRecord objects, I just want to have rollback behavior if one of my methods fails.
I will explore more , you seem to be right - I can confirm your answer in mysql console. However in my Rails application I have to do connection.execute("set autocommit = 0") to get rollback working.
Right answer. I was doing DROP TABLE IF EXISTS in my code in the beginning of the transactions. MySQL seems to have a problem with DROP statements within a transaction:
http://bugs.mysql.com/bug.php?id=989
Funny enough that I've guessed correct work-around for the problem sending "SET aucotommit = 0" before the DROP statement.
Thank you for your help