When NOT to use database connection pooling in Java? [closed]

Asked 22/12, 2016 at 14:32 Answered 22/12, 2016 at 18:17

Solved java database jdbc pool single-threaded

I can find a lot of questions about how to use connection pooling and why it's a good idea, but I'm wondering if I actually need it.

I'm creating a server application and although it is multi-threaded, I've been careful that only a single thread ever accesses my database connection.

So is there any point in using a connection pool?

Can't I just open a connection to my database at the start of the life-cycle and use that one connection forever, or will it time-out if inactive for too long?

Do I absolutely have to call close() on my connection after I do something with it, or is it enough call close() on the ResultSet and/or Statement?

Adabelle answered 22/12, 2016 at 14:32 Comment(2)

Check this out :- #16093554. – Perhaps 22/12, 2016 at 14:49

"closed as primarily opinion-based", Why? If there are researching that give examples when it is useful to not make use of connection pools, than it isn't opinion-based. – Bellbottoms 1/11, 2018 at 21:15

What is a "database connection", really? It is a session with your database, and as such:

There is a client side and server side session state
There is a transaction associated with that session

Now, since your client application is multi-threaded, I suspect there is only an extremely limited number of cases where your setup makes sense, namely:

You don't use the session state, not even inadvertently (and that may be vendor specific)
You don't use transactions and always auto-commit
Even with auto-committing, there may be race conditions, so you make sure those don't happen
You're very sure that each statement only takes very little time, freeing resources immediately

In all other cases, you want one connection per client thread, or in a reactive/async environment, you at least want one connection per isolated database interaction. And because it's expensive to create new connections (i.e. initialise server side session state, etc.) people simply use connection pools. In fact, a connection pool can have only one connection inside of it (as per your requirement), and it is still a good abstraction for you to use. So why write your own connection pool, instead?

Regarding your specific questions:

So is there any point in using a connection pool?

Except for very trivial cases (see above), it is usually good to have a connection pool.

Can't I just open a connection to my database at the start of the life-cycle and use that one connection forever, or will it time-out if inactive for too long?

You could, of course. There's usually a setting in JDBC drivers or other client libraries to prevent these time outs, or to reconnect.

The perfect use-case for such an approach is a migration script, or a batch script, or a simple test script, or a simple Swing application, etc. All of these don't need a connection pool.

Do I absolutely have to call close() on my connection after I do something with it, or is it enough call close() on the ResultSet and/or Statement?

You should call close() on connections obtained from DataSource.getConnection() (e.g. when the connection pool implements DataSource).

You don't have to call close() on connections whose lifecycle you manage on your own.

Cobber answered 22/12, 2016 at 14:46 Comment(6)

Thanks for the answer, however, I'm still not sure if using a pool makes sense. "There is a transaction associated with that session" - Can't a session have multiple transactions associated with it if you auto-commit? Also, this should be considered a synchronous, single-threaded, imperative environment - the database is read from/written to as little as possible, but when it is, I need to block until the transaction is complete – Adabelle 22/12, 2016 at 15:48

@MikkelRavnholtSimonsen: A session / connection can have multiple serial transactions, yes. It doesn't matter if you're auto committing or if you're using multi-statement transactions. In fact, when you're using a connection pool, that's precisely what happens. A client process checks out a connection, uses it to run a (several?) transactions, and returns it to the pool such that another client process can use it. That's exactly what you need, even if you want only one connection. So why bother implementing a connection pool yourself? – Cobber 23/12, 2016 at 8:2

"So why bother implementing a connection pool yourself?" - Well I wanted to avoid implementing a connection pool all together. I cannot see the argument for using a connection pool in what is practically a single-threaded environment (I know I said the server is multi-threaded, but there's single thread dedicated to talking to the db). I'm leaning towards just using one because everything I can find says to just use a connection-pool, but I'm not convinced it isn't just unnecessary overhead. – Adabelle 23/12, 2016 at 12:1

I think I said everything. There are use-cases where not using a pool makes sense. They're rare, because people usually just don't want to risk the entire application blocking on that single thread talking to the DB. But if you're sure, go for it – Cobber 23/12, 2016 at 12:3

Okay, thanks for taking the time to help me understand this whole thing a little better, maybe I should embrace the connection pools and try to rethink my design so I can use the benefits they have. – Adabelle 23/12, 2016 at 12:12

Well, you cannot lose much (except for having one more dependency). But at the very least, you can have an additional abstraction that guarantees your "only one thread accessing the single connection at a time" semantics" – Cobber 23/12, 2016 at 12:14

Database connections are expensive to create, so connection pools come into play when you have a large number of requests that generally take a short amount of time. You reuse a prior database connection, in a new context to avoid the cost of setting up a new database connection for each request.

The primary reason to avoid using database connections is that you're application's approach to solving problems isn't structured to accommodate a database connection pool. With pools, certain optimizations that make sense in making a single shared connection act with high performance might have to be undone and reverified to be correct to adopt a pool and use the pool effectively.

In addition, while many people worry about the latency of the connection, and a pool reduces that by having a group of connections at the ready; the fact that your database is now a host to multiple connections means that the "resting" state of your application will use more resources. Generally this is not enough to make a large difference in most environments.

Pooled connections sometimes enable more reordering of queries and commits, and code that was not written defensively to handle database state as the database changes might require additional rework and verification. Keep in mind that this is not a weakness of the connection pool, but more so latent bugs in the database handling logic of a database (as all databases should have been considered shared resources and have had these kinds of logic checks in place).

Apart answered 22/12, 2016 at 14:56 Comment(0)

Can't I just open a connection to my database at the start of the life-cycle and use that one connection forever, or will it time-out if inactive for too long?

Connections sometimes get stale, disconnect, fail.
Many connection pools are able to automatically check connections' state, close stale/failed connections and reopen new ones if needed.
Consider a case when the network breaks for a moment or someone restarts a database server.
The connection pool automatically restores connections to the database after a failure - you don't have to restart your application afterward.
You can implement such a feature in your dao class ... but the pool already has this, it's easier to just use it.
You can configure a poll with just 1 single connection if you don't want more.

Swainson answered 22/12, 2016 at 18:17 Comment(0)

Recommended topics

Hot tags