Should JDBC connection handles be per-app, per-thread, or per-query?

Question

Let's say we've got a web application or web service on an application server supporting JDBC connection pooling.

Should I be grabbing a new Connection on a per-thread or per-query basis?

Thanks!

Answer 1

Hopefully you are grabbing them on a per-transactional-unit-of-work basis.

Per query implies that you never have any logical unit of work in your system that spans more than a single query. (Maybe that's true, but you still might want to think about the future!)

Per-thread (which I assume to mean request-scoped, rather than for the entire life of the thread?) will probably result in holding them for longer than absolutely necessary, but it does allow you to manage transactions much better. (and it's how plenty of leading frameworks have worked or did work for a long time. A pattern known as Open Entity Manager In View, if you'd like to do some google-fu on it)

Assigning it indefinitely to a single thread means your max number of active request processors is capped at the max size of your database pool, which is a definite failure in scalability.

Answer 2

per-thread

Each new request will grab a new connection (new thread = new request). There is no need for getting a new connection for each query as after each query the connection can be reused.