1

My understanding of the ecosystem is limited so excuse my ignorance.

I have a really large Oracle database (19) with a lot of connections to it from multiple applications. We have put some limit of maximum amount of connections (sessions) to it and we can hit that limit quite easily.

We are using Java 17 + Spring Boot 2.6.1 & HikariCP (considering UCP) & JDBI3

When I ran my application before adding connection pools I have noticed that when I terminate my connection ungracefully (kill the application forcefully) the connection is kept alive on Oracles side for quite a while (30m+). How is this handled when shutting down an application with connection pools? I assume that the connection pool will be shut down by TCP timeouts instead of a TCP FIN/ACK and that it works differently since the application is killed before being able to shut down the connections, but does that mean that the connections on the Oracle side would stay alive like before?

TL;DR: Do I need to handle an ungraceful shutdown by shutting down the connection pool manually to avoid Oracle keeping dead connections alive?

Alternatively I would need to contact our Oracle team and somehow convince them of lowering this keep-alive value that they seem to have on their side.

Zanndorin
  • 360
  • 3
  • 15
  • You are using a connection pool (Hikari) so not sure why you think you aren't using a connection pool. – M. Deinum Dec 07 '21 at 09:57
  • I changed the text a bit: "When I ran my application before adding connection pools". The main part of the question is if connection pools handle ungraceful shutdowns and Oracle keeping the session alive. Ungraceful being terminate or straight up pulling the electricity cable. – Zanndorin Dec 07 '21 at 10:01
  • Spring Boot by default uses a connection pool, so unless you explicitly disabled it/overrode it you have been using a connection pool from the start. I'm also not sure what you mean with an ungracefull shutdown, every shutdown of a Spring Boot application will lead to closing the application context and thus the connection pool (unless you misconfigured things manually yourself!). Unless you are directly killing the application, which prevents a shutdown, everything will be closed. In the latter event there is nothing you can do to close things afterwards (on the application side). – M. Deinum Dec 07 '21 at 10:04
  • Are you sure? The "HikariCP starting" log doesn't show up when unless I add the HikariCP in spring settings. Also the behavior was way different after adding it. – Zanndorin Dec 07 '21 at 10:07
  • Yes I'm sure, if you use data access code and the spring boot starters that is. If you manually are adding things and not using the starters you are in a different place. I also wonder how you have configured the datasource before that (and that also means if that still is in place the hikaricp is useless). Basically what I want is to see code you think it problematic as you now have a vague(ish) description (at least for me). So basically add some dependencies, confiugration and code to your post. – M. Deinum Dec 07 '21 at 10:08
  • Yeah I had read that it would be autoconfigured when found in the classpath but I strongly doubt that it was in this application due to the difference in behavior between the two settings. This might be because of our Gradle runtimeClasspath settings, the application ecosystem is quite large so I don't have the full picture. I'll reconsider the question but I am a bit unsure of what I am asking, you mentioned it above that the issue is the "application killing" and not being able to do anything. So I guess that was the question, now to look for settings that might fix that in Oracle/Hikari – Zanndorin Dec 07 '21 at 10:15
  • Well if it didn't you either used a different one, datasource/database access doesn't even use a datasource (regardless of the fact it is or isn't configured). Without change and onlyu adding the dependency and it still works, I highly doubt the connection pool is even in use. – M. Deinum Dec 07 '21 at 10:17
  • @Zanndorin could you shed a light what OS you are on (both DB and Java) and what is the output of `netstat -na --timers` or `ss -nao` commands (assuming your DB is on Linux)? Are there any firewalls between Java and DB? At first glance it does not matter whether you are using connection pool or not - it is OS responsibility to cleanup resources (including sockets as well) of failed processes – Andrey B. Panfilov Dec 07 '21 at 10:23
  • Sadly (or luckily) I am not responsible for the Databases and can't access them like that. I assume that I need to tell someone on the Oracle side to fix this or spend time on setting up my own... When I wrote the question I had hoped for an answer like "UCP fixes this" or "Have you set this Hikari setting?" :) – Zanndorin Dec 07 '21 at 10:30
  • 1
    it can't depend on usage of connection pool: from OS perspective "direct" and "cp" connections are both sockets, if you are issuing `kill -9` the application has no option to handle it, so it is handled by OS - it sends either FIN or RST. *BUT* the real world behaviour may depend on various factors (OS vendor, version, firewalls, etc), for example: https://access.redhat.com/discussions/2990731, what you have provided is not enough. – Andrey B. Panfilov Dec 07 '21 at 10:47
  • @M.Deinum You were right it was already used because it was in the path but the default settings (not ours but from Spring?) for maxPoolSize were -1 in the HikariDataSource so (I assume) it wasn't really used so I "activated" it by setting real values. – Zanndorin Dec 07 '21 at 13:28

1 Answers1

0

You may try using Universal Connection Pool (UCP), it has an inbuilt mechanism to gracefully close the connections on application/JVM shutdown.

Saurabh Verma
  • 116
  • 4
  • One might benefit from a few details on how exactly UCP handles shutdown: https://medium.com/@edyg/hacking-oracle-universal-connection-pool-shutdown-hook-fd0a5f8185cb – Ed Gomoliako Mar 03 '23 at 10:10