JDBC Tutorials:commit or Rollback transaction in finally block

Http://skeletoncoder.blogspot.com/2006/10/jdbc-tutorials-commit-or-rollback.html

JDBC tutorials:commit or Rollback transaction in finally block

In the most of the JDBC books, the transaction management idiom, which is followed are, after executing the UPDATE statements commit, And if an SQLException is thrown, rollback.
That's,



Connection con = null;
try{
  con =//...
  Con.setautocommit (FALSE);

  Statement STMT1 = ...
  Stmt1.executeupdate ();

  Some operations

  Statement stmt2 = ...
  Stmt2.executeupdate ();

  Con.commit ();
  Con.setautocommit (TRUE);
}catch (SQLException e) {
  if (con!=null) {
    try{
      Con.rollback ();
    }catch (SQLException e) {
      Log the error ...
    }
  }
}

The similar structure is followed in the JDBC (TM) API
Tutorial and Reference from the Sun Microsystems. There is a look at Thetransactions Tutorial and the Sample code provided.

There is a severe problem with the this from commiting and rollback. The problem is our handling only the SQLException. What would happen if a runtimeexception occured after executing the first UPDATE statement but beforethe second update stat Ement?

The transaction is opened and neither commited nor rolled back. This would leave the data integrity into trouble. If We are reusing the same connection (as in most cases), and we commit the transaction in the next statements, we is int O Serious trouble. We have inconsitent data.

What's the solution?
Catch Exception instead of SQLException
A simpler and not recommended solution are, catch all the execeptions, including RuntimeException. Even now, what if a Error is thrown, say outofmemoryerror or some virtualmachineerror or something else? What ever happens in the code, we should either the database should be committed or rolledback. So, the worst thing is we should catch the Throwable class, instead of Exception.

Doesn ' t awkward,whenever we use transactions we should catch a Throwable class or atleast Exception class?

Use finally block
A Clean solution And yet simple solution are, use finally block. Since It is always guaranteed that the finally block would be executed even if any Exception was thrown or even when the M Ethod is returned.




Connection con = null;
Boolean success = false;
try{
  con =//...
  Con.setautocommit (FALSE);

  Statement STMT1 = ...
  Stmt1.executeupdate ();

  Some operations

  Statement stmt2 = ...
  Stmt2.executeupdate ();

  Success = true;

}catch (SQLException e) {
  Success = false;
}finally{
  if (con!=null) {
    try{
      if (success) {
        Con.commit ();
        Con.setautocommit (TRUE);
      }else{
        Con.rollback ();
      }
    }catch (SQLException e) {
      Log the error ...
    }
  }
}

JDBC Tutorials:commit or Rollback transaction in finally block