JDBC Transaction not working

Question

I have a simple database transaction like the code below. The first query should work fine while the second one should throw an exception and therefore the transaction should not go through!

The problem is, after running this code, the first query seemed to have inserted a row in my table, as if it was not transactional. The code does throw an exception and myCon.rollback(); is executed but that new row is inserted in the table anyways.

I am not sure what exactly I am missing, any hint would be highly appreciated.

-- Edit: The problem was that I was using the default MyISAM engine for my table. I changed it to InnoDB and the problem was solved.

    Connection myCon = null;

    try
    {
        Class.forName("com.mysql.jdbc.Driver");
        myCon = DriverManager.getConnection (dbUrl, dbUser, dbPass);

        myCon.setAutoCommit(false); // the intention is to do a transaction

        Statement stmt = myCon.createStatement();

        String query = "INSERT INTO tbltest(f1) VALUES (1);";
        stmt.executeUpdate(query);

        query = "INSERT INTO"; // a malformed query
        stmt.executeUpdate(query);

        con.commit();
    }
    catch(Exception e)
    {
        System.err.println(e.toString());
        myCon.rollback();
    }
    finally
    {
        myCon.close();
    }

Answer 1

Your MySQL database has to be set up to support such a thing. I believe that means InnoDB for every table. Is that the case for your situation? If not, create InnoDB tables and try again.

http://dev.mysql.com/doc/refman/5.0/en/commit.html