Rollback does not work

Question

I want to rollback all records have been inserted to table when exception occurs.

but conInsert.rollback() doesn't work.

Maybe I miss some code?

Here is my code

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(connectionUrl);
            //con.setAutoCommit(false);
            Statement st = con.createStatement();

            String querySelectOrderInTp = "SELECT order_in_tp_id, order_in_tp_qty, order_in_tp_price, order_in_tp_article_tc_id, order_in_tp_warehouse_tc_id, inv_stock_tp_id, inv_stock_tp_qty_available from order_in_tp LEFT JOIN inv_stock_tp on(order_in_tp_warehouse_tc_id=inv_stock_tp_whouse_current_id AND order_in_tp_article_tc_id=inv_stock_tp_article_tc_id AND order_in_tp_price=inv_stock_tp_price) where order_in_tp_pick_up_timestamp = 'A' AND order_in_tp_date = '2013-06-11' GROUP BY order_in_tp_id;";
            ResultSet rs = st.executeQuery(querySelectOrderInTp);

            String queryUpdateInvStockTp = "INSERT INTO inv_stock_tp (cre_tms,upd_tms,cre_usr,upd_usr,version,usr_act,inv_stock_tp_id,inv_stock_tp_key, inv_stock_tp_whouse_current_id,inv_stock_tp_article_tc_id,inv_stock_tp_qty_available,inv_stock_tp_qty_min,inv_stock_tp_price) VALUES (NOW(),NOW(),'demo2','demo2',1,'A',null,'AAAA',?,?,?,0,2000.0000)";
            conInsert = DriverManager.getConnection(connectionUrl);
            conInsert.setAutoCommit(false);
            ps = conInsert.prepareStatement(queryUpdateInvStockTp);

            String queryUpdateOrderInTp = "UPDATE order_in_tp set order_in_tp_pick_up_timestamp = ? WHERE order_in_tp_id = ?";
            psUpdate = con.prepareStatement(queryUpdateOrderInTp);

            while(rs.next()) {
                Integer qty = rs.getInt(7) - rs.getInt(2);

                ps.setString(1, rs.getString(5));
                ps.setString(2, rs.getString(4));
                ps.setString(3, rs.getString(2));
                ps.execute();

                psUpdate.setString(1, "A");
                psUpdate.setString(2, rs.getString(1));
                psUpdate.execute();
                ps.clearParameters();
                psUpdate.clearParameters();
            }
            conInsert.commit();
        } catch (Exception e) {
            e.printStackTrace();
            if (conInsert != null) {
                try {
                    System.err.print("Transaction is being rolled back");
                    conInsert.rollback();
                } catch (SQLException excep) {
                    excep.printStackTrace();
                }
            }
        }

I make an exception in last record but all record before it still have been inserted.

http://stackoverflow.com/questions/14016278/is-there-any-way-to-rollback-after-commit-in-mysql — Naveen Kumar Alone, Jul 12 '13 at 07:56
Do you use a transactional db engine like innodb? or do you use MyISAM? — JB Nizet, Jul 12 '13 at 07:58

score 0 · Answer 1 · answered Jul 12 '13 at 08:32

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent. I dont know java, but make sure that You have the START TRANSACTION statement, and then COMMIT or ROLLBACK.

Rollback does not work

1 Answers1