Why I Don’t Need Asynchronous JDBC (Yet)

Lukáš Křečan considered whether he needs an asynchronous JDBC API in Java and put his conclusion in Why I Don’t Need Asynchronous JDBC. I’d like to add a few points.

First, let’s use a simple example to demonstrate the Servlet 3.0 API with asynchronous support that Lukáš mentions. It plays an important role in the discussion that follows.

@WebServlet(name = "AsyncServlet", urlPatterns = { "/AsyncServlet" }, asyncSupported=true)
public class AsyncServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) {
AsyncContext ac = request.startAsync(request, response);
ac.start(new Worker(ac));

}
public static class Worker implements Runnable {
private final AsyncContext ac;
public Worker(AsyncContext ac) {
this.ac = ac;
}
@Override
public void run() {
HttpServletResponse response = (HttpServletResponse) ac.getResponse();
try {
response.setContentType(“text/html;charset=UTF-8”);
PrintWriter out = null;
try {
out = response.getWriter();
out.println(“<html>”);

out.println(“<body>”);
out.println(“<h1>Servlet AsyncServlet output</h1>”);
for(int i = 0; i < 10000; i++) {
out.println(“<p>Asynchronously generated response :-)</p>”);
out.flush();
try {
Thread.sleep(200);

} catch (InterruptedException e) {}
}
out.println(”</body></html>”);

} catch (IOException e) {

} finally {
out.close();
}
} finally {
ac.complete();
}
}
}
}

A little explanation. On line 5 we tell the container that the request will be handled asynchronously. On line 6 we start asynchronous execution. In this case it uses a thread from the servlet container’s pool. Of course, that won’t always suit us. We can create a regular ThreadPoolExecutor and take threads from there.

After line 6, execution starts on a new thread, as shown in the Worker class. The original servlet container thread finishes the doGet method and can be used to handle another HTTP request. The run method of Worker therefore runs on another thread. We use the AsyncContextu we passed along as our channel to the servlet container to construct a response. The call to complete on line 40 signals that the asynchronous work is done.

Now to the heart of the matter. Where does the much-invoked asynchronous JDBC API fit in? Imagine a slight variation on the example that calls the JDBC we already know. We only need the Workerclass.

public static class Worker implements Runnable {
  private final AsyncContext ac;
  
  public Worker(AsyncContext ac) { 
   this.ac = ac;
  }

@Override
  public void run() {
   Connection conn = getConnectionFromSomewhere();
   PreparedStatement ps =  conn.prepareStatement(“insert into orders (a) values (‘a’)”);
   ps.executeUpdate();
      ac.dispatch(“/dekujeme-za-nakup.jsp”);   
  } 
 }

What happens? The servlet container gets its thread back and can handle other HTTP requests, but the thread executing run will block at lines 10 and 12. Jíra Mareš has a point that a database connection pool can help on line 10. The thread will still block by line 12 at the latest.

Suppose we run an online shop and the user has just made a purchase. The request goes out and the code gets stuck at line 12. We probably don’t want the user’s browser UI to freeze for ten seconds. We’d prefer to show a page thanking them for their purchase and perhaps suggest something from a sister store.

Without an asynchronous JDBC API, we have to wrap both blocking calls (10 and 11) in an asynchronous call ourselves. The original run method then looks simply delightful.

@Override
public void run() {
 new Thread(){    
  public void run() {
   Connection conn = getConnectionFromSomewhere();
   PreparedStatement ps =  conn.prepareStatement(“insert into foo (a) values (‘a’)”);
   ps.executeUpdate();
  }    
 }.start();   
 ac.dispatch(“/dekujeme-za-nakup.jsp”);
}

I’m not arguing that JDBC is a cure-all. It’s only the tip of the iceberg. It would at least be a good start toward sparing application developers from littering their code this way. Asynchronous JDBC won’t bring a dramatic performance boost on its own. Lukáš and I agree about that. It might make the UI feel more responsive. For an effect on performance, you’d need a driver and database that support asynchronous calls.

And by the way, this isn’t so far-fetched. See below.

• Building Reliable, Asynchronous Database Applications Using Service Broker (Microsoft SQL Server 9.0 Technical Articles)
• Asynchronous Database Connectivity in Java
• Asynchronous DB: DBSlayer & HTTP