Skip to main content

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? 
Labels:

Comments

Unknown 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.
7:32 PM
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
4:55 PM
Post a Comment

Popular posts from this blog

My HSQLDB schema inspection story

This is a simple story of my need to inspect the schema of an HSQLDB database for a participar FOREIGN KEY, and the interesting things I had to do to actually inspect it. I am using an HSQLDB 1.8 database in one of my web applications. The application has been developed using the Play framework , which by default uses JPA and Hibernate . A few days back, I wanted to inspect the schema which Hibernate had created for one of my model objects. I started the HSQLDB database on my local machine, and then started the database manager with the following command java -cp ./hsqldb-1.8.0.7.jar org.hsqldb.util.DatabaseManagerSwing When I tried the view the schema of my table, it showed me the columns and column types on that table, but it did not show me columns were FOREIGN KEYs. Image 1: Table schema as shown by HSQLDB's database manager I decided to search on StackOverflow and find out how I could view the full schema of the table in question. I got a few hints, and they all pointed to ...
Post a Comment
Read more

Commenting your code

Comments are an integral part of any program, even though they do not contribute to the logic. Appropriate comments add to the maintainability of a software. I have heard developers complain about not remembering the logic of some code they wrote a few months back. Can you imagine how difficult it can be to understand programs written by others, when we sometimes find it hard to understand our own code. It is a nightmare to maintain programs that are not appropriately commented. Java classes should contain comments at various levels. There are two types of comments; implementation comments and documentation comments. Implementation comments usually explain design desicisions, or a particularly intricate peice of code. If you find the need to make a lot of implementation comments, then it may signal overly complex code. Documentation comments usually describe the API of a program, they are meant for developers who are going to use your classes. All classes, methods and variables ...
Post a Comment
Read more

Inheritance vs. composition depending on how much is same and how much differs

I am reading the excellent Django book right now. In the 4th chapter on Django templates , there is an example of includes and inheritance in Django templates. Without going into details about Django templates, the include is very similar to composition where we can include the text of another template for evaluation. Inheritance in Django templates works in a way similar to object inheritance. Django templates can specify certain blocks which can be redefined in subtemplates. The subtemplates use the rest of the parent template as is. Now we have all learned that inheritance is used when we have a is-a relationship between classes, and composition is used when we have a contains-a relationship. This is absolutely right, but while reading about Django templates, I just realized another pattern in these relationships. This is really simple and perhaps many of you may have already have had this insight... We use inheritance when we want to allow reuse of the bulk of one object in other ...
3 comments
Read more