0

Let’s say there is this code which lives in a server side backend:

class Foo {
    void doSomething() {
        try (Connection c = dataSource.getConnection()) {
            executeSQL(c);
            externalApiCall(); // this can be slow
            executeSQL(c);
        }
    }
}

The problem here is that externalApiCall() can be very slow, and it keeps a lot of database connections open (even though it’s not used) and it can lead to running out of the maximum number of the connections. It will be harmful to other parts of my app which don’t depend on any external API, and I want to avoid this.

The code above is simplified and in reality I use something like a ServletFilter which puts Connection into ThreadLocal when it receives a http request, so it’s difficult to change the overall mechanism which opens a Connection at the beginning of business logic.

So my question is: isn’t there any nice connection pooling library, a wrapper or something that can handle such a situation nicely? For example, something which opens a real database connection only when a Statement object is created and close the connection afterwards automatically when the Statement is closed. Currently I use Tomcat DBCP.

Kohei Nozaki
  • 1,154
  • 1
  • 13
  • 36
  • Apache DBCP or the Tomcat version both do pooling, but if the intervening method call is slow you may as well use two connection blocks. – user207421 May 28 '19 at 03:20
  • Your use case is pretty specific. If you know `externalApiCall` is going to take a long time, I suggest that you perform it without having a connection open, or manually open/close connections at the right time. Doing what you suggests will only lead to hard to understand code with a lot of magic that could break expectations of people (eg regarding transactionality etc). – Mark Rotteveel May 28 '19 at 09:40
  • I wish I could do that.. as I explained in the question it’s difficult unfortunately. as for transaction, I’m looking for something which can handle that based on setAutoCommit(false).. – Kohei Nozaki May 28 '19 at 09:47
  • @KoheiNozaki If you expect this to work with auto-commit disabled then you probably won't find anything standard. JDBC only supports a single transaction per connection, so reusing a connection while your code is waiting is simply not possible with plain JDBC (at least not with breaking transactional behaviour), so you would need to do something outside of JDBC, and databases that allow multiplexing multiple transactions on a single connection also aren't very common. – Mark Rotteveel May 28 '19 at 12:12
  • The standard solution is to move the long-running task outside of request processing, using some form of message queue / background thread. This means that the results of the long-running task will not be available to return ot the user. Are you prepared to do this? – guest May 28 '19 at 14:53
  • I just wish I could do that.. – Kohei Nozaki May 28 '19 at 22:38
  • 1
    So, nothing can change but you want different behavior? – guest May 29 '19 at 10:39

1 Answers1

0

Use cp3 connection pooling.

The property max_idle_time may satisfy your requirements here.

  • maxIdleTime

Default: 0

Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire.

For more info, refer https://www.mchange.com/projects/c3p0/

Coder
  • 54
  • 6
  • Isn't this property applied to only connections in the pool? My question is about the connections which are acquired by application code and are being idle. To me the property sounds like it doesn't apply to the acquired connections. – Kohei Nozaki May 28 '19 at 00:13