Why the rollback operation is not done after an exception came in my java code

Question

I have a jdbc code in which I have used transaction management in code. Following is the code.I am using Mysql Database.

public class JdbcConn {
public static void main(String[] args){
    Savepoint spt1 = null;
    Connection con = null;
    try{
        Class.forName("org.gjt.mm.mysql.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost","root","tany");
        con.setAutoCommit(false);
         spt1= con.setSavepoint("svpt1");

        PreparedStatement psmt;
        String query1 = "select city, countryid from querytest.city;";
        psmt=con.prepareStatement(query1);
        ResultSet rs=psmt.executeQuery();
        while(rs.next()){
            String query2 = "insert into sun.city (city,countryid) values('"+rs.getString(1)+"',"+rs.getInt(2)+");";
            psmt=con.prepareStatement(query2);
            psmt.executeUpdate();
        }
        String query3 = "create database `transtest`;";
        psmt=con.prepareStatement(query3);
        psmt.executeUpdate();

        String query4 = "CREATE TABLE `transtest`.`trans` (`id` tinyint(4) NOT NULL auto_increment,`val` int(5) NOT NULL default 0, PRIMARY KEY  (`id`)) ENGINE=MyISAM;";                
        psmt=con.prepareStatement(query4);
        psmt.executeUpdate();


        String query5 = "CREATE TABLE `transtest`.`transone` (`id` tinyint(4) NOT NULL auto_increment,`val` int(5) NOT NULL default 0, PRIMARY KEY  (`id`)) ENGINE=MyISAM;";                
        psmt=con.prepareStatement(query5);
        psmt.executeUpdate();


        String query6 = "CREATE TABLE `transtest`.`transtwo` (`id` tinyint(4) NOT NULL auto_increment,`val` int(5) NOT NULL default 0, PRIMARY KEY  (`id`)) ENGINE=MyISAM;";                
        psmt=con.prepareStatement(query6);
        psmt.executeUpdate();

        for(int i=1;i<=10;i++){
            String query7 = "insert into `transtest`.`transtwo` (`val`) values ("+i*2+");";                
            psmt=con.prepareStatement(query7);
            psmt.executeUpdate();
        }

        String query8 = "insertd into `transtest`.`trans` (`val`) values (500);";                
        psmt=con.prepareStatement(query8);
        psmt.executeUpdate();
        JOptionPane.showMessageDialog(null, "Process completed!");
        con.commit();
        con.setAutoCommit(true);

    }catch(SQLException sqle){
        try {
            con.rollback(spt1);
            JOptionPane.showMessageDialog(null, "Rollback1!");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        sqle.getMessage();
        sqle.printStackTrace();
    }catch (ClassNotFoundException cnfe) {
        // TODO Auto-generated catch block
        try {
            con.rollback(spt1);
            JOptionPane.showMessageDialog(null, "Rollback2!");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        cnfe.getMessage();
        cnfe.printStackTrace();
    }catch (Exception e) {
        // TODO Auto-generated catch block
        try {
            con.rollback(spt1);
            JOptionPane.showMessageDialog(null, "Rollback3!");
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        e.getMessage();
        e.printStackTrace();
    }

}

}

This above code is not rolling back when sql exception comes. The schema of the table in query1 and the schema of the table in query2 is same but as you see the databases are different.

I just dont know if any exception comes why its not rolling back the changes made by the querys from query2 to query7.

I had intensionally made a syntactical mistake in query8 for an exception.

Please guide me friends in this issue and plaease lent me know my mistakes in code.

Thank You!

That's because you're not committing after every `executeUpdate()`. No? — Buhake Sindi, Apr 19 '12 at 12:17
As a general principle, catch `Throwable`, not just `Exception`. In fact, catch **only** `Throwable`, no need to repeat the blocks. That may even solve your problem. — Marko Topolnik, Apr 19 '12 at 12:19
@TheEliteGentleman Thanks you Sir for you reply! As Suggested by you I have added con.commit(); statement after every psmt.executeUpdate() statement still its not rolling back. — Param-Ganak, Apr 19 '12 at 12:31
@MarkoTopolnik Thanks You Sir for your comment! But I really dont understand what exactly you want to say? — Param-Ganak, Apr 19 '12 at 12:32
I say `try { ... all your code as it is ... } catch (Throwable t) { con.rollback(); }` Your try-block might be throwing something that isn't an `Exception`. — Marko Topolnik, Apr 19 '12 at 12:33
Maybe your table does not support transactions? Which storage engine does it use? — , Apr 19 '12 at 12:55

score 5 · Accepted Answer · edited May 23 '17 at 10:26

5

Here's how I would do it:

Connection con = null;
boolean ok = false;
try {
    con = DriverManager.getConnection(...);
    ...
    con.commit();
    ok = true;
} catch (...) {
    // diagnose exception 
} 
...
} finally {
    if (con != null) {
        try {
            if (!ok) con.rollback();
        } finally {
           con.close();
        }
    }
}

In other words, do the connection close and rollback in the finally block ... and don't repeat the code.

And DON"T catch Exception ... see below.

A comment on the Question says this:

As a general principle, catch Throwable, not just Exception. In fact, catch only Throwable, no need to repeat the blocks. That may even solve your problem.

Catching Exception and especially Throwable is a bad idea UNLESS the next action after handling the exception is to exit the application. There are any number of potential unchecked exceptions / errors that might occur. You've no way of knowing what the cause of the unexpected exception was, or whether the application can safely recover.

But the problem in my code is that its not rolling back the transactions done by the queries from query2 to query7

Perhaps it is because some of those statements are non-transactional (e.g. CREATE TABLE), and executing a non-transactional statement causes the current transaction to commit automatically.

Perhaps it is a problem with the old version of MySQL and the JDBC drivers that you are using. The "org.gjt.mm.mysql" driver is REALLY old, and I know for a fact that early versions of MySQL did not support transactions at all.

As I suspected, you can't rollback a CREATE TABLE in MySQL.

Source:

StackOverflow Q&A Is it possible to roll back CREATE TABLE and ALTER TABLE statements in major SQL databases?

edited May 23 '17 at 10:26

Community

1
1

answered Apr 19 '12 at 12:22

Stephen C

698,415
94
811
1,216

You can improve this by throwing out the boolean and just resetting con to `null` in the end. Also, `rollback` can throw an exception on its own, and you still need to close the connection. – Marko Topolnik Apr 19 '12 at 12:25
Agreed on the second point. On the first point, that makes the code harder to understand - an explicit flag is clearer. – Stephen C Apr 19 '12 at 12:33
Do you mean to say that you **don't** need to rollback in case of a non-Exception throwable? I never once encountered such a scenario in all the millions of lines of code I worked with. – Marko Topolnik Apr 19 '12 at 12:35
@Stephen C Thank you Sir for ur reply! But the problem in my code is that its not rolloing back the transactions done by the queries from query2 to query7. – Param-Ganak Apr 19 '12 at 12:35
@MarkoTopolnik - No ... I mean that you don't need to CATCH the exception in order to do the rollback!!! – Stephen C Apr 19 '12 at 12:37
Ah, you mean your idiom here. But that's not the point -- I was answering to OP's idiom. There is nothing wrong with writing a catch-all to rollback. And you can always rethrow if that is your wish. – Marko Topolnik Apr 19 '12 at 12:39
*"You can always rethrow if that is your wish"*. That means that the method would need to be declared as `throws Exception`. Trust me, you don't want your methods to do that. – Stephen C Apr 19 '12 at 12:44
*"But that's not the point -- I was answering to OP's idiom."* - Sorry, but that IS the point! The OP's idiom is wrong because it forces him to catch `Exception` or `Throwable`. You avoid that badness by fixing the idiom, not by suggesting a fix that is as bad if not worse. – Stephen C Apr 19 '12 at 12:48
Depending on the situation, that may be something you *must* do. A simple `new RuntimeException(e)` takes care of your objection. – Marko Topolnik Apr 19 '12 at 12:51
OP is desperate to find out why his tx is not being rolled back. My comment gives a good diagnostic tool with minimum effort. Your idiom is just fine, too, and is a better long-term solution. – Marko Topolnik Apr 19 '12 at 12:52
Yes, but this is not one of those situations. – Stephen C Apr 19 '12 at 12:53
Assuming that `rollback` is actually being called, the problem is not in the exception handling at all. It is at the database level. See my updated answer. – Stephen C Apr 19 '12 at 12:55
I commend your updated answer, you are truly an expert in this field. – Marko Topolnik Apr 19 '12 at 12:58
@StephenC Thank You Sir for your reply! As suggested by you in your answer I changed the Mysql Driver version to latese one and as you mentioned in your answer I have queries from query3 to query6 as non transactional queries. After making the suggested changes the code is rolling back the transactions done by query6. But As per my requirement I also want to roll back the changes made by queries from query2 to query6 whether its as transactional query or non-transactional. So is there any solution on this. – Param-Ganak Apr 19 '12 at 12:59
@Param-Ganak - if an SQL statement is non-transactional, you can't roll it back. See updated answer for source which says that `CREATE TABLE` can't be rolled by in MySQL. – Stephen C Apr 20 '12 at 05:11
To follow this up -- this is just schoolbook code. In real-life project, I'd never architect an application so that each individual DAO method must do its own transaction and exception handling. That is the business of a framework method -- typically AOP. In that case you either catch Throwable or don't catch anything because where you catch it is where you handle it -- in the exception-handling aspect. Transaction handling goes on in its own aspect. – Marko Topolnik Apr 21 '12 at 07:16

score 3 · Answer 2 · answered Apr 19 '12 at 12:48

3

You cannot roll back a create table statement in MySQL, because it will cause an implicit commit. See: Statements That Cause an Implicit Commit

answered Apr 19 '12 at 12:48

TPete

2,049
4
24
26

Tahnks for your reply! But my requirement is that I want to roll back the all changes made by the queries from query2 to query7. so is there any alternate solution for this problem. – Param-Ganak Apr 19 '12 at 13:03
Well, to roll back a create table/database statement, you could just drop that table/database, right? – TPete Apr 19 '12 at 13:09

Why the rollback operation is not done after an exception came in my java code

2 Answers2