Friday, January 19, 2007

JDBC transaction rollback

We are advised to rollback a JDBC transaction in the catch block that handles SQLException, just as shown in the code below. But what if a runtime exception is thrown while after midway in the transaction? Control will never go to the catch block and the transaction will never be rolled back.

 


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class UnderstandingJdbcTransactions {

  public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    String jdbcurl = "jdbc:derby:testdb;create=true";
    try {
      Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
      conn = DriverManager.getConnection(jdbcurl);
      conn.setAutoCommit(false);
      stmt = conn.createStatement();
      int rowsPopulated1 = 
        stmt.executeUpdate("INSERT INTO PEOPLE VALUES
         (01, 'Raja', 'Chandrashekhar')");
      //WHAT IF THERE ARE MORE SQL UPDATES BEING EXECUTED 
      //HERE AND A RUNTIME EXCEPTION IS THROWN
      int rowsPopulated2 = 
        stmt.executeUpdate("INSERT INTO PEOPLE VALUES
         (02, 'Mike', 'Russell')");
      conn.commit();
      conn.setAutoCommit(true);
    catch(SQLException sqle) {
      System.out.println("Caught Exception: " 
        sqle.getMessage());
      try {
        conn.rollback();
        System.out.println("Transaction rolled back");
      catch (SQLException e) {
        System.out.println("Could not rollback 
          transaction: " + e.getMessage());
      }
    catch (ClassNotFoundException e) {
      System.out.println("Could not find the database 
        driver class");
    finally {
      if(conn != null) {
        try {
          conn.close();
        catch(SQLException sqle) {
          System.out.println("Could not close 
          connection: " + sqle.getMessage());
        }
      }
    }
  }
}
Java2html
 
There are two ways to handle this issue. The first is to explicitly set a flag in this class if the transaction commits. In the finally block we should check the flag. If it has not been set, it means that the transaction could not be commited and must be rolled back. Another way is to catch 'Exception' after 'SQLException'. If a runtime exception is thrown we will handle it in the catch block by rolling back the transaction and rethrowing the RuntimeException.
 
What do you think? Is this a workable solution? Which os the two approaches would you favor? 

2 comments:

Jens said...

A third option would be to always do a rollback in the finally block - doing a rollback after doing commit doesn't change anything. I guess there is some sort of performance issue to consider but I doubt it's significant.

Parag said...

@Jens, That is also an option. I did not think of it when I wrote this post, but you are right... we can rollback in finally. It results in fewer lines of code, with a slight runtime impact.

--
Thanks
Parag