Does Oracle keeps query running in the background despite losing connection with the app?

user_649283 0
Viewed 298

Our app written in Java sends long-running queries to Oracle through JDBC API. It's inevitable that sometimes the app could crash or could get killed abruptly for plethora of reasons without giving it the chance to terminate the queries it has sent. When the app gets killed or stops, it also loses connection to Oracle.

Does Oracle DB keep the query running in the background even if it already has lost connection with the app that had sent the query?

Please cite sources.

oracle12c jdbc oracle java
1 Answers

When a connection between the database and the app is lost, Oracle will stop the session's queries and kill the session. But there are two potential exceptions:

  1. Rollback must finish. From the Database Concepts manual: "A transaction ends when ... A client process terminates abnormally, causing the transaction to be implicitly rolled back using metadata stored in the transaction table and the undo segment." That rollback process cannot be stopped regardless of what happens to the connection. Even if you kill the database instance, when the instance restarts it will resume the rollback. As a general rule of thumb, the time to rollback will be about the same as the time the database spent running the DML. You just have to wait while Oracle puts itself back into a consistent state.
  2. Zombie sessions. Although I don't have a reproducible test case for this problem, I'm sure every DBA has a story about sessions running after the client process disappeared, or even after they killed the session. Before you dismiss this concern as an old myth, note that the SQLNET.EXPIRE_TIME parameter was created for this scenario. Setting the value greater than 0 will have Oracle periodically check and clear terminated sessions. But you don't need to set this parameter unless you're having specific problems.
user_4091720
Related