Re: Deadlock detection - Mailing list pgsql-jdbc

We came across this thread today researching our issue. Tersely as possible:

JBoss 4.2.3, Solaris 10 (x64), PG 8.2 (with GIS), Hibernate/EJB2.

We were storing a GIS column as a LOB in WKT format (e.g., POINT(23.22
23.22)) and ocassionly would have apparent transactions timeout trying to
insert into this table. (turns out that a T doesn't timeout per se, it just
never finishes and the timeout our customers see are Ajax/session related).

- JBoss logs would show the transaction committing from Hibernate.

- Thread dumps on the JVM would show the stuck thread in

"http-0.0.0.0-8443-4" daemon prio=3 tid=0x00000000019ee400 nid=0x70 runnable
[0xfffffd7eab923000..0xfffffd7eab9268a0]
   java.lang.Thread.State: RUNNABLE
        at java.net.SocketOutputStream.socketWrite0(Native Method)
        at
java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
        at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
        at java.io.BufferedOutputStream.write(BufferedOutputStream.java:105)
        - locked <0xfffffd7fad49c858> (a java.io.BufferedOutputStream)
        at java.io.FilterOutputStream.write(FilterOutputStream.java:80)
        at org.postgresql.core.PGStream.Send(PGStream.java:208)
        at
org.postgresql.core.v3.SimpleParameterList.writeV3Value(SimpleParameterList.java:258)
        at
org.postgresql.core.v3.QueryExecutorImpl.sendBind(QueryExecutorImpl.java:861)
        at
org.postgresql.core.v3.QueryExecutorImpl.sendOneQuery(QueryExecutorImpl.java:1052)
        at
org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:643)
        at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:344)
        - locked <0xfffffd7fad4a1af8> (a
org.postgresql.core.v3.QueryExecutorImpl)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2592)
        at sun.reflect.GeneratedMethodAccessor152.invoke(Unknown Source)
        at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at

org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:471)
        at $Proxy291.executeBatch(Unknown Source)
        at
org.jboss.resource.adapter.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:774)
        at
org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
        at
org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
        at
org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
        at
org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
        at
org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
...

(no other monitor/blocks - ONLY thread "running" at the time!)

- PGAdmin3 server stats/locks would show a slew of locks on this table (no
other locks, so no traditional  deadlock per se).

- truss(1M) on the postgres pid holding the locks showed:

% truss -p 14713
send(9, 0x082FBF00, 8192, 0)    (sleeping...)

pfiles(1M) on this pid showed that fd9 was this same socket connected to our
JVM and blocked

clearly (at least to us) - the database was writing to this socket (seen
from truss) and the jvm was writing to this socket (seen from jvm stack)


- netstat(1M) showed no send/recvQ data on this socket

- PG log showed

-- query was simple insert into FeatureName(xxx) values ($1, ...)
-- the geom text/LOB had a 22K bind (large country MULTIPOLYGON)

WORKAROUND

We're going to remove the string (we determined our client app doesn't use
the geom for this table) or at least convert it to the binary version long
term when we start using it again.

WAY FORWARD

We're planning on upgrading to 8.3.17 (? whatever is the latest) and hope
that this deadlock in the driver doesn't happen again.

Just posting this if it might be useful for your testing.



--
View this message in context: http://www.nabble.com/Deadlock-detection-tp21580039p23067564.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.