MySQL and Transactions not rolling back

I am writing an import routine and want the whole import to fail if an error occurs. I am using MySQL database which is set to InnoDB and an asp page to drive the import. I want to start a transaction and then rollback if an error occurs or commit if it is successful. My problem is that when an error occurs on line 4 the first 3 entries are saving in the database rather than rolling back.

Here is a sample of my code:-

        MySqlConnection conn = new MySqlConnection(connStr);
        conn.Open();

        MySqlCommand cmd = new MySqlCommand();

        MySqlTransaction tran = conn.BeginTransaction();

        cmd.Connection = conn;
        cmd.Transaction = tran;

        int ErrorCount = 0;
        do while read from file{
    开发者_如何学Python        try{
                if (fail validate){
                    ErrorCount ++;
                    break;
                }
                run store procedure 1 which does insert
                run store procedure 2 which does insert
            }
            catch (exception e){
                ErrorCount ++;
                break;
            }
        }

        if (ErrorCount == 0){
            tran.Commit();
        }
        else{
            tran.RollBack();
        }

        if (conn != null) conn.Close();

I have read about autocommit and how you have to set it in the database. The only problem is that if it set it off how will it affect all the other inserts into the database that don't already have transactions set. Also I can not see how to set autocommit on or off from c#.

Anyone know how to get my transactions rolling back?

Thanks Cheryl

If your stored procedures has transactions or statements with implicit commits in them then this will happen.

You can turn off autocommit for you session only. Try to execute sql "set autocommit=0"

Have you checked the storage engine for this table? I know your db defaults to innoDB, but I've somehow created MyISAM tables in that type of environment before.

SHOW CREATE TABLE tablename

I believe Autocommit is on a per command basis, so it won't quite handle your situation. I think the key is to make sure that each stored proc call is a new command that's added to the transaction. I don't tend to use MySQL, but I've used the following code successfully on an OleDb.

OleDbConnection conn = new OleDB(); //obviously missing important stuff...
conn.open();

using(OleDbTransaction trans = conn.BeginTransaction()){
    try{
        OleDbCommand cmd1 = new OleDbCommand("insert into t1...", conn, trans);
        cmd1.ExecuteNonQuery();

        OleDbCommand cmd2 = new OleDbCommand("insert into t2...", conn, trans);
        cmd2.ExecuteNonQuery();

        trans.Commit();
    } catch {
        trans.Rollback();
    }
}

conn.close();

I had a similar issue. The stored procedure that was failing in turn had called another stored procedure which had START TRANSACTION and COMMIT lines. Once I removed these commands the committed worked as expected.