Stale sessions and locks

[Christian Buchegger]

With our application using h2(1.4.199) as database in server mode, we see stale sessions and sometime they hold locks.

We understand the cause for this behavior and can reproduce it. Application users are hopping with their notebooks between meeting rooms, loose their connections and eventually get assigned new IP addresses in another network.

While the keep-alive feature discussed in https://github.com/h2database/h2database/issues/2032 may mitigate this to some extend, we possibly need further functionality to have more control.

I would like to get feedback on the below two proposals:

1. Add a function abort_session:
   Abort current command and call session.close() so that the locks and resources will be released. This will avoid the need of server restart.
2. Add a parameter SQL_SESSION_IDLE_TIMEOUT
   With this parameter, a session will be closed by the server, when there was no client activity within the defined timeout period.

Please provide your view regarding:

• Do you see this behavior as well?
• If yes, how do you handle this?
• Would you benefit from the proposed features, so that it would make sense to pursue this ?

Thanks,

Christian

[Matt Pavlovich]

Christian-

Look into setting these combinations:

1. On the server: h2.socketConnectTimeout

2. On the client urls: ;AUTO_RECONNECT=TRUE

The behavior will be that clients re-connect over and over (harmless, but not ideal) but you won’t have stale connections or abandoned locks. 

Also note— there already exists a function to close sessions:

http://www.h2database.com/html/functions.html#cancel_session

-Matt

[Christian Buchegger]

Matt-

thanks for your suggestions. Unfortunate as our application requires AUTOCOMMIT=FALSE; AUTO_RECONNECT is not an option.

Also this would just help for situations when a reconnect succeeds, and still leave stale sessions.

I am aware of cancel_session(). It is documented as "Cancels the currently executing statement of another session." and exactly behaves as such.

The session will still exist after using this function.

Thanks,

Christian

[Matt Pavlovich]

Christian-

Yeah, you are hitting some of the same issues I did. There doesn't seem to be a straight-line path for supporting TCP connection health in a distributed environment. I started making some patches to improve H2's TCP client management.. there is still open discussion on the approach and need (https://github.com/h2database/h2database/pull/1990).

Q: What is the use case where you set AUTOCOMMIT=FALSE on the url?  It seems to me you would want AUTO_RECONNECT.

Specifically-- would your use case instead support disabling autoCommit on the connection object and deal with the in doubt transactions instead?

-Matt

[Noel Grandin]

You

On Mon, 9 Sep 2019 at 13:47, 'Christian Buchegger' via H2 Database <h2-da...@googlegroups.com> wrote:

I would like to get feedback on the below two proposals:

1. Add a function abort_session:
   Abort current command and call session.close() so that the locks and resources will be released. This will avoid the need of server restart.

That sounds reasonable.

2. Add a parameter SQL_SESSION_IDLE_TIMEOUT
   With this parameter, a session will be closed by the server, when there was no client activity within the defined timeout period.

Note that, with something like this, you are invariably going to occasionally manage to kill a session under a live client, and the client may not notice until it issues a command, whereupon it will need to reconnect and re-issue that command.

You can probably mitigate these issues already by

(*) chatting to your sysadmin and making the timeout before recycling IP addresses longer, so your users normally get the same IP address again.

(*)  changing your app to shut down connections aggressively itself, and re-open them as needed - this can normally be configured inside a connection pool. For bonus points, the connection pool can be configured to do a "test if connection OK before handing it out", which will prevent the problem that your app sees a broken connection.

But really the root of the problem is that you're using H2 as an application server, which it is not really meant to be - it is not intended to be using directly facing a complex client network.

 

[Matt Pavlovich]

Noel-

Would you kindly elaborate on your last statement there about H2 is not intended to be used over a complex client network?

My impression is that H2 is pretty close to being able to have robust client network connections.

Is there a bigger issue or something more fundamental that would prevent this from being successful?

Thanks,

Matt

[Noel Grandin]

On Mon, 9 Sep 2019 at 17:45, Matt Pavlovich <ma...@hyte.io> wrote:

Would you kindly elaborate on your last statement there about H2 is not intended to be used over a complex client network?

H2 is intended (mostly) to be small, reasonably fast, and reasonably effective, without being overly complicated.

Database Servers, in general, are intended to be deployed in locked down and controlled environments.

Normally, that means behind some kind of application server / web server / etc.

Now, of course, H2 __can__ be used in other environments, but then, you are going to continue finding things just don't quite work right, because now you are operating outside the intended design space.

 

[Matt Pavlovich]

Noel-

Got it, thanks. We use H2 for those same reasons, Also, the ability to automate our integration tests using the same database engine as we use in production is awesome and reduces our operational support footprint.

FWIW- our contributed enhancements to the TCP client handling stem from wanting to improve automate management and monitoring tasks.

Thanks,

-Matt

[Christian Buchegger]

Thanks for this discussion,

I will go for the abort_session function, as it would resolve out most pressing issue with the stale locks. As soon we have tested the implementation I will provide a PR for this.

-Christian