主页 / dba / 问题 / 115252
Accepted
Jonesome Reinstate Monica
Asked: 2015-09-17 06:59:02 +0800 CST

在这个 while 循环中是否需要显式事务？

  • 772

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
sql-server t-sql

1 个回答

  1. Best Answer

    单独的语句——DML、DDL 等——本身就是事务。所以是的，在循环的每次迭代之后（技术上：在每条语句之后），无论该UPDATE语句更改了什么都已自动提交。

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

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

    不。事实上，鉴于您希望能够停止进程并重新启动，添加显式事务（或启用隐式事务）将是一个坏主意，因为停止进程可能会在它执行COMMIT. 在这种情况下，您需要手动发出COMMIT（如果您在 SSMS 中），或者如果您是从 SQL 代理作业运行它，那么您就没有这个机会，并且可能会以孤立的事务告终。

    此外，您可能希望设置@CHUNK_SIZE为较小的数字。锁升级通常发生在单个对象上获得 5000 个锁时。根据行的大小，如果它正在执行行锁与页锁，您可能会超过该限制。如果一行的大小使得每页只能容纳 1 或 2 行，那么即使它正在执行页面锁定，您也可能总是会遇到这个问题。

    如果表是分区的，那么您可以选择为表设置LOCK_ESCALATION选项（在 SQL Server 2008 中引入），AUTO以便在升级时仅锁定分区而不是整个表。或者，对于任何表，您都可以将相同的选项设置为DISABLE，尽管您必须非常小心。有关详细信息，请参阅ALTER TABLE。

    以下是一些关于锁升级和阈值的文档：锁升级（它说适用于“SQL Server 2008 R2 及更高版本”）。这是一篇关于检测和修复锁升级的博客文章：Locking in Microsoft SQL Server (Part 12 – Lock Escalation)。

    与确切的问题无关，但与问题中的查询有关，可以在此处进行一些改进（或者至少从查看来看似乎是这样）：

    1. 对于您的循环，执行WHILE (@@ROWCOUNT = @CHUNK_SIZE)会稍微好一些，因为如果在最后一次迭代中更新的行数少于请求更新的数量，那么就没有工作要做了。

    2. 如果该deleted字段是一个BIT数据类型，那么该值不是由是否deletedDate是确定的2000-01-01吗？为什么两者都需要？

    3. 如果这两个字段是新的并且您添加了它们，NULL那么它可能是一个在线/非阻塞操作并且现在想要将它们更新为它们的“默认”值，那么这不是必需的。从 SQL Server 2012（仅限企业版）开始，NOT NULL只要 DEFAULT 的值为常量，添加具有 DEFAULT 约束的列就是非阻塞操作。因此，如果您尚未使用这些字段，只需将其删除并重新添加为NOT NULLDEFAULT 约束即可。

    4. 如果在您执行此 UPDATE 时没有其他进程正在更新这些字段，那么如果您将要更新的记录排入队列，然后从该队列中取出，则速度会更快。当前方法中存在性能损失，因为您必须每次重新查询表以获取需要更改的集合。相反，您可以执行以下操作，仅在这两个字段上扫描一次表，然后仅发出非常有针对性的 UPDATE 语句。随时停止进程并稍后启动它也不会造成任何损失，因为队列的初始填充只会找到剩下要更新的记录。

      1. 创建一个临时表 (#FullSet)，其中仅包含聚集索引中的关键字段。
      2. 创建具有相同结构的第二个临时表 (#CurrentSet)。

      3. 通过插入#FullSetSELECT TOP(n) KeyField1, KeyField2 FROM [huge-table] where deleted is null or deletedDate is null;

        由于TOP(n)桌子的大小，它在那里。由于表中有 1 亿行，您实际上并不需要使用整组键来填充队列表，特别是如果您计划每隔一段时间停止该进程并稍后重新启动它。所以可能设置n为 100 万，然后让它一直运行到完成。您始终可以在运行 100 万（甚至更少）的集合的 SQL 代理作业中安排此操作，然后等待下一个安排的时间再次启动。然后，您可以安排每 20 分钟运行一次，这样在n. 然后在无事可做时让工作自行删除:-)。

      4. 在一个循环中，执行：
        1. 通过类似的方式填充当前批次DELETE TOP (4995) FROM #FullSet OUTPUT Deleted.KeyField INTO #CurrentSet (KeyField);
        2. IF (@@ROWCOUNT = 0) BREAK;
        3. 使用以下方法进行更新：UPDATE ht SET ht.deleted = 0, ht.deletedDate='2000-01-01' FROM [huge-table] ht INNER JOIN #CurrentSet cs ON cs.KeyField = ht.KeyField;
        4. 清除当前集合：TRUNCATE TABLE #CurrentSet;
    5. 在某些情况下，它有助于添加过滤索引以帮助将SELECT其馈送到#FullSet临时表中。以下是与添加此类索引相关的一些注意事项：
      1. WHERE 条件应与查询的 WHERE 条件匹配，因此WHERE deleted is null or deletedDate is null
      2. 在该过程开始时，大多数行将匹配您的 WHERE 条件，因此索引没有那么有用。您可能要等到 50% 左右再添加它。当然，它有多大帮助以及何时最好添加索引会因多种因素而异，因此需要反复试验。
      3. 您可能必须手动 UPDATE STATS 和/或 REBUILD 索引以使其保持最新，因为基础数据经常更改
      4. 请务必记住，索引在帮助 的同时SELECT会伤害 ，UPDATE因为它是在该操作期间必须更新的另一个对象，因此需要更多的 I/O。这既适用于使用过滤索引（当您更新行时会缩小，因为与过滤器匹配的行较少），并等待一段时间添加索引（如果它在开始时不会很有帮助，那么没有理由招致额外的 I/O）。

    更新：请参阅我对与此问题相关的问题的回答，以全面实施上述建议，包括跟踪状态和彻底取消的机制：sql server：以小块更新大表上的字段：如何获取进展/状态？

    • 15

相关问题