1

I have a program that performs frequent requests to a database using a single-threaded workflow. The requests require obtaining connections within a second or less of each other, but one connection should always be closed before a new one is opened.

However, over time my debugging shows that the connections rack up rather quickly. It seems that connections start multiplying, and eventually reach the point where the program hangs because the connection pool's maximum (currently using BoneCP) is filled. I have the maximum set at 15, but it had similar behaviour when it was at the default number (8 I believe).

I am using try-with-resource statements, similar to the one below, to close the connections.

//Connection example
//Connection pool is configured.
DataSource source = new DataSource();
try (Connection con = source.getConnection();
        PreparedStatement stmt = con.prepareStatement(query) {
    //Do SQL stuff
} catch (SQLException e) {
    //Handle Exception
}

If I understand try with resources right, this should automatically close the connection, and return it to the pool. Since this didn't work, I tried adding a con.close() statement in an attempt to better handle the connections. This quantitatively seemed to help, but I'm not sure it did anything in reality.

Why are so many connections being created, and how can I better manage my connections so I don't create so many?

DonyorM
  • 1,760
  • 1
  • 20
  • 34
  • If you are connecting so often, why not use a single persistent connection? – Brian A. Henning Nov 12 '15 at 16:41
  • @Lotharyx that those are bad ideas, and they also tend to close mid method. – DonyorM Nov 12 '15 at 16:50
  • 1
    @Lotharyx because the OP is not "connecting so often". The OP is taking connections from a pool - this is **exactly** what connection pools were designed for. With a single persistent connection the OP would have to deal with failover and connection monitoring. – Boris the Spider Nov 12 '15 at 16:58
  • @DonyorM I would start by doing the following - wrap the native connection in your own wrapper class - log when `open()` and `close()` and anything else of interest is called. Collate this with the `try-with-resources` to see if that works as expected. You are positive you're not swallowing an exception somewhere? – Boris the Spider Nov 12 '15 at 16:59
  • @BoristheSpider Fairly, but I'll try to check it. Exceptions get thrown if I use a single instance as Lotharyx suggests, because the connection tends to fail, so I think exceptions would get through. – DonyorM Nov 12 '15 at 17:01
  • @DonyorM it sounds to me like connections are "leaking" - you are not calling `close()` for every `getConnection()`. This is usually caused by swallowed exceptions or using multiple `try...blocks`. If your `try-with-resources` wraps the **whole** interaction this should not be happening. Although - I did see one case where someone was _unwrapping_ the pool's connection and calling `close()` on the native connection; which also cases no end of problems. – Boris the Spider Nov 12 '15 at 17:03
  • @BoristheSpider yeah that's what I thought too, but BoneCP is supposed to watch for that, and has not been giving any errors. As far as I know, all my methods have the entire interaction with the database wrapped in a try with resources. – DonyorM Nov 12 '15 at 17:05
  • @BoristheSpider Ok, I looked through the code and there were a few connection instances outside of `try-with-resources` statements, but fixing that didn't fix the problem. How would you propose that I write wrapper? I looked it up and found [this](http://www.techrepublic.com/article/create-wrapper-classes-on-the-fly-with-java-dynamic-proxies/) tutorial explaining how to do dynamic proxy wrappers, but it didn't seem to work real well (I kept getting `StackOverflow` exceptions). – DonyorM Nov 12 '15 at 21:48
  • @BoristheSpider Ok I figured the problem out, and I feel a bit embarrassed now. You were right, I had some Connection statements outside of `try-with-resources`. If you want to add that as an answer I'll accept it. In case you want to add it to your answer, I figured out which statements were the problem by printing stacktraces in the "getConnection()" method of my connector (info on printing traces [here](http://stackoverflow.com/a/6983897/2719960)) – DonyorM Nov 13 '15 at 17:56

0 Answers0