Number of sockets available for a JDBC connection at Windows 2003

Question

My team built a Windows Service in Java that connects to a SQL Server 2005 in a Windows 2003 Server, using pure JDBC (no connection pooling) with the JTDS driver.

After a while, the method that opens the connections to the database start raising exceptions with the following stack trace:

    java.net.BindException: Address already in use: connect
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(PlainSocketImpl .java:305)
at java.net.PlainSocketImpl.connectToAddress(PlainSoc ketImpl.java:171)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.j ava:158)
at java.net.Socket.connect(Socket.java:452)
at java.net.Socket.connect(Socket.java:402)
at java.net.Socket.<init>(Socket.java:309)
at java.net.Socket.<init>(Socket.java:124)

Although the programmers were careful to close connections when they were done, something is not going right.

For the time being, we solved the problem switching to the Named Pipes protocol (since all is hosted in the same machine), but this is a temporary solution.

I've googled for the problem and it seems we should be using some connection pooling library such c3p0. Is this the only solution to the problem?

Could I try to raise the sockets limit in Windows 2003?

Answer 1

It really looks like you're connections aren't being closed. That or you're trying to re-use the connection incorrectly...

It's strongly recommend to use a connection pool for various performance reasons. For example, you won't need to create a connection each time, which is very expensive. Re-using connections makes a world of difference.

Secondly, do you really want to create your own pooling mechanism? It's not as simple as it appears, there are lots of idiosyncratic threading issues. It's much easier to just use an existing library that's withstood the test of time.

Answer 2

Are you opening / closing connections at a very rapid rate? When a TCP connection is closed, they hang around for a little while in the TIME_WAIT state. On Windows, the default time they exist is 240 seconds. It sounds like you might have quite a few tcp connections in the TIME_WAIT state.

You can check this by running netstat. If you have a huge number of tcp connections to the database server in the TIME_WAIT state, a connection pool will fix your issue.

You can try to raise the socket limit, and/or lower the time a connection will stay in the TIME_WAIT state. But this will alter the behavior of all tcp connections. So use a connection pool :) We use dbcp as our connection pool solution in Java.