Re: Hung thread

Following is the JDBC connection.

<Resource name="jdbc/DB" auth="Container" type="javax.sql.DataSource"
driverClassName="org.postgresql.Driver" url="jdbc:postgresql://IP:PORT/DB_NAME"
username="" password=""
maxActive="100" maxIdle="30" maxWait="30000"
validationQuery="select 1" testOnBorrow="true"
removeAbandoned="true" removeAbandonedTimeout="120" logAbandoned="true" />

There is nothing reflecting in the result of the pg_stat_activity.

There is no relevant logs found in pg_logs too.

Thread is not only stuck at the time of get connection. Some times at the time of query execution.

java.net.SocketInputStream.socketRead0(Native Method)
java.net.SocketInputStream.read(SocketInputStream.java:129)
org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:145)
org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:114)
org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
org.postgresql.core.PGStream.ReceiveChar(PGStream.java:274)
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1660)
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
com.eos.accounts.data.OrderAuthorization.loadOrderAuthorization(OrderAuthorization.java:102)
com.eos.tally.reports.data.TallyOrder.getOrdersForTallyReport(TallyOrder.java:88)
com.eos.tally.reports.SaleReport.setData(SaleReport.java:71)
com.eos.tally.reports.AgentSaleReport.generateMap(AgentSaleReport.java:25)
com.eos.tally.reports.AbstractTallyReport.generateTallyReport(AbstractTallyReport.java:171)
com.eos.tally.reports.TallyReport.generateReport(TallyReport.java:112)
com.eos.b2c.beans.callcenter.AccountingBean.downloadTallyAgentSalesReport(AccountingBean.java:602)
com.eos.b2c.ui.B2cCallcenterNavigation.doGet(B2cCallcenterNavigation.java:1117)
javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)

TCP_KEEP_ALIVE is 2 hours at the server level.

> Date: Tue, 28 Oct 2014 07:28:16 -0700
> From: kgrittn(at)ymail(dot)com
> Subject: Re: [JDBC] Hung thread
> To: pg(at)fastcrypt(dot)com; dhavallj(at)hotmail(dot)com
> CC: pgsql-jdbc(at)postgresql(dot)org
>
> Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
> > On 28 October 2014 07:34, dhaval jaiswal <dhavallj(at)hotmail(dot)com> wrote:
>
> >> 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:257)
> >> - locked <0x00002aaaf6282d00> (a org.postgresql.core.v3.QueryExecutorImpl)
> >> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
> >> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
> >> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
> >> at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
> >> at org.apache.tomcat.dbcp.dbcp.AbandonedObjectPool.borrowObject(AbandonedObjectPool.java:84)
> >> at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:96)
> >> at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
> >> at com.eos.hotels.viahotel.manager.ViaHotelDatabaseManager.getConnection(ViaHotelDatabaseManager.java:74)
> [ snip ]
>
> > Given the little bit of information available my guess would be
> > that since this is using dbcp, and the connections are never really
> > closed.
>
> The stack trace shows that the getConnection() method of dbcp is
> trying to re-use a connection in the pool, and is first testing the
> connection by invoking our JDBC driver's executeQuery() method.
>
> > I believe once a connection is made we are always listening
> > on the socket.
>
> I don't follow that; it is listening for the response to a query.
> What thread would be "always listening", anyway? The driver is not
> multi-threaded.
>
> > However I will say I haven't really spent much time on this. Either
> > way we would need much more information in order to be able to help
> > you.
>
> It would be interesting to know what query dbcp is using to test
> whether the connection is still alive. Also, if the client port
> can be discovered, looking for it in the pg_stat_activity view
> would be interesting.
>
> I can think of one halfway plausible explanation, though. A
> firewall between the machine running dbcp and the database server
> might be blocking traffic after some idle timeout, possibly
> resulting in a hang like this when the pooler eventually tries to
> use the connection again. You might want to try configuring TCP
> timeouts on both the client and the server. It wouldn't hurt to
> closely review the network path between the machines.
>
> Logging connections and disconnections and closely reviewing the
> server logs could provide insights.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company