使用回滚停止查询可确保回滚

Say I have a query like this:

说我有这样的查询：

BEGIN Transaction
UPDATE Person SET Field=1
Rollback

There are one hundred million people. I stopped the query after twenty minutes. Will SQL Server rollback the records updated?

有一亿人。二十分钟后我停止了查询。 SQL Server是否会回滚更新的记录？

3 个解决方案

#1

A single update will not update some rows. It will either update all or 0.

单个更新不会更新某些行。它将全部更新或0。

So, if you cancel the query, nothing will be updated.

因此，如果您取消查询，则不会更新任何内容。

This is atomicity database systems which SQL Server follows.

这是SQL Server遵循的原子数据库系统。

In other words, you don't have to do that rollback at the end, nothing was committed anyway.

换句话说，您最后不必执行该回滚，无论如何都没有提交。

When you cancel a query, it will still hold locks until everything is rolled back so no need to panic.

当您取消查询时，它仍将保持锁定，直到所有内容都回滚，因此无需恐慌。

You could test it yourself, execute the long query, cancel it and you will notice that it takes a while before the process really end.

你可以自己测试一下，执行长查询，取消它，你会发现它需要一段时间才能真正结束。

#2

While the update statement will not complete, the transaction will still be open, so make sure you rollback manually or close out the window to kill the transaction. You can test this by including two statements in your transaction, where the first one finishes and you cancel while it's running the second - you can still commit the transaction after stopping it, and then get the first half of your results.

虽然更新语句无法完成，但事务仍将处于打开状态，因此请确保手动回滚或关闭窗口以终止事务。您可以通过在事务中包含两个语句来测试这一点，其中第一个语句完成，而在第二个语句运行时取消 - 您可以在停止事务后提交事务，然后获取结果的前半部分。

BEGIN Transaction
    UPDATE Person SET Field=1 WHERE Id = 1
    UPDATE Person SET Field=1
Rollback

If you start this, give it enough time for the first line to finish, hit the Stop button in SSMS, then execute commit transaction, you'll see that the first change did get applied. Since you obviously don't want part of a transaction to succeed, I'd just kill the whole window after you've stopped it so you can be sure everything's rolled back.

如果你开始这个，给它足够的时间让第一行完成，点击SSMS中的Stop按钮，然后执行commit transaction，你会看到第一个更改确实被应用了。既然你显然不希望某个事务的一部分成功，我会在你停止它之后杀死整个窗口，这样你就可以确保一切都回滚了。

#3

Since you have opened the Transaction, Stoping the Query manually does not completes the transaction, This transaction will still be open and all the subsequent requests to this table will be blocked.

由于您已打开事务，手动停止查询并未完成事务，此事务仍将处于打开状态，并且将阻止对此表的所有后续请求。

You can do any one of following options

您可以执行以下任一选项

Kill the Connection using the command KILL SPID (SPID is the process ID of your connection) Note: This will auto rollback the changes you made, you can monitor the rollback status with command KILL SPID WITH STATUSONLY (After killing)
使用命令KILL SPID终止连接（SPID是连接的进程ID）注意：这将自动回滚您所做的更改，您可以使用命令KILL SPID WITH STATUSONLY监视回滚状态（杀死后）
run the ROLLBACK command manually
手动运行ROLLBACK命令

** SPID is your request id, you can find it from sys.sysprocesses table/ you can also find it on Management Studio query Window the number which is within brackets / also you can find it at bottom right corner of your management studio beside the login name.

** SPID是您的请求ID，您可以从sys.sysprocesses表中找到它/您也可以在Management Studio查询窗口中找到它在括号内的数字/您也可以在管理工作室的右下角找到它登录名。

Example SQLQuery2.sql... (161) -- 161 is your spid.

示例SQLQuery2.sql ...（161） - 161是你的spid。

#1