Day One SHAREABLE JDBC connection setting

I do not know why I didn't start a blog on this topic earlier! This is what I do day in and day out so having an outlet where I can describe and discuss various scenarios is going to be helpful to me.

So, for our first topic we are going to talk about a common malfeasance in production performance that typically negatively affects any application that runs in the WebSphere Application Server environment. Okay, maybe not all that common but I've seen it three times now and obviously someone needs to start paying attention.

Inside the application EAR file there are deployment descriptors for the JDBC connection pool that refer to SHAREABLE and UNSHAREABLE connections. While there is plenty of documentation on this particular deployment descriptor there seems to be little understanding of how this particular setting works. BTW the default setting is SHAREABLE which is where most problems seem to stem from.

Now, what I'm writing here is not gospel so you will need to understand your application and how it uses JDBC connections particularly if there are transactional UOW involved. However, for the other 90% of you that are doing simple SQL queries and a few inserts here and there you will want to pay close attention... change the descriptor to UNSHAREABLE and run your performance test suite (you do have a performance load test, right?) and notice the change in behaviour of the JDBC connection pools. If you did this right you should see fewer connections being used.

This is because the default setting of SHAREABLE does not return the connection to the pool when you call close(). In fact, that connection is held with the thread until the thread finishes processing the request. This is an optimization that some people may need but the majority do not.

So the next question people always ask me is "why is this the default setting?" Well, heck, someone had to decide on what to use as a default and they picked one. But just because something is a default setting does not necessarily mean it is the right setting for your application. I think the JVM defaults to 128M for a maximum and you can pretty sure that probably is not the right maximum for 99% of the J2EE-based applications out there!