Re: Hung JDBC connections - Mailing list pgsql-jdbc

Just to be clear, I'm assuming that you're referring to
java.sql.Statement.setQueryTimeout(). Is that correct? Is there any
other mechanism used for setting timeouts?

It sounds like we need to set this timeout with a value that is as low
as possible, but not too low such that a long-running query/update would
be terminated.

Thanks again for your help.


Mike

On 06/08/2012 03:29 PM, Dave Cramer wrote:
> 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@nextbus.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@nextbus.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@postgresql.org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-jdbc