在这个 while 循环中需要明确的事务吗？

Question

在这个 while 循环中需要明确的事务吗？

sql-servert-sqlsql-server-2014transaction

13

SQL Server 2014：我们有一个非常大的表（1亿行），需要更新其中几个字段。为了日志传送等，显然我们希望将其分解成可控的事务。如果我们让下面的查询运行一段时间，然后取消/终止查询，已经完成的工作是否会被提交，还是我们需要添加明确的BEGIN TRANSACTION / END TRANSACTION语句，以便随时可以取消？

DECLARE @CHUNK_SIZE int
SET @CHUNK_SIZE = 10000

UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
where deleted is null or deletedDate is null

WHILE @@ROWCOUNT > 0
BEGIN
    UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
    where deleted is null or deletedDate is null
END

- Jonesome Reinstate Monica

1个回答

相关问题
4 在 while 循环中显示局部变量

- Solomon Rutzky · Accepted Answer

个别语句 -- DML、DDL等 -- 本身就是事务。所以，是的，在每次循环迭代之后（技术上说，是在每个语句之后），无论那个UPDATE语句改变了什么，都已经自动提交。

当然，总有例外，对吧？通过SET IMPLICIT_TRANSACTIONS可以启用隐式事务，在这种情况下，第一个UPDATE语句将开始一个事务，你需要在最后COMMIT或ROLLBACK。这是一个会话级别的设置，默认情况下大多数情况下是关闭的。

我们需要添加显式的BEGIN TRANSACTION / END TRANSACTION语句，以便随时取消吗？

不。实际上，考虑到您希望能够停止进程并重新启动，添加显式事务（或启用隐式事务）是一个坏主意，因为在进程执行提交之前停止可能会导致事务被捕获。在这种情况下，您需要手动发出提交命令（如果您在SSMS中），或者如果您正在从SQL Agent作业运行此命令，则没有机会进行提交，可能会导致孤立的事务。

此外，您可能希望将@CHUNK_SIZE设置为较小的数字。锁升级通常发生在对单个对象获取5000个锁时。根据行的大小以及是执行行锁还是页锁，您可能会超过这个限制。如果一行的大小只能容纳每页1或2行，那么即使执行页锁，您也可能始终遇到这个问题。如果表已分区，则可以选择为表设置LOCK_ESCALATION选项（在SQL Server 2008中引入），以便在升级时仅锁定分区而不是整个表。或者，对于任何表，您可以将相同的选项设置为DISABLE，尽管您必须非常小心。有关详细信息，请参阅ALTER TABLE。这里有一些关于锁升级和阈值的文档：锁升级（它适用于"SQL Server 2008 R2及更高版本"）。还有一篇博客文章讨论了检测和修复锁升级的问题：Microsoft SQL Server中的锁定（第12部分 - 锁升级）。

与确切问题无关，但与问题中的查询相关，这里可以进行一些改进（至少从外观上看是这样的）：对于你的循环，使用WHILE (@@ROWCOUNT = @CHUNK_SIZE)会稍微好一些，因为如果在最后一次迭代中更新的行数少于要求的UPDATE数量，则没有剩余的工作要做。如果deleted字段是BIT数据类型，那么该值是否确定取决于deletedDate是否为2000-01-01？为什么需要两者？如果这两个字段是新添加的，并且您将它们添加为NULL，以便可以进行在线/非阻塞操作，并且现在想要将它们更新为它们的“默认”值，那么这是不必要的。从SQL Server 2012开始（仅适用于企业版），添加具有DEFAULT约束的NOT NULL列是非阻塞操作，只要DEFAULT的值是常量。因此，如果您尚未使用这些字段，只需删除并重新添加为NOT NULL，并带有DEFAULT约束即可。如果在执行此UPDATE时没有其他进程正在更新这些字段，那么如果您将要更新的记录排队，然后只需处理该队列，速度会更快。当前方法存在性能损耗，因为每次都必须重新查询表以获取需要更改的集合。相反，您可以执行以下操作，该操作仅在这两个字段上扫描一次表，然后仅发出非常有针对性的UPDATE语句。此外，停止进程并稍后重新启动时不会有任何惩罚，因为队列的初始填充将仅找到剩余要更新的记录。 1. 创建一个临时表（#FullSet），其中只包含聚集索引的键字段。 2. 创建第二个具有相同结构的临时表（#CurrentSet）。 3. 通过SELECT TOP(n) KeyField1, KeyField2 FROM [huge-table] where deleted is null or deletedDate is null;向#FullSet插入数据。这里的TOP(n)是由于表的大小。在表中有1亿行的情况下，您不需要用整个键集填充队列表，特别是如果您计划定期停止进程并稍后重新启动它。因此，可以将n设置为100万，并让其完成。您可以安排这个SQL Agent作业运行一组100万（甚至更少）然后等待下一个预定时间再次开始。然后，只需在没有其他任务时删除该作业即可 :-) 4. 在循环中执行以下操作： a. 通过类似DELETE TOP (4995) FROM #FullSet OUTPUT Deleted.KeyField INTO #CurrentSet (KeyField);的方式填充当前批次。 b. IF (@@ROWCOUNT = 0) BREAK; c. 使用类似

UPDATE ht SET ht.deleted = 0, ht.deletedDate='2000-01-01' FROM [huge-table] ht INNER JOIN #CurrentSet cs ON cs.KeyField = ht.KeyField;

的方式进行更新。 d. 清空当前集合：TRUNCATE TABLE #CurrentSet; 在某些情况下，为了帮助输入到#FullSet临时表的SELECT语句，添加一个过滤索引可能会有所帮助。以下是与添加此类索引相关的一些考虑事项： 1. WHERE条件应与查询的WHERE条件匹配，因此为WHERE deleted is null or deletedDate is null 2. 在开始时，大多数行将与WHERE条件匹配，因此索引并不那么有用。您可能要等到大约50%左右才添加它。当然，由于几个因素的影响，它有多

更新：请查看我对与此问题相关的问题的回答，以获取上述建议的完整实现，包括跟踪状态和干净取消的机制：sql server: 更新大表中的字段：如何获取进度/状态？