Datasource.getConnection hangs

[Nilbus]

I have a web application written in Struts that makes many database transactions.

The application works fine for a while, but after several days, it starts hanging on the DataSource.getConnection() method. I assume this means all the connections have been used, and it's waiting for a connection to become available.

In each database method, there is code similar to this:

Connection con = null;
try {
    con = dataSource.getConnection();
    //Do database stuff

} finally {
    if (con != null)
        con.close();
}

In MySQL, max_connections=256, so it seems to me that the connections are not being closed after they're opened. I don't see how that can happen though, since the connection is closed in the finally clause. I verified that every connection in my code is closed in the manner shown above.

To get it working again, I have to killall -9 java, and restart the web application.

Any ideas?

 

[Diancecht]

I'm not experienced in this field but ... what does the getConection() method do?

If there's a Connection pool under that, you should return the connection back to the pool instead of closing it.

Cheers,

Dian

 

[Nilbus]

getConnection() opens a new connection to the DataSource.

Someone else mentioned something about connection pools too, so that may be the issue. However, I'm not familiar with connection pools at all. I'll look into it.

FYI, I'm using this with a MySQL database.

 

[sem]

This may be even a container issue, check how your pool is tuned. When the number of opened connection exceedes some limit you may either get an error or wait for a free one. In any case there may be one of the applications that doesn't free connections.

Regards, Dima

http://seminihin.narod.ru

 

[sedj]

When the call to grab a connection hangs, does restarting your servlet/JSP container solve the issue ? If it does, then it sounds like you have a connection leak somewhere in your application - ie you are not closing a connection somewhere.

You should check your source very carefully that all connections are being closed under all circumstances.

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[Nilbus]

I will look into the connection pool stuff.

Sedj, I have verified that all connections in this application are closed. The only other application accessing this database server is a PHP application that automatically closes every connection it opens (when the script ends), according to the documentation.

 

[timw]

I'd bet that the documentation for the PHP application is mistaken

Tim
---------------------------
"Your morbid fear of losing,
destroys the lives you're using." - Ozzy

 

[Nilbus]

My application is using the Struts DataSource. From what I read, the Struts datasource isn't reliable, and shouldn't be used. Maybe this is my problem. I'm going to replace it with the Commons DBPC implementation, and see if that works better.

In the mean time, I'll also be keeping an eye on the active connections in mysql with show processlist;

 

[timw]

Sounds like a plan to me. Or you could try the free Connection Pooling software at

http://www.primrose.org.uk

Tim
---------------------------
"Your morbid fear of losing,
destroys the lives you're using." - Ozzy

 

[Nilbus]

I've resolved the issue.

The person who write my application was using the Struts implementation of the DataSource (org.apache.struts.util.GenericDataSource). This implementation is deprecated. The Struts strongly recommends against using this implementation, and to use the DBCP or some other implementation instead.

I've replaced it with DBCP, and told it to time out if a connection is not available after 10 seconds, instead of waiting indefinitely.

Thanks everyone for your help!


Timw,
FYI, after monitoring the database connections, I believe the php documentation is correct. When the script terminates, all database connections it opens are closed. It does not do any pooling.

 

[timw]

Glad you've sorted it. I know (from experience) how frustrating this kind of problem can be.

Tim
---------------------------
"Your morbid fear of losing,
destroys the lives you're using." - Ozzy