0

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?

The pseudocode is something like:

  • Populate the Main temptable (ID, Name, UpdatedFlag). This flag column indicates whether the record has been updated or not.

  • Start while loop (do as long as there is a record in MainTable with 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
haku
  • 4,105
  • 7
  • 38
  • 63

1 Answers1

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)

"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.

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Does it mean that Begin Tran on Update statements are redundant? I just ran a test and sql doesn't update any row if Update fails (I never noted, smh). Now it totally make sense what the code I was looking at doing. The same isn't the case with Insert and Delete, Begin Tran must be specified, right? – haku Jul 15 '15 at 20:31
  • @NoSaidTheCompiler - All statements run in a transaction not just update. For a single statement there is no benefit of using an explicit transaction. – Martin Smith Jul 15 '15 at 20:38
  • Depending on how fancy you want to get, it may be beneficial to update the temp table and the "live" table atomically (that is within one transaction). – Ben Thul Jul 16 '15 at 03:40