Re: Connection terminated by the server causes deadlock in jdbc client side connection

Hi

Do have a deadlock, you need at least two threads.
Also they both need to be BLOCKED, not RUNNABLE.
socketWrite should throw an exception, if the connection is closed for sending.

So I assume the server closed the connection for transfer from the server to the client, but the connection remained half-open from the client to the server.
Then on the other hand, you write should have gone through...

What locks here and why?

Regards,
Steffen

-----Ursprüngliche Nachricht-----
Von: pgsql-jdbc-owner(at)postgresql(dot)org [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] Im Auftrag von Leonardo Frittelli
Gesendet: Mittwoch, 7. Oktober 2015 18:33
An: pgsql-jdbc(at)postgresql(dot)org
Betreff: [JDBC] Connection terminated by the server causes deadlock in jdbc client side connection

Hi,

We are experiencing very frequent deadlocks in pgsql jdbc connections. The scenario is a replicated database with hot_standby = on

At times of high volumes of queries in both the primary and the replicated server, we sometimes get the following log indicating that the server has terminated the connection in the replicated database.
FATAL: terminating connection due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
HINT: In a moment you should be able to reconnect to the database and repeat your command.

This is expected and we see no issue with that. What I did not expect, however, is that in the JDBC client side, the connection is deadlocked.

java.lang.Thread.State: RUNNABLE
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(Unknown Source)
at java.net.SocketOutputStream.write(Unknown Source)
at java.io.BufferedOutputStream.flushBuffer(Unknown Source)
at java.io.BufferedOutputStream.flush(Unknown Source)
- locked <0x0000000700742e30> (a java.io.BufferedOutputStream)
at org.postgresql.core.PGStream.flush(PGStream.java:518)
at org.postgresql.core.v3.ProtocolConnectionImpl.close(ProtocolConnectionImpl.java:136)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:280)
- locked <0x0000000700742fd0> (a org.postgresql.core.v3.QueryExecutorImpl)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:547)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
...

Looking at the Postgres JDBC code, I notice that ProtocolConnectionImpl.close() (invoked by the exception handler in QueryExecutorImpl.execute) is trying to 'gracefully close' by sending an 'X' to the server before actually closing the socket.

...
if (logger.logDebug()) logger.debug(" FE=> Terminate"); pgStream.SendChar('X'); pgStream.flush(); pgStream.close(); ...

Does this make sense in a scenario of a connection which has already been terminated by the server side?

At times of high load, all connections in the pool get eventually locked with exactly the same stack trace.

I'd appreciate any advice on how to handle this. Could this be a bug in the JDBC driver?

Thanks,

Leonardo