如果我停止长时间运行的查询，它是否回滚？

本文介绍了如果我停止长时间运行的查询，它是否回滚？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

用于循环 17百万条记录以删除重复的查询现在已经运行了大约 16小时，我想知道查询是否正确停止现在，如果它将完成删除语句，或者如果它在运行此查询时已被删除？确实，如果我停止它，它是否完成删除或回滚？

A query that is used to loop through 17 millions records to remove duplicates has been running now for about 16 hours and I wanted to know if the query is stopped right now if it will finalize the delete statements or if it has been deleting while running this query? Indeed, if I do stop it, does it finalize the deletes or rolls back?

我发现当我做一个

select count(*) from myTable

它返回的行（执行此查询时）比起始行计数少约5。显然，服务器资源非常差，这是否意味着这个过程需要16个小时才能找到5个重复（实际上有数千个），而且这可能会运行几天？

That the rows that it returns (while doing this query) is about 5 less than what the starting row count was. Obviously the server resources are extremely poor, so does that mean that this process has taken 16 hours to find 5 duplicates (when there are actually thousands), and this could be running for days?

这个查询在2000行的测试数据上花费了6秒钟时间，并且在这组数据上工作得很好，所以我认为完成后需要15个小时。

This query took 6 seconds on 2000 rows of test data, and it works great on that set of data, so I figured it would take 15 hours for the complete set.

任何想法？

以下是查询：

--Declare the looping variable DECLARE @LoopVar char(10) DECLARE --Set private variables that will be used throughout @long DECIMAL, @lat DECIMAL, @phoneNumber char(10), @businessname varchar(64), @winner char(10) SET @LoopVar = (SELECT MIN(RecordID) FROM MyTable) WHILE @LoopVar is not null BEGIN --initialize the private variables (essentially this is a .ctor) SELECT @long = null, @lat = null, @businessname = null, @phoneNumber = null, @winner = null -- load data from the row declared when setting @LoopVar SELECT @long = longitude, @lat = latitude, @businessname = BusinessName, @phoneNumber = Phone FROM MyTable WHERE RecordID = @LoopVar --find the winning row with that data. The winning row means SELECT top 1 @Winner = RecordID FROM MyTable WHERE @long = longitude AND @lat = latitude AND @businessname = BusinessName AND @phoneNumber = Phone ORDER BY CASE WHEN webAddress is not null THEN 1 ELSE 2 END, CASE WHEN caption1 is not null THEN 1 ELSE 2 END, CASE WHEN caption2 is not null THEN 1 ELSE 2 END, RecordID --delete any losers. DELETE FROM MyTable WHERE @long = longitude AND @lat = latitude AND @businessname = BusinessName AND @phoneNumber = Phone AND @winner != RecordID -- prep the next loop value to go ahead and perform the next duplicate query. SET @LoopVar = (SELECT MIN(RecordID) FROM MyTable WHERE @LoopVar < RecordID) END

推荐答案

否，如果停止查询执行，sql server将不会回滚其已执行的删除。 oracle需要明确提交操作查询或数据回滚，而不是mssql。

no, sql server will not roll back the deletes it has already performed if you stop query execution. oracle requires an explicit committal of action queries or the data gets rolled back, but not mssql.

与sql server不会回滚，除非您在上下文中运行的事务，并且您回滚该事务，否则连接在没有提交事务的情况下关闭。但是我在上面的查询中没有看到一个事务上下文。

with sql server it will not roll back unless you are specifically running in the context of a transaction and you rollback that transaction, or the connection closes without the transaction having been committed. but i don't see a transaction context in your above query.

你也可以尝试重新构造你的查询，使删除更有效率，但是基本上是你的盒子的规格没有达到鼻烟，那么你可能会被卡住等待它。

you could also try re-structuring your query to make the deletes a little more efficient, but essentially if the specs of your box are not up to snuff then you might be stuck waiting it out.

向前，你应该在桌子上创建一个唯一的索引，以保持自己不得不再次通过这个。

going forward, you should create a unique index on the table to keep yourself from having to go through this again.