Connections and Threads

Question: which is the best way to manage JDBC Connections?

Issues:

1. Passing connection around as a parameter is a nuisance. (A Bigger point of contention is 'global variables' vs. 'passing parameters' style of coding. To some, the 'global variables' approach seamless object orientated)
2. I want to ensure that within the session I use the same connection object across requests.

Solution:

Point number one is best solved by using ThreadLocal.

Point number two can only be solved by saving the connection in HttpSession. That’s again is a source of contention. Reason being:

1.      You end up needing a database connection (and an active transaction) per concurrent *user* of your application (i.e. everyone who is logged on and has an active session) instead of a database connection per current *request*.

2.      The database connection would remain unavailable to other users

3.      Other users have to wait to access rows are in the middle of updating, and the transaction has locked

4.      What if there are multiple requests for the same session?  This can happen more often than you might think. In any such scenario, it is not safe to assume that a Connection retrieved from a session is only being used by a single thread.  You'll need to ensure that you don't try to retrieve it from more than one thread simultaneously. A couple of common causes:

a.       Framed presentation, because the browser will often trigger multiple simultaneous requests, and they are all participating in the same session.

b.      User submits a request that starts a long SQL query, presses STOP, and then submits a second request while the first one is still running.

Nevertheless there are situations where the transaction spans across request.

Let’s discuss the merits of using ThreadLocal:

This is what I got from one of the mailing lists: (http://marc2.theaimsgroup.com/?l=tomcat-user&m=103609047904032&w=2)
The notion of binding a Connection to a particular Thread using a ThreadLocal is a pretty good idea. The big advantage to my mind is that you can use the Connection from any level of the call tree without passing it around as a parameter. What you need to keep in mind is that you must reset the ThreadLocal before the Thread leaves your control. In other words:

• At the beginning of your processing, check out a Connection from a connection pool and bind it to the ThreadLocal.
• Throughout processing, the ThreadLocal can be used to obtain access to the Connection.
• At the end of your processing, close the connection (which returns it to the connection pool) and reset the ThreadLocal.

If you need to maintain database state across multiple HTTP requests, a slight modification is needed: before you check a Connection out from the connection pool, look in the HttpSession to see whether there is a Connection there already. If there is, bind that Connection to the ThreadLocal. Otherwise, obtain a new Connection and put it both in the HttpSession and in the ThreadLocal.

In the context of servlets, one good way to structure this would be to create a ServletWithConnection class (extending HttpServlet), and have its service() method look something like this:

protected static ThreadLocal tl = new ThreadLocal();

protected void service (……) {

Connection conn = connPool.getConnection();

tl.set(conn);

super.service();

tl.set(null);

conn.close();

}

Then you can extend this servlet whenever you need a database connection, override doGet, doPost, whatever, as normal, and access the Connection via tl.get() whenever you need it.

Incidentally, Martin Fowler talks about this in his new book on enterprise architecture patterns — look at the "registry" pattern.

The following are the points against threadlocal approach:

1. If you're going to keep a Connection in the session (across requests) at all, you're wasting your time bothering with a connection pool — you’ve already said you're willing to leave an expensive resource (an open connection to the database) allocated to a particular user in between requests, even if the user went to lunch. You might as well just open a connection when the session is created, and close it when the session completes.
2. The pattern mentioned above (extending HttpServlet) does not violates these principles (because it gives the connection back before the request is completed), but it does do unnecessary work if you're running on a servlet container that provides data sources via JNDI resources — and that means every J2EE appserver in the world, plus Tomcat 4.x and 5.x, and I'm sure a bunch of others.
3. It’s overkill if many calls don't need to use the database for anything. I guess you could be lazy and only get the connection from the pool and bind it to the threadlocal just before you use it for the first time.
4. You'd still need to go straight to the connection pool, if you needed to use a second connection in the same thread (e.g. for logging. you still want to log even if there is a rollback).