Re: JDBC driver doesn't close socket event after connection.close()

From: Hamelech Al Hakol (djmarcusIGNORE_at_m5incIGNORE.com)
Date: 02/15/05 

Date: Tue, 15 Feb 2005 16:01:35 -0500

Hi

I'm glad to report that the problem has been solved. It appears to be a
coding error on my part.

It turns out the problem is with the initialization of the JDBC driver:

         driver = (Driver)Class.forName(driverName).newInstance();

This line was being executed each time a connection was made (every 10
seconds), rather than just once when the application starts.

It seems that the JDBC driver keeps its one socket alive (for
optimization purposes?) even though the application "closed" the
connection. HOWEVER, since each connection was instantiating a new
instance of the driver (I believe) then each connection is using a new
driver instance. This would account for so many open sockets to the
MS/SQL server.

Carb, am I correct in deducing that the JDBC driver keeps its one
connection open even though the application closes it? Am I also correct
that each call to 'newInstance()' instantiates a new driver even if one
is already running?

When I moved the driver initialization out of the connection code (into
the constructor) the problem goes away. I now see just one connection to
the server.

-Thanks for your help
David

Hamelech Al Hakol wrote:
> Hi
>
> The 'jar' manifests are the same for msbase.jar, mssqlserver.jar, and
> msutil:
>
> Manifest-Version: 1.0
> Created-By: 1.2.2 (Sun Microsystems Inc.)
>
> ========================================================================
>
> I don't have a stack trace per-se of the 'Too many open files' error. It
> is simply a victim of the JDBC driver leaving too many sockets connected
> to server.
>
>
> ========================================================================
>
> Finally, I tried to attach the LSOF output but the news server rejects
> it as too long (137KB). However here's a snippet (the LSOF output is
> full of these [pages and pages]):
>
> java 14874 root 29u IPv4 93075018 TCP
> 192.168.1.253:54413->192.168.1.98:ms-sql-s (ESTABLISHED)
> java 14874 root 30u IPv4 93075958 TCP
> 192.168.1.253:54415->192.168.1.98:ms-sql-s (ESTABLISHED)
> java 14874 root 31u IPv4 93077322 TCP
> 192.168.1.253:54417->192.168.1.98:ms-sql-s (ESTABLISHED)
> java 14874 root 32u IPv4 93069974 TCP
> 192.168.1.253:54405->192.168.1.98:ms-sql-s (ESTABLISHED)
> java 14874 root 33u IPv4 93078697 TCP
> 192.168.1.253:54419->192.168.1.98:ms-sql-s (ESTABLISHED)
> java 14874 root 34u IPv4 92826578 TCP
> 192.168.1.253:52778->192.168.1.98:ms-sql-s (ESTABLISHED)
> java 14874 root 35u IPv4 93080053 TCP
> 192.168.1.253:54421->192.168.1.98:ms-sql-s (ESTABLISHED)
> java 14874 root 36u IPv4 93082352 TCP
> 192.168.1.253:54425->192.168.1.98:ms-sql-s (ESTABLISHED)
> java 14874 root 37u IPv4 94364533 TCP
> 192.168.1.253:56366->192.168.1.98:ms-sql-s (ESTABLISHED)
> java 14874 root 38u IPv4 94377910 TCP
> 192.168.1.253:56405->192.168.1.98:ms-sql-s (ESTABLISHED)
> java 14874 root 39u IPv4 94398227 TCP
> 192.168.1.253:56433->192.168.1.98:ms-sql-s (ESTABLISHED)
>
>
>
> -TIA & Regards
> David
>
>
> Carb Simien [MSFT] wrote:
>
>> --------------------
>> | Date: Tue, 15 Feb 2005 12:49:06 -0500
>> | From: Hamelech Al Hakol <djmarcusIGNORE@m5incIGNORE.com>
>> | User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
>> | X-Accept-Language: en-us, en
>> | MIME-Version: 1.0
>> | Subject: JDBC driver doesn't close socket event after
>> connection.close()
>> | Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>> | Content-Transfer-Encoding: 7bit
>> | Message-ID: <ugytaa4EFHA.2832@TK2MSFTNGP14.phx.gbl>
>> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
>> | NNTP-Posting-Host: 209.101.178.227
>> | Lines: 1 | Path:
>> TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.jdbcdriver:884
>> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
>> | | Hi
>> | | I have a Java application (on Red Hat Enterprise) that
>> | 1) connects every 10 seconds to an MS/SQL database
>> | 2) attempts to get the metadata for a specific table
>> | [correctly fails because the table does not exist]
>> | 3) closes the connection
>> | | After a while, the other components of the application (unrelated
>> to the | JDBC activity) begin to experience 'Too many open files'
>> exceptions.
>> | | Using the 'lsof' command (which displays the open files for each |
>> process), I can see that there are very many socket connections from
>> my | Java process to the MS/SQL database. In fact, they easily
>> consumed the | available open-files table slots for the process.
>> | | Question: Is this a bug or a feature of the JDBC driver?
>> | | If a bug (keeping the sockets connected), is there a known fix? is
>> there | a work-around to the problem?
>> | | If a feature (perhaps some side-effect of connection pooling), is
>> there | a parameter that I can set when I open the connection to
>> restrict the | total number of sockets actively maintained by the JDBC
>> driver?
>> | | Any help would be greatly appreciated.
>> | | TIA
>> | David
>> |
>> Hi David,
>>
>> Can you post the full exception stack as well as the output from
>> LSOF? Also, which build of the JDBC driver are you using?
>>
>> Carb Simien, MCSE MCDBA MCAD
>> Microsoft Developer Support - Web Data
>>
>> Please reply only to the newsgroups.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> Are you secure? For information about the Strategic Technology
>> Protection Program and to order your FREE Security Tool Kit, please
>> visit http://www.microsoft.com/security.
>>

Relevant Pages
Quantcast