Skip to Main Content
  • Questions

  • What happens when a user cancels a query

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, oracle.

Asked: August 01, 2017 - 4:31 am UTC

Last updated: August 03, 2017 - 10:01 am UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

Hi Team,

I have some doubts can you pls help me clear it out -

Backend db is oracle 11g
Frontend is Java

Q.1 When a user triggers a download report query, and he closes the window immediately does the query get aborted in the backend or it still keeps running? What is the status of that user in db ?

Q.2 What if the user clicks on download report, and he logs off from the main window? Still the query keeps running? What is the status of that user in db ?

Q.3 What if the user clicks on the download report, then he waits for some time. The query is running in backend. Then he thinks there is something wrong with the system. He again clicks on the download report. He repeats this multiple times, then finally he closes the window.So there will be many queries running in the backend on the database right? What is the status of that user in db ?

Q.4 Can we abort the query if user closes window or logs off from the login?

Thanks

and Chris said...

When the client dies for whatever reason, Oracle Database can detect this via dead client detection and kill off the session:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2233109200346833212

Though the query will likely continue to run for a short while.

What precisely happens in your application depends on how you've built it. If your application is a locally installed Java app with a direct connection to the database, you should see the DB session close.

But that setup is unlikely today. You likely have an application server which has a pool of connections to the database. So you need to figure out how the application server maps DB sessions to user actions. In the world of N-tier architectures and asynchronous processing, just because the user closed the window, doesn't necessarily mean the app server knows this.

You can always kill a session manually in the DB with "alter system kill session 'sid,serial#';". And JDBC has a cancel method on Statement you can use to stop queries.

But it sounds like you're having an issue with users getting frustrated with slow processes. Work on improving your UI to:

- let then know their request is processing
- stop them submitting (the same) request again

If necessary, take the processing offline (for example, email the report).

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

oracle oracle, August 01, 2017 - 10:28 am UTC

Not get clarity

I just wanted to know simply it runs in background or user session in which mode for all 4 questions ?

Chris Saxon
August 01, 2017 - 1:53 pm UTC

Like I said, what precisely happens depends on your configuration. The query will likely continue for a short while until the database notices the client is gone.

But precisely what chain of events happens when "the user closes the window"? Is your application server or whatever is managing your database connections aware of this?

Only you can answer that.

Elaborate

G., August 01, 2017 - 6:13 pm UTC

" The query will likely continue for a short while until the database notices the client is gone. "

What is exactly the process name of that "watcher "? How technically oracle notices the absent client? Pls provide involved processes and db objects.
Chris Saxon
August 03, 2017 - 10:01 am UTC

The SQLNET.EXPIRE_TIME (set in your sqlnet.ora) determines how often you check for dead connections:

"Specify the time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, then it returns an error, causing the server process to exit. This setting is intended for the database server, which typically handles multiple connections at any one time."

http://docs.oracle.com/database/122/NETAG/configuring-profiles.htm#NETAG091
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database