oracle 如何取消长时间运行的数据库操作？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/889102/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to cancel a long-running Database operation?
提问by Rob P.
Currently working with Oracle, but will also need a solution for MS SQL.
目前与 Oracle 合作，但还需要 MS SQL 的解决方案。
I have a GUI that allows users to generate SQL that will be executed on the database. This can take a very long time, depending on the search they generate. I want the GUI/App to responsive during this search and I want the user to be able to cancel the search.
我有一个 GUI，它允许用户生成将在数据库上执行的 SQL。这可能需要很长时间，具体取决于它们生成的搜索。我希望 GUI/App 在此搜索过程中做出响应，并且我希望用户能够取消搜索。
I'm using a Background Worker Thread.
我正在使用后台工作线程。
My problem is that, when the user cancels the search, I can't interrupt the call to the database. It waits until it is finished and then, it can poll the 'CancelationPending' property. Not only does this waste resources on the database, but it creates problems for my code.
我的问题是，当用户取消搜索时，我无法中断对数据库的调用。它会一直等到它完成，然后才能轮询“CancelationPending”属性。这不仅会浪费数据库上的资源，还会给我的代码带来问题。
If the user hits 'Search' on a very long query, then clicks 'Cancel' and then 'Search' again - the first search is still chugging away on the database. The background worker is still busy when they hit search again. The only solution I've got to this problem is to make a new background worker.
如果用户在很长的查询中点击“搜索”，然后点击“取消”，然后再次点击“搜索”——第一次搜索仍在数据库中进行。当他们再次点击搜索时，后台工作人员仍然很忙。我对这个问题的唯一解决方案是创建一个新的后台工作人员。
It seems like a really ugly way to do things. The database keeps working I'm creating new instances of background workers....when I really want to STOP the database call and re-use the same worker.
这似乎是一种非常丑陋的做事方式。数据库继续工作我正在创建后台工作人员的新实例......当我真的想停止数据库调用并重新使用同一个工作人员时。
How can I do that?
我怎样才能做到这一点？
采纳答案by Josh Curren
I dont think it is possible. Here is a link to a discussion on Oracle's website about this topic: http://forums.oracle.com/forums/thread.jspa?threadID=400492&start=15&tstart=0
我不认为这是可能的。这是 Oracle 网站上有关此主题的讨论的链接：http: //forums.oracle.com/forums/thread.jspa?threadID=400492&start=15&tstart=0
回答by Mehmet Aras
If you're using ADO.NET and SQL data provider, take a look at SqlCommand.Cancel method. That does what you're looking for. However, it tries to cancel and the cancellation may take time. Basically, it's up to SQL Server to decide when to grant your cancellation request. When the query is cancelled, you should get a SqlException that indicates that the operation was cancelled by user. Apparently, you don't want to treat this exception as exception and handle it specially such as if SqlException is due to user cancelling the operation, just swallow it.
如果您使用 ADO.NET 和 SQL 数据提供程序，请查看 SqlCommand.Cancel 方法。这就是你要找的。但是，它会尝试取消并且取消可能需要一些时间。基本上，由 SQL Server 决定何时授予取消请求。当查询被取消时，您应该得到一个 SqlException，表明该操作已被用户取消。显然，您不想将此异常视为异常并对其进行特殊处理，例如如果 SqlException 是由于用户取消操作引起的，只需将其吞下即可。
回答by Koen
I also noticed command.Cancel() doesn't really abort the command. What worked for me is closing the connection (rollback transaction if you use one) when the user aborts. This will raise an exception in your background thread while the command is executing, so you have to catch it and check the CancellationPending property there and not rethrow the exception in that case...
我还注意到 command.Cancel() 并没有真正中止命令。对我有用的是在用户中止时关闭连接（如果使用，则为回滚事务）。这将在命令执行时在后台线程中引发异常，因此您必须捕获它并检查那里的 CancellationPending 属性，并且在这种情况下不要重新抛出异常...
// When aborting
worker.CancelAsync();
command.Connection.Close();
// In your DoWork event handler
...
catch (Exception)
{
if (worker.CancellationPending)
{
e.Cancel = true;
return;
}
else
{
throw;
}
}
// And in your RunWorkerCompleted event handler
if (e.Error == null && !e.Cancelled)
{
...
}
回答by JosephStyons
I am pretty sure it is possible- we use TOAD for Oracle, and it lets you cancel long-running queries, as described here. I'm not sure how they do it though.
我很确定这是可能的- 我们将 TOAD 用于 Oracle，它可以让您取消长时间运行的查询，如此处所述。我不确定他们是如何做到的。
回答by Hugues Van Landeghem
回答by GWLlosa
You could have the background worker fire off the actual database call on a different thread, and then periodically check to see if either the database call has finished, or cancel has been pressed, at which point you could kill off the database thread. This wouldn't actually help the database load any (as your query has been sent and is still processing) but it does release your local resources related to it.
您可以让后台工作人员在不同的线程上触发实际的数据库调用，然后定期检查数据库调用是否已完成，或者是否按下了取消，此时您可以终止数据库线程。这实际上不会帮助数据库加载任何内容（因为您的查询已发送并仍在处理中），但它确实会释放与它相关的本地资源。
回答by stuartd
回答by Burnsys
What about opening a new connection to the database, login in as sysdba and sending a "ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE" command specifying the SID of the process you want to terminate.
打开一个到数据库的新连接，以 sysdba 身份登录并发送“ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE”命令，指定要终止的进程的 SID 怎么样？
To get the sessionID: select sid from v$mystat where rownum = 1
获取 sessionID： select sid from v$mystat where rownum = 1
To get Serial#: select sid, serial# from v$session where sid = :SID
要获得 Serial#: select sid, serial# from v$session where sid = :SID
http://www.oracle-base.com/articles/misc/KillingOracleSessions.php
http://www.oracle-base.com/articles/misc/KillingOracleSessions.php
EDIT: WW idea for not Login as sysdba here: http://forums.oracle.com/forums/thread.jspa?threadID=620578
编辑：WW 不以 sysdba 身份登录的想法：http: //forums.oracle.com/forums/thread.jspa?threadID= 620578
回答by crk
I have tried both Cancel and Close with ADO 2.8 and SQLOLEDB or SQL Server native client. With Cancel, the recordset stops fetching data, but in the backround the reading from the server continues and consumes memory from the application. In a 32 bit application it can happen that you get an "out of memory" message some minutes later. When I close the recordset (or the connection, with or without Cancel before), ADO 2.8 waits until all records are fetched.
我已经尝试使用 ADO 2.8 和 SQLOLEDB 或 SQL Server 本机客户端取消和关闭。使用取消，记录集停止获取数据，但在后台继续从服务器读取并消耗应用程序的内存。在 32 位应用程序中，您可能会在几分钟后收到“内存不足”消息。当我关闭记录集（或连接，之前有或没有取消）时，ADO 2.8 会等到所有记录都被提取。
I don't know if ADO.NET does it better, but I think it's a good idea to monitor memory and network access after Cancel/Close to be sure that ADO really stops reading data.
我不知道 ADO.NET 是否做得更好，但我认为在 Cancel/Close 之后监视内存和网络访问以确保 ADO 真的停止读取数据是个好主意。
回答by Panagiotis Piperopoulos
KILL SESSION was the only working way for me to cancel the long running query. I am using the managed oracle provided and OracleCommand.Cancel() works some times but usually it's not working. Also OracleCommand.CommandTimeout is not respected according to my tests. Some time ago when i was using the unmanaged oracle provided i managed to cancel commands but not any more with the managed one. Any way killing the session was the only option. The query is not running on the UI thread but on a seperate thread. The cancel command is send from the UI thread. Its a little more complex because the application uses a middletier using WCF but at the end of the day i am killing the session. Of cource when running the query i have to find and save the session in order to kill it if necessary. There are many ways to find sid and serial# in order to kill the oracle session and i want waste your time explaining something you already know.
KILL SESSION 是我取消长时间运行的查询的唯一有效方法。我正在使用提供的托管 oracle，并且 OracleCommand.Cancel() 有时会起作用，但通常不起作用。根据我的测试，OracleCommand.CommandTimeout 也不受尊重。前段时间，当我使用提供的非托管 oracle 时，我设法取消了命令，但不再使用托管的命令。以任何方式终止会话是唯一的选择。查询不在 UI 线程上运行，而是在单独的线程上运行。取消命令是从 UI 线程发送的。它有点复杂，因为应用程序使用使用 WCF 的中间层，但在一天结束时我正在终止会话。当然，在运行查询时，我必须找到并保存会话，以便在必要时终止它。
回答by Toolkit
Oracle introduced ALTER SYSTEM CANCEL SQLin 18c. You would need to add some kind of comment with UID to your SQL and then look for it something like this
OracleALTER SYSTEM CANCEL SQL在 18c 中引入。您需要在 SQL 中添加某种带有 UID 的注释，然后查找类似这样的内容
SELECT S.SID||','||S.SERIAL#
FROM GV$SESSION S, V$SQL Q
WHERE S.USERNAME IS NOT NULL
AND S.STATUS = 'ACTIVE'
AND S.SQL_ID IS NOT NULL
AND Q.SQL_ID = S.SQL_ID
and sql_text like '%{queryId}%'
And then run another operation from .NET ALTER SYSTEM CANCEL SQL 'SID, SERIAL'
然后从 .NET 运行另一个操作 ALTER SYSTEM CANCEL SQL 'SID, SERIAL'
