Transactions in MySQL - Unable to Roll Back
I had a second database open that had bad data showing ><... this method works. Turns out I didn't even need:
command.CommandText = "SET autocommit = 0";
command.executeNonQuery();
So this code does work for transactions.
Austin
Updated on June 14, 2022Comments
-
Austin almost 2 years
I'm using MySQL 5.0.27 and am trying to get transactions to work. I followed this tutorial:
http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqltransaction.html
and still cannot get these to work. The table I am trying to update is InnoDB and have tried to execute 'set autocommit=0' but it doesn't seem to be doing anything.... The code I've written is the following:
public int transactionUpdate() { MySqlConnection connection = new MySqlConnection(connStr); connection.Open(); MySqlCommand command = connection.CreateCommand(); MySqlTransaction trans; trans = connection.BeginTransaction(); command.Connection = connection; command.Transaction = trans; try { command.CommandText = "SET autocommit = 0"; command.executeNonQuery(); command.CommandText = "UPDATE TBL.rec_lang rl SET rl.lang_code = 'en-us' WHERE rl.recording=123456"; command.executeNonQuery(); command.CommandText = "UPDATE TBL.rec_lang rl SET rl.lang_code = en-us WHERE rl.recording=123456"; command.executeNonQuery(); trans.Commit(); } catch(Exception ex) { try { trans.Rollback(); } catch(MySqlException mse) { log.error(mse); } } }
The second command fails as it is missing the ' around 'en-us'. This should roll back the first query as well to a previous value but it isn't. Can you tell me what I'm doing wrong???
MySQLConnector v. 6.3.6.0
MySQL v. 5.0.27
C# VS2010