Rails transactions
I am trying to use ActiveRecord::Base.transaction
. I figured rollback does not work by default using Rails 1.2.6 and mysql 5.0. Playing with it a bit more I found out that it is autocommit
not set to 0 in the mysql connection.
Questions:
1) How to disable auto-message in rails for all connections?
2) Will this have some kind of negative impact on other code that shouldn't be transactional?
source to share
If you have a mix of code that requires explicit transactions and code that might rely on autocommitting, you might not want to disable autocommit for all connections. You are on the right track wondering if this will affect other code. If you turn off autocommit but the other code doesn't know that it should commit its work, then that will be a problem. Work in progress is canceled when the connection is closed.
You should be aware that the default storage engine for MySQL is MyISAM , which does not support transactions at all. When you make changes to a table using MyISAM, those changes are effectively committed immediately, regardless of your explicit requests to start and end transactions, and regardless of the autocommit state. This way you won't be able to rollback no matter what if you haven't created your tables with the InnoDB storage engine (or other transactions like BDB ).
It is unnecessary to disable autocommit mode for using transactions in MySQL. Just start the transaction explicitly. The following SQL statements will remain part of a transaction until you commit or rollback that transaction, regardless of the autocommit value on your current connection.
http://dev.mysql.com/doc/refman/5.0/en/commit.html says:
WITH AN INITIAL OPERATION, autocommit remains disabled until you complete the transaction with a COMMIT or ROLLBACK. The autonegotiation mode then reverts to its previous state.
source to share
Correct answer. I was doing DROP TABLE IF EXISTS in my code at the start of transactions. MySQL seems to have problems with DROP statements in a transaction:
http://bugs.mysql.com/bug.php?id=989
It's funny that I figured out the correct work for the problem of sending "SET aucotommit = 0" before the DROP statement.
thanks for the help
source to share
Not that I have any specific non-transactional code - these are basically all ActiveRecord objects, I just want to be able to rollback if one of my methods fails.
I will explore more, you seem to be right - I can confirm your answer in the mysql console. However, in my Rails application, I need to execute connection.execute ("set autocommit = 0") to get the rollback.
source to share