Re: Hung thread

So it is possible that as Kevin said the test on borrow statement referred
to a connection that was still in the TCP stack but had not timed out yet.

How long did you wait ?

Dave Cramer

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

On 28 October 2014 13:44, dhaval jaiswal <dhavallj(at)hotmail(dot)com> wrote:

> 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
>