Thread: org.postgresql.core.QueryExecutor.execute not responding (Deadlock ?)

org.postgresql.core.QueryExecutor.execute not responding (Deadlock ?)

From
"Email User"
Date:
I used postgres 7.2.3 and pg72jdbc2.jar as my jdbc driver on my production
server (java 1.4.2_02, jakarta-tomcat-4.1.24, Redhat Linux 8.0). The jdbc seem
to having problems when the postgresql server was
buzy. The org.postgresql.core.QueryExecutor.execute stuck when doing updating
process for a query. This caused all my other threads that waiting for execute
a query hang because waiting for releasing of the lock on
postgresql.core.QueryExecutor.execute. I have attacted the thread dump from my
server. I suspected that the postgresql having deadlock on the server site but
however i cannot confirm about that. Does postgresql having capability of
detection for deadlock ? Or, it was bugs for the jdbc driver ?

"Thread-39" daemon prio=1 tid=0x08646590 nid=0x666d runnable [5beb7000..5beb88b8]
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.read(SocketInputStream.java:129)
    at java.io.BufferedInputStream.fill(BufferedInputStream.java:183)
    at java.io.BufferedInputStream.read(BufferedInputStream.java:201)
    - locked <0x47bfb940> (a java.io.BufferedInputStream)
    at org.postgresql.PG_Stream.ReceiveChar(PG_Stream.java:141)
    at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:68)
    - locked <0x47bfb758> (a org.postgresql.PG_Stream)
    at org.postgresql.Connection.ExecSQL(Connection.java:398)
    at org.postgresql.jdbc2.Statement.execute(Statement.java:130)
    at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:73)
    at com.ebusinessoft.gen.sys.AutoNumber.nextNumber(AutoNumber.java:79)
    at org.apache.jsp.processpax_jsp._jspService(processpax_jsp.java:832)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
    at
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:210)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
    at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
    at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
    at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
    at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
    at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
    at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
    at
org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
    at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
    at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
    at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2415)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
    at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
    at
org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
    at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
    at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
    at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
    at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
    at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
    at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
    at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:594)
    at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:392)
    at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:565)
    at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:619)
    at java.lang.Thread.run(Thread.java:534)

Re: org.postgresql.core.QueryExecutor.execute not responding

From
Kris Jurka
Date:

On Fri, 30 Jan 2004, Email User wrote:

> I used postgres 7.2.3 and pg72jdbc2.jar as my jdbc driver on my production
> server (java 1.4.2_02, jakarta-tomcat-4.1.24, Redhat Linux 8.0). The jdbc seem
> to having problems when the postgresql server was
> buzy. The org.postgresql.core.QueryExecutor.execute stuck when doing updating
> process for a query. This caused all my other threads that waiting for execute
> a query hang because waiting for releasing of the lock on
> postgresql.core.QueryExecutor.execute. I have attacted the thread dump from my
> server. I suspected that the postgresql having deadlock on the server site but
> however i cannot confirm about that. Does postgresql having capability of
> detection for deadlock ? Or, it was bugs for the jdbc driver ?

This is likely a problem with a long running transaction that is holding a
lock that the other connections are waiting on.  PG does have deadlock
detection, but this situation is not a deadlock.  A deadlock is when there
is no way any of the involved transactions can possibly succeed.
Here the transaction would be waiting, but with the ability to
continue once the long running transaction committed or rolled
back.  In later versions of pg there is a system view pg_locks which can
be used to debug this, but you are left with using ps and the pg_stat*
views if you have enabled the stats collector.

Kris Jurka