JDBC transactions rollback() still updating table

Question

I have a table that contains an id column, a bids column and a cost column. I am using transactions in jdbc. I set auto commit to false, executed some queries to update that table and then called the rollback method, which should undo changes made in the transaction and revert the table back to its previous state. However, the table is still being updated.

Here is my code:

public class JdbcTransactions {
public static void main(String[] args) {
    Connection myConn = null;
    Statement myStmt = null;

    try {
        myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/tutorialdatabase", "student", "student");

        myConn.setAutoCommit(false);

        myStmt = myConn.createStatement();
        myStmt.executeUpdate("UPDATE items SET bids = 0 WHERE id <= 5");
        myStmt.executeUpdate("UPDATE items SET cost = cost + 100 WHERE id <= 5");

        myConn.rollback();


    }catch(SQLException e) {
        System.err.println("Something went wrong: ");
        e.printStackTrace();

    } finally {
        try {
            myStmt.close();
            myConn.close();
        }catch(SQLException e) {

        }
    }
}

}

I want to update the items table in tutorialdatabase to set the bids to 0 and cost to increment by 100 for the first 5 rows(all rows with id 1 - 5)

Are you sure you are not getting any exceptions when running the code? — tsolakp, Jun 08 '17 at 22:06
What about any other exceptions. Maybe you need to catch generic Exception and see what is going on? — tsolakp, Jun 08 '17 at 22:14
your autocommit may also be enabled on the database session level — access_granted, Jun 09 '17 at 00:04
What is the DDL of the table, are they MyISAM tables by any chance? — Mark Rotteveel, Jun 09 '17 at 06:42
@Solace Performing a commit before a rollback makes zero sense. — Mark Rotteveel, Jun 09 '17 at 06:43
Well, you either need to live with this or switch to InnoDB; MyISAM doesn't support transactions (see duplicate). — Mark Rotteveel, Jun 09 '17 at 13:46
Ah, OK I see, thank you. I did not realise MyISAM didn't support transactions :) — Nalyd, Jun 09 '17 at 13:48

score -1 · Answer 1 · answered Jun 08 '17 at 23:07

-1

Try to put autocommit off on db SET autocommit=0;

answered Jun 08 '17 at 23:07

user3186511

63
2
8

Auto commit is turned off using `myConn.setAutoCommit(false)`, you are not supposed to use database statements for that because it can leave the driver-side connection in an incorrect state. – Mark Rotteveel Jun 09 '17 at 06:42

JDBC transactions rollback() still updating table

1 Answers1