Db connection is hung when there is a network change

[tuliren]

A db connection will hang when there is a network change. This is because we don't enforce timeout at any level. For detailed explanation, please refer to this article.

There are several ways to fix it:

1. Specify socket timeout. This is easy to implement (modify connection string), but it is hard to find the optimal value, or to use special setting for legitimate long-running query.
2. Enforce timeout on transactor. This requires a refactoring of the transactor implementation (relevant PR: #210).
3. Call isValid before checking out a connection from the pool (modify this method). This approach will only partially solve this problem. Queries that are already running while there is a network change will still suffer from this issue. In addition, currently IDb methods are implemented in each implementation, while many of the methods are the same and can be extracted to a base class. Should we choose this approach, we should consider performing this refactoring as well.

[joshk0]

Can tcp keepalives be used? This would prevent closing a long running transaction, as long as the server is responding at a low level, it does not need to be returning query results.

…

On Tue, Aug 14, 2018, 17:04 LiRen Tu ***@***.***> wrote: A db connection will hang when there is a network change. This is because we don't enforce timeout at any level. For detailed explanation, please refer to this article <https://www.cubrid.org/blog/understanding-jdbc-internals-and-timeout-configuration> . There are several ways to fix it: 1. Specify socket timeout. This is easy to implement (modify connection string <https://github.com/LiveRamp/jack/blob/master/jack-core/src/com/rapleaf/jack/DatabaseConnection.java#L92>), but it is hard to find the optimal value, or to use special setting for legitimate long-running query. 2. Enforce timeout on transactor. This requires a refactoring of the transactor implementation (relevant PR: #210 <#210>). 3. Call isValid <https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#isValid(int)> before checking out a connection from the pool (modify this method <https://github.com/LiveRamp/jack/blob/master/jack-core/src/com/rapleaf/jack/transaction/DbPoolManager.java#L167>). This approach will only partially solve this problem. Queries that are already running while there is a network change will still suffer from this issue. In addition, currently IDb methods are implemented in each implementation, while many of the methods are the same and can be extracted to a base class. Should we choose this approach, we should consider performing this refactoring as well. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#227>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AFHsXv9-XGIChKBeTXsQ48GFzbt7rw8Eks5uQ2WAgaJpZM4V9W50> .

[sidoh]

@joshk0 - I had the same idea, but I think you have the firmer understanding here. It seems like this kind of thing is exactly what SO_KEEPALIVE is intended to solve for. It's also just a JDBC parameter:

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

[joshk0]

Yes, I'd suggest trying that first. You can even collaborate with us to simulate a network outage on a specific VM whenever you want.

…

On Tue, Aug 14, 2018, 17:25 Chris Mullins ***@***.***> wrote: @joshk0 <https://github.com/joshk0> - I had the same idea, but I think you have the firmer understanding here. It seems like this kind of thing is exactly what SO_KEEPALIVE is intended to solve for. It's also just a JDBC parameter: https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#227 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AFHsXhDWmsmy4jmmPnTq4Ufe7vKvoKFNks5uQ2ppgaJpZM4V9W50> .