I came across a piece of code in a sql stored proc in our code base where it was using chunking without transaction block. I don’t see how chunking could be beneficial without tran block? I've been humbled a few times when I've jumped into conclusion without digging more, so what advantage does chunking without tran block offer? Is there any?
我在代码库中的sql存储过程中遇到了一段代码,它使用的是没有事务块的分块。我没有看到如果没有tran块,分块是如何有益的?当我在没有挖掘更多内容的情况下进入结论时,我已经被贬低了几次,那么没有tran block提供的分块有什么优势呢?有没有?
The pseudocode is something like:
伪代码类似于:
-
Populate the Main temptable (ID, Name, UpdatedFlag). This flag column indicates whether the record has been updated or not.
填充主要临时表(ID,Name,UpdatedFlag)。此标志列指示记录是否已更新。
-
Start while loop (do as long as there is a record in MainTable with UpdatedFlag = 0)
启动while循环(只要MainTable中有一条记录,并且UpdatedFlag = 0)
- Only select the given chunkSize into ChunkSizeMain tempTable (ID, Name) from the records that hasn’t been marked as updated
- Begin TRY block
- Start updating some other table by joining on ID of ChunkSizeMainTable.
- Update UpdatedFlag = 1 in MainTable.
- End try
- Begin catch //some action End Catch
仅从未标记为已更新的记录中选择给定的chunkSize到ChunkSizeMain tempTable(ID,Name)
开始TRY块
通过加入ChunkSizeMainTable的ID开始更新其他表。
在MainTable中更新UpdatedFlag = 1。
开始捕捉//一些动作结束捕获
1 个解决方案
#1
1
Every update query in SQL Server runs in a transaction irrespective of whether it has a BEGIN TRAN next to it. (autocommit transaction if implicit_transaction is not on)
SQL Server中的每个更新查询都在事务中运行,而不管它旁边是否有BEGIN TRAN。 (如果未启用implicit_transaction,则自动提交事务)
"Chunking" is usually done to stop the transaction log needing to increase in size when the database is in simple recovery mode. A single UPDATE statement that affects 1 million rows will need to have all of that logged to the active log. Dividing into batches can allow the log from earlier committed batches to be truncated and reused by later batches.
当数据库处于简单恢复模式时,通常会执行“分块”以停止需要增加大小的事务日志。影响100万行的单个UPDATE语句将需要将所有这些记录到活动日志中。分成批处理可以允许先前提交的批处理中的日志被截断并在以后的批处理中重用。
It may also be done to reduce the effect on concurrent queries by reducing the length of time of each operation and/or potentially reducing the risk of lock escalation by only updating a few thousand rows at a time.
还可以通过减少每个操作的时间长度来减少对并发查询的影响和/或通过一次仅更新几千行来潜在地降低锁升级的风险。
#1
1
Every update query in SQL Server runs in a transaction irrespective of whether it has a BEGIN TRAN next to it. (autocommit transaction if implicit_transaction is not on)
SQL Server中的每个更新查询都在事务中运行,而不管它旁边是否有BEGIN TRAN。 (如果未启用implicit_transaction,则自动提交事务)
"Chunking" is usually done to stop the transaction log needing to increase in size when the database is in simple recovery mode. A single UPDATE statement that affects 1 million rows will need to have all of that logged to the active log. Dividing into batches can allow the log from earlier committed batches to be truncated and reused by later batches.
当数据库处于简单恢复模式时,通常会执行“分块”以停止需要增加大小的事务日志。影响100万行的单个UPDATE语句将需要将所有这些记录到活动日志中。分成批处理可以允许先前提交的批处理中的日志被截断并在以后的批处理中重用。
It may also be done to reduce the effect on concurrent queries by reducing the length of time of each operation and/or potentially reducing the risk of lock escalation by only updating a few thousand rows at a time.
还可以通过减少每个操作的时间长度来减少对并发查询的影响和/或通过一次仅更新几千行来潜在地降低锁升级的风险。