Re: Hung JDBC connections

Mike,

setTimeout is definitely in the 9.1 driver. It is part of the API. The
client does need to set it.

This http://javaeesupportpatterns.blogspot.ca/2011/04/javanetsocketinputstreamsocketread0.html
looks like the same problem

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Fri, Jun 8, 2012 at 2:57 PM, Mike Charnoky <noky(at)nextbus(dot)com> wrote:
> Thanks for replying Dave.
>
> Is this setTimeout new in the 9.1 version of the driver? We have been using
> the latest 9.0 driver. Does the user of the JDBC API need to set this
> timeout manually somehow? Does the driver maintain some sort of keepalive
> communication with the server?
>
> Again, it is very strange that the OS (for all client applications) did not
> detect that the socket closed despite the fact that the server rebooted.
> That's why I was wondering if an application level keepalive was being used.
> We are using Sun's JVM, 1.6.0_30.
>
>
> Mike
>
>
> On 06/08/2012 02:18 PM, Dave Cramer wrote:
>>
>> Mike,
>>
>> The latest version of the driver does have setTimeout implemented.
>>
>> Curious who's java implementation are you using ?
>>
>> Dave Cramer
>>
>> dave.cramer(at)credativ(dot)ca
>> http://www.credativ.ca
>>
>>
>> On Fri, Jun 8, 2012 at 10:20 AM, Mike Charnoky<noky(at)nextbus(dot)com>  wrote:
>>>
>>> Hi,
>>>
>>> Yesterday we experienced a problem whereby hundreds of JDBC client
>>> connections remained in a hung state, despite the fact that the
>>> PostgreSQL
>>> server was actually rebooted. This is disconcerting because our client
>>> applications have a db failover facility, but that feature won't work
>>> unless
>>> existing JDBC connections (most of which are pooled) fail in the first
>>> place. We had to instead manually restart all client applications to
>>> eliminate all the hung connections.
>>>
>>> Here are the details:
>>> * server: PostgreSQL v8.3 on CentOS 5.4
>>> * clients: JDBC v9.0-802.jdbc4, Java 1.6.0_30, on various CentOS 5.x
>>> releases
>>>
>>> The strange thing is that on the client side, according to "netstat" the
>>> TCP
>>> connections between the JDBC clients and PostgreSQL were still in the
>>> ESTABLISHED state (even though the server was rebooted). A thread dump of
>>> the a typical hung client (one performing DB updates) looked like this,
>>> it
>>> was stuck in a socket read. I was further surprised that some test
>>> clients
>>> (which I did not restart yesterday) are STILL in this hung state this
>>> morning, 18 hours after the incident!
>>>
>>> "DBThrottle-" prio=10 tid=0xad1ff400 nid=0x24b8 runnable [0xad2ad000]
>>>   java.lang.Thread.State: RUNNABLE
>>>        at java.net.SocketInputStream.socketRead0(Native Method)
>>>        at java.net.SocketInputStream.read(SocketInputStream.java:129)
>>>        at
>>>
>>> org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:145)
>>>        at
>>>
>>> org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:114)
>>>        at
>>>
>>> org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
>>>        at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:274)
>>>        at
>>>
>>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1660)
>>>        at
>>>
>>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
>>>        - locked<0xb243db20>  (a org.postgresql.core.v3.QueryExecutorImpl)
>>>        at
>>>
>>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2725)
>>>        at xxx.xxxxxxx.util.DBThrottle.sqlLoop(DBThrottle.java:401)
>>>        at xxx.xxxxxxx.util.DBThrottle.access$000(DBThrottle.java:59)
>>>        at xxx.xxxxxxx.util.DBThrottle$1.run(DBThrottle.java:199)
>>>
>>> Is there a way this hang can be addressed by the JDBC driver? Again, this
>>> is
>>> very disconcerting that the clients did not detect the server failure.
>>> Does
>>> the connection use any sort of timeout mechanism? Thank you for your
>>> help.
>>>
>>>
>>> Mike
>>>
>>>
>>> --
>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-jdbc