1

I am using C# and MYSQL and the Rollback is not working. I have three delete statement where the table third delete SQL don't exists and in catch i am doing rollback but its only happening for third while 

         string id = dataGridView1.Rows[index].Cells[0].Value.ToString();
         string strDelete = "DELETE FROM user WHERE id = " + id;
         OdbcTransaction transaction = null;
         OdbcCommand cmd = new OdbcCommand();
         cmd.Connection = Singleton.Instance.GetConnection();
         transaction = Singleton.Instance.GetConnection().BeginTransaction();
         cmd.Transaction = transaction;
         try
         {
             cmd.CommandText = strDelete;
             cmd.ExecuteNonQuery();
             // delete from userdata
             strDelete = "DELETE FROM userdata WHERE id = " + id;
             cmd.CommandText = strDelete;
             cmd.ExecuteNonQuery();
             // delete from usersystem
             strDelete = "DELETE FROM usersystem WHERE id = " + id;
             cmd.CommandText = strDelete;
             cmd.ExecuteNonQuery();
             // delete from user systemstatus. here table don't exists, will throw 
             // exception 
             strDelete = "DELETE FROM usersystemstatusAAAA WHERE id = " + id;
             cmd.CommandText = strDelete;
             cmd.ExecuteNonQuery();
             transaction.Commit();
         }
         catch (Exception ex)
         {
             // Attempt to roll back the transaction.
             try
             {
                 transaction.Rollback();
             }
             catch (Exception ex2)
             {
                 Console.WriteLine("  Message: {0}", ex2.Message);
             }

         }

Here the first two SQL statements are committed while suppose to rollback all.

CrazyC
  • 1,840
  • 6
  • 39
  • 60
  • Are you using MyISAM or InnoDB as storage engine? Does a rollback work when you execute it in SQL? – Sjoerd Jan 26 '12 at 09:25

2 Answers2

4

You need to check the storage type of your MySQL database. Not all storages are transactional. For example InnoDB has transactions while MyISAM does not.

MyISAM versus InnoDB MyISAM versus InnoDB

Community
  • 1
  • 1
Jeno Laszlo
  • 2,023
  • 18
  • 36
2

I suspect you have a second connection, and there is confusion over the connection/transaction/command; try starting the transaction on the connection you are actually using instead:

cmd.Connection = Singleton.Instance.GetConnection();
transaction = cmd.Connection.BeginTransaction();

also, you might want to look at using blocks here.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900