CODEWITHSUNDEEP
javajdbctransactions
Abbas
Abbas

Reputation: 3258

JDBC Transaction not working

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();
    }

Upvotes: 2

Views: 1698

Answers (2)

mbelow
mbelow

Reputation: 1133

What MySql engine are you using? AFAIK, some engines (MyISAM for example) do not support transactions. InnoDB does.

Upvotes: 5

duffymo
duffymo

Reputation: 308998

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

Upvotes: 5

Related Questions