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

Hi Leonardo

On 14/10/2015 22:26, lfrittelli wrote:
> These are good points all. Let me provide more context for my scenario on
> each item
>> >First I'd like to say that even though we are dealing with TCP one must
>> >treat it almost like UDP. TCP
>> >packets can be consumed by firewalls and proxy servers with no response
>> >given to the sender. So with
>> >that in mind to create a robust fault tolerant driver, timeout on all TCP
>> >communications is a must.
> True in the general case, but I don't think it would have affected the
> specific scenario under issue. For context on the scenario, note that there
> was no firewall or proxy. We were using local connections
> (jdbc:postgresql://localhost...)
>
Well in your case the server closed the connection and one can only
assume that it did so by tearing down the socket. That is the shut-down
was not a graceful close so no FIN packet sent to the client and
therefore the client socket has no idea that the server connection has
been terminated. Maybe the server did send a FIN and there is a bug in
the Java socket code, but I doubt it. It really highlights my point that
a timeout should be used for sending data. The socket API supports this
(SO_SNDTIMEO) however there is no such feature within the Oracle Java
implementation, one has to use a third party implementation to find this
feature for example: com/savarese/rocksaw/net/RawSocket. Using Oracle
the only option is to set the SO_KEEPALIVE setting for the socket, for
which there is a PostgreSQL driver parameter setting, and it means the
socket would "eventually" get closed if the server silently closed the
connection. "Eventually" is determined by the default OS settings for
SO_KEEPALIVE.

>> 1) So in the first case where an IOException is thrown, the original code
>> >that is closing the connection
>> >via "close" cannot have a timeout feature enabled and that will still
>> >exist in the code with the potential
>> >to fail...
> As the 'graceful' close() method will write on the socket, would it be
> possible to put a timeout on write? I am no expert but have not been able to
> find out how.
Not possible with the implementation of the current driver and Oracle's
implementation of sockets.

>> 2) When an IOException is thrown as the origin of an exception this should
>> >not be lost and should
>> >always be attached to the final exception. I don't see this cascaded in
>> >the updated driver using the
>> >"abort" code...
> At least in some of the cases it appears that the original IO error is
> consumed and converted to an error SQL state, and not re-thrown.
> In other cases there is actually a message from the server advising of the
> termination and this is somehow interpreted by the client, thus no IO
> exception in those cases.
I'm not sure what is happening, but probably some times you are actually
seeing an IOException generated from the "close" because the real
exception is shown via you latest listing;

> (SqlExceptionHelper.java:145) - SQL Error: 0, SQLState: 40001
> (SqlExceptionHelper.java:147) - 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 shows the real cause of the exception, which is an SQLException,
and we know at this point that the server socket will be closed so
definitely no point in trying to send anything to the server after
receiving this.

So in summary the changes from "close" to "abort" are the right fix,
however until Oracle implement the SO_SNDTIMEO feature we will always
have the possibility of a "hung" socket unless we use the SO_KEEPALIVE
feature, which is not ideal because we have no control over the
intervals that these are sent without altering OS settings somewhere.

Regards
Donald Fraser