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 {
      conn = DriverManager.getConnection(jdbcurl);
      stmt = conn.createStatement();
      int rowsPopulated1 = 
        stmt.executeUpdate("INSERT INTO PEOPLE VALUES
         (01, 'Raja', 'Chandrashekhar')");
      int rowsPopulated2 = 
        stmt.executeUpdate("INSERT INTO PEOPLE VALUES
         (02, 'Mike', 'Russell')");
    catch(SQLException sqle) {
      System.out.println("Caught Exception: " 
      try {
        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 {
        catch(SQLException sqle) {
          System.out.println("Could not close 
          connection: " + sqle.getMessage());
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? 


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.
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.


Popular posts from this blog

Five Reasons Why Your Product Needs an Awesome User Guide

Photo Credit: Peter Merholz ( Creative Commons 2.0 SA License ) A user guide is essentially a book-length document containing instructions for installing, using or troubleshooting a hardware or software product. A user guide can be very brief - for example, only 10 or 20 pages or it can be a full-length book of 200 pages or more. -- As engineers, we give a lot of importance to product design, architecture, code quality, and UX. However, when it comes to the user manual, we often only manage to pay lip service. This is not good. A usable manual is as important as usable software because it is the first line of help for the user and the first line of customer service for the organization. Any organization that prides itself on great customer service must have an awesome user manual for the product. In the spirit of listicles - here are at least five reasons why you should have an awesome user manual! Enhance User Satisfaction In my fourteen years as a

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

Planning a User Guide - Part 3/5 - Co-ordinate the Team

Photo by  Helloquence  on  Unsplash This is the third post in a series of five posts on how to plan a user guide. In the first post , I wrote about how to conduct an audience analysis and the second post discussed how to define the overall scope of the manual. Once the overall scope of the user guide is defined, the next step is to coordinate the team that will work on creating the manual. A typical team will consist of the following roles. Many of these roles will be fulfilled by freelancers since they are one-off or intermittent work engagements. At the end of the article, I have provided a list of websites where you can find good freelancers. Creative Artist You'll need to work with a creative artist to design the cover page and any other images for the user guide. Most small to mid-sized companies don't have a dedicated creative artist on their rolls. But that's not a problem. There are several freelancing websites where you can work with great creative ar