blogger templates blogger widgets
This is part of a list of blog posts.
To browse the contents go to

JDBC web application - (shared) connections using DriverManager, connection rollbacks and other scenarios

Main purpose of creating Web applications is to serve user requests concurrently.

Let's try emulating concurrency.

Requests from different browser windows on the same machine are considered different. So opening 2 browser windows and hitting the url simultaneously will do the trick.

Since we cannot use 2 windows simultaneously (at the exact same time), we modify our program as follows:

package com.prodapps;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class ProductServlet
 */

@WebServlet(description = "This is primary product servlet", urlPatterns = { "/ProductServlet" })
public class ProductServlet extends HttpServlet {
 private Connection conn=null;
 private static final long serialVersionUID = 1L;
       

    public ProductServlet() {
        super();
       
    }


 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  System.out.println("doGet");
  response.setContentType(request.getContentType());
  String caseID = request.getParameter("caseID");
  try {
   conn = createConnection();
   conn.setAutoCommit(false);
   if(caseID.equals("1")){
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("insert into products values('3','htc')");
    System.out.println("3");
    try {
     Thread.sleep(100);
    } catch (InterruptedException e) {
     System.out.println("interrupted");
    }
    stmt.executeUpdate("insert into products values('4','nexus')");
    System.out.println("4");
    try {
     Thread.sleep(10);
    } catch (InterruptedException e) {
     System.out.println("interrupted");
    }
    conn.commit();
   }else{
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("insert into products values('5','galaxy')");
    System.out.println("5");
    try {
     Thread.sleep(2000);
    } catch (InterruptedException e) {
     System.out.println("interrupted");
    }
    
    stmt.executeUpdate("insert into products values('6','xolo')");
    System.out.println("6");
    try {
     Thread.sleep(10);
    } catch (InterruptedException e) {
     System.out.println("interrupted");
    }
    conn.rollback();
   }
   RequestDispatcher dispatch = request.getRequestDispatcher("/index.jsp");
   dispatch.forward(request, response);
  } catch (SQLException e) {
   e.printStackTrace();
  }finally {
   closeConnection();
  }
  
 }

 public Connection createConnection() throws SQLException{
  if(conn==null||conn.isClosed()){
    try {
          Class.forName("oracle.jdbc.OracleDriver");
          conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "SYSTEM", "password");
    }catch(SQLException e){
     e.printStackTrace();
    } catch (ClassNotFoundException e) {
    e.printStackTrace();
   }
  }
  return conn;
 }
 public void closeConnection(){
  if(conn!=null){
   try {
    conn.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }

}

Note that we have changed the query to insert values into the table.

In this scenario we don't have any use of Product.java nor the jsp file since our focus is on how JDBC behaves for parallel requests.

You can download this sample here


We read in a request parameter (caseID)and based on the parameters value we insert values into the table.
If the caseID == 1 then we insert (3, htc) and (4, nexus) and commit(save).
Else we insert(5, galaxy) and (6, xolo) and then rollback(undo).

By default all insertion/updation are auto-committed, ie. we don't have to call commit to save it to the database.

But in real-time transactions when there are multiple inserts/updates, it has to follow ACID principle.

This will make sense if we take a real-time scenario like money transfer between bank accounts. Suppose money needs to be transferred from account A to B.

We have 2 operations -
1. Deduct amount from A
2. Add amount to B
Now if any one operation fails and the other succeeds then the data is spoiled.

How connection rollback works?

In JDBC, transaction is a logical unit of work.
A transaction involves multiple SELECT, INSERT, UPDATE and DELETE statements.
All transactional work is handled at the Connection object level.
All Statement objects under a connection are a part of the transaction.

This means is that if an application creates three Statement objects and uses each object to make changes to the database, when a commit or rollback call happens, the work for all three statements either becomes permanent or is discarded. The commit and rollback SQL statements are used to finalize transactions when working purely with SQL.

To simulate our concurrent situation we open 2 instances of our favorite browser.


Hitting
http://localhost:8081/ProductApp/ProductServlet?caseID=2
first and then immediately
http://localhost:8081/ProductApp/ProductServlet?caseID=1
simulates the concurrent situtation.


We get a Closed Connection - SQL exception if you see the logs.

Let's check the table.
SQL> select * from products;

ID TITLE
---------- -------------------------
1 ipad
2 galaxytab
5 galaxy
3 htc
4 nexus

The record
5 galaxy
isn't supposed to be there.

What happened?

Let's analyze the logs to see what happened.

doGet
5
doGet
3
4
SEVERE: Servlet.service() for servlet jsp threw exception
java.lang.NullPointerException
at org.apache.jsp.index_jsp._jspService(index_jsp.java:77)
...
at com.prodapps.ProductServlet.doGet(ProductServlet.java:77)
SEVERE: Servlet.service() for servlet [com.prodapps.ProductServlet] in context with path [/ProductApp] threw exception [java.lang.NullPointerException] with root cause
java.lang.NullPointerException
at org.apache.jsp.index_jsp._jspService(index_jsp.java:77)
...
at com.prodapps.ProductServlet.doGet(ProductServlet.java:77)
java.sql.SQLException: Closed Connection
...
at com.prodapps.ProductServlet.doGet(ProductServlet.java:67)

When the servletcontainer, like Apache Tomcat starts up, it will deploy and load all web applications. When a web application gets loaded, the container creates servletcontext object and then the webapp's web.xml will be parsed and every Servlet, Filter and Listener found in web.xml will be created once and kept in server's memory. The respective constructors of those objects are run.

The child element(of element) in web.xml indicates that this servlet should be loaded (instantiated and have its init() called) on the startup of the web application. The optional contents of these element must be an integer indicating the order in which the servlet should be loaded.

value >= 0 means that the servlet is loaded when the web-app is deployed or when the server starts.
value < 0 means servlet is loaded whenever the container feels like - i.e it could be when the first request comes or when the container starts. When is not specified, the container initializes the servlet when the first request comes.

Is it possible to write a argument constructor in a servlet?

yes, you can but it is useless since you cannot pass arguments since Java lacks proper constructor via reflection.
"According to the servlets (2.4) specification, the servlets are instantiated by the servlet engine by invoking the no-arg constructor."

Only one instance of servlet per web-application runs in a container but multiple threads are created to handle the concurrent user requests.

When first request (caseID=2) hits the ProductServlet, a thread is picked from a pool created by the container or a new thread is created and started. The thread executes doGet method.

Inside doGet we create a connection object if it doesn't exists else we re-use the connection that's existing.

Since connection object is declared as a instance variable, only one reference exits for the servlet object. And thus there is only one reference for the whole web application too because there is only one instance of servlet per web application.

The code executes the "else" block and inserts (5, galaxy). Then the tread sleeps for 1 sec.

Meanwhile the user hits the url again with request parameter, caseID=1. Another thread is created and the doGet method runs. Now, conn object exits already and it's reused.

(3, htc) and (4, nexus) are entered into the database with a 10 millisecond gap in-between. The values are saved into db by calling commit() and then the doGet method completes after closing the connection.

Now the first thread resumes. And it runs

stmt.executeUpdate("insert into products values('6','xolo')");
But the connection object is already closed. So, our program throws a closed connection exception or sometimes a null pointer exception also.

Now, let's try the same example by sleeping the first condition 5 seconds.

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  System.out.println("doGet");
  response.setContentType(request.getContentType());
  String caseID = request.getParameter("caseID");
  try {
   conn = createConnection();
   conn.setAutoCommit(false);
   if(caseID.equals("1")){
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("insert into products values('3','htc')");
    System.out.println("3");
    try {
     Thread.sleep(5000);
    } catch (InterruptedException e) {
     System.out.println("interrupted");
    }
    stmt.executeUpdate("insert into products values('4','nexus')");
    System.out.println("4");
    try {
     Thread.sleep(10);
    } catch (InterruptedException e) {
     System.out.println("interrupted");
    }
    conn.commit();
   }else{
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("insert into products values('5','galaxy')");
    System.out.println("5");
    try {
     Thread.sleep(2000);
    } catch (InterruptedException e) {
     System.out.println("interrupted");
    }
    
    stmt.executeUpdate("insert into products values('6','xolo')");
    System.out.println("6");
    try {
     Thread.sleep(10);
    } catch (InterruptedException e) {
     System.out.println("interrupted");
    }
    conn.rollback();
   }
   RequestDispatcher dispatch = request.getRequestDispatcher("/index.jsp");
   dispatch.forward(request, response);
  } catch (SQLException e) {
   e.printStackTrace();
  }finally {
   closeConnection();
  }

Hitting
http://localhost:8081/ProductApp/ProductServlet?caseID=2
first and then immediately
http://localhost:8081/ProductApp/ProductServlet?caseID=1
Again throws closed-connection exception as expected.

doGet
5
doGet
3
6
java.sql.SQLException: Closed Connection
Which means after (5, galaxy) is entered, the 2nd request comes which reused conn object. Executes and adds (3, htc) and then sleeps for 5 seconds. JVM swaps the sleeping thread with our first thread that is now ready to run. First thread executes and adds (6, xolo) and rollbacks all previous transactions and closes the connection.

Now the 2nd thread continues and tries to execute (4, nexus) but the connection is closed and hence the sql exception.

Let's check the table.
SQL> select * from products;

ID TITLE
---------- -------------------------
1 ipad
2 galaxytab

In both the above scenarios, you may not get the same output as above as how threads compete and run among themselves cannot be accurately predicted.


Let's see what happens if connection is initialized in init().

@WebServlet(description = "This is primary product servlet", urlPatterns = { "/ProductServlet" })
public class ProductServlet extends HttpServlet {
 private Connection conn=null;
 private static final long serialVersionUID = 1L;
       

    public ProductServlet() {
        super();
    }
    
    @Override
    public void init() throws ServletException {
     super.init();
     System.out.println("init");
      try {
         Class.forName("oracle.jdbc.OracleDriver");
         conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "SYSTEM", "password");
         conn.setAutoCommit(false);
   }catch(SQLException e){
    e.printStackTrace();
   } catch (ClassNotFoundException e) {
   e.printStackTrace();
  }
 }


 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  System.out.println("doGet");
  response.setContentType(request.getContentType());
  String caseID = request.getParameter("caseID");
  try {
   if(caseID.equals("1")){
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("insert into products values('3','htc')");
    System.out.println("3");
    try {
     Thread.sleep(100);
    } catch (InterruptedException e) {
     System.out.println("interrupted");
    }
    stmt.executeUpdate("insert into products values('4','nexus')");
    System.out.println("4");
    try {
     Thread.sleep(10);
    } catch (InterruptedException e) {
     System.out.println("interrupted");
    }
    conn.commit();
   }else{
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("insert into products values('5','galaxy')");
    System.out.println("5");
    try {
     Thread.sleep(2000);
    } catch (InterruptedException e) {
     System.out.println("interrupted");
    }
    
    stmt.executeUpdate("insert into products values('6','xolo')");
    System.out.println("6");
    try {
     Thread.sleep(10);
    } catch (InterruptedException e) {
     System.out.println("interrupted");
    }
    conn.rollback();
   }
   RequestDispatcher dispatch = request.getRequestDispatcher("/index.jsp");
   dispatch.forward(request, response);
  } catch (SQLException e) {
   e.printStackTrace();
  }
  
 }

 @Override
 public void destroy() {
  super.destroy();
  System.out.println("destroy");
  try {
   conn.close();
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
}

You again get indeterminate/incorrect results because init runs just once and the connection object is shared across requests/threads.

SQL> select * from Products;

ID TITLE
---------- -------------------------
1 ipad
2 galaxytab
5 galaxy
3 htc
4 nexus

As we have seen sharing connection object across transactions is evil. It leads to indeterminate behaviour and corrupts the data.

Solution?

Create a new connection for every transaction. In other words, each request uses it's own connection object.

Continue reading: JDBC web application - unique connections using DriverManager

No comments:

Post a Comment