MySQL and transactions do not roll back

Question

I am writing an import procedure and want all imports to fail if an error occurs. I am using a MySQL database that is configured on InnoDB and an asp page to import imports. I want to start a transaction, and then rollback if an error occurs or is committed if it is successful. My problem is that when an error occurs in line 4, the first 3 records are stored in the database, and not rolled back.

Here is an example 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{
            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 read about autocommit and how you should install it in the database. The only problem is that if he disconnects it, how will it affect all other database inserts that do not yet have transactions installed. Also, I don't see how to enable or disable C # auto-creation.

- , ?

Answer 1

, .

Answer 2

. sql "set autocommit = 0"

Answer 3

? , db innoDB, - MyISAM .

SHOW CREATE TABLE tablename

Answer 4

, Autocommit , . , , , proc - , . MySQL, 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();

Answer 5

I had a similar problem. The stored procedure, which in turn failed, called another stored procedure, which had lines START TRANSACTIONand COMMIT. As soon as I deleted these commands, the team did the work as expected.