Are explicit transactions needed in this while loop?

Question

SQL Server 2014:

We have a very large (100million row) table, and we need to update a couple of fields on it.

For log shipping, etc, we also, obviously, want to keep it to bite-size transactions.

If we let the below run for a bit, and then cancel/terminate the query, will the work done so far all be committed, or do we need to add explicit BEGIN TRANSACTION / END TRANSACTION statements so that we can cancel any time?

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

Answer 1

Individual statements -- DML, DDL, etc -- are transactions in themselves. So yes, after each iteration of the loop (technically: after each statement), whatever that UPDATE statement changed has been auto-committed.

Of course, there is always an exception, right? It is possible to enable Implicit Transactions via SET IMPLICIT_TRANSACTIONS, in which case the first UPDATE statement would start a transaction that you would have to COMMIT or ROLLBACK at the end. This is a session level setting that is OFF by default in most cases.

do we need to add explicit BEGIN TRANSACTION / END TRANSACTION statements so that we can cancel any time?

No. And in fact, given that you want to be able to stop the process and restart, adding an explicit transaction (or enabling Implicit Transactions) would be a bad idea since stopping the process might catch it prior to it doing the COMMIT. In that case you would need to manually issue the COMMIT (if you are in SSMS), or if you are running this from a SQL Agent job, then you do not have that opportunity and might end up with an orphaned transaction.

---

Also, you might want to set @CHUNK_SIZE to a smaller number. Lock escalation generally happens at 5000 locks acquired on a single object. Depending on the size of the rows and if it is doing Row locks vs Page locks, you might be going over that limit. If the size of a row is such that only 1 or 2 rows fit per each page, then you might always be hitting this even if it is doing Page locks.

If the table is partitioned then you have the option of setting the LOCK_ESCALATION option (introduced in SQL Server 2008) for the table to AUTO so that it locks only the partition and not the entire table upon escalating. Or, for any table you can set that same option to DISABLE, though you would have to be very careful about that. See ALTER TABLE for details.

Here is some documentation that talks about Lock Escalation and the thresholds: Lock Escalation (it says is applies to "SQL Server 2008 R2 and higher versions"). And here is a blog post that deals with detecting and fixing lock escalation: Locking in Microsoft SQL Server (Part 12 – Lock Escalation).

---

Unrelated to the exact question, but related to the query in the question, there are a few improvements that could be made here (or at least it seems that way just from looking at it):

1. For your loop, doing WHILE (@@ROWCOUNT = @CHUNK_SIZE) is slightly better since if the number of rows updated on the last iteration is less than the amount requested to UPDATE, then there is no work left to do.

2. If the deleted field is a BIT datatype, then isn't that value determined by whether or not deletedDate is 2000-01-01? Why do you need both?

3. If these two fields are new and you added them as NULL so it could be an online / non-blocking operation and are now wanting to update them to their "default" value, then that wasn't necessary. Starting in SQL Server 2012 (Enterprise Edition only), adding NOT NULL columns that have a DEFAULT constraint are non-blocking operations as long as the value of the DEFAULT is a constant. So if you aren't using the fields yet, just drop and re-add as NOT NULL and with a DEFAULT constraint.

4. If no other process is updating these fields while you are doing this UPDATE, then it would be faster if you queued the records that you wanted to update and then just work off that queue. There is a performance hit in the current method as you have to re-query the table each time to get the set that needs to be changed. Instead, you could do the following which only scans the table once on those two fields and then issues only very targeted UPDATE statements. There is also no penalty from stopping the process at any time and starting it later since the initial population of the queue will simply find the records left to update.

   1. Create a temporary table (#FullSet) that just has the key fields from the clustered index in it.
   2. Create a second temporary table (#CurrentSet) of that same structure.

   3. insert into #FullSet via SELECT TOP(n) KeyField1, KeyField2 FROM [huge-table] where deleted is null or deletedDate is null;

      The TOP(n) is in there due to the size of the table. With 100 Million rows in the table, you don't really need to populate the queue table with that entire set of keys, especially if you plan on stopping the process every so often and restarting it later. So maybe set n to 1 million and let that run through to completion. You can always schedule this in a SQL Agent job that runs the set of 1 million (or maybe even less) and then waits for the next scheduled time to pick up again. You can then schedule to run every 20 minutes so there will be some enforced breathing room between sets of n, but it will still finish the entire process unattended. Then just have the job delete itself when there is nothing more to do :-).

   4. in a loop, do:
      1. Populate the current batch via something like DELETE TOP (4995) FROM #FullSet OUTPUT Deleted.KeyField INTO #CurrentSet (KeyField);
      2. IF (@@ROWCOUNT = 0) BREAK;
      3. Do the UPDATE using something like: 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. Clear out the current set: TRUNCATE TABLE #CurrentSet;
5. In some cases it helps to add a Filtered Index to assist the SELECT that feeds into the #FullSet temp table. Here are some considerations related to adding such an index:
   1. The WHERE condition should match the WHERE condition of your query, hence WHERE deleted is null or deletedDate is null
   2. At the beginning of the process, most rows will match your WHERE condition, so an index isn't that helpful. You might want to wait until somewhere around the 50% mark before adding this. Of course, how much it helps and when it is best to add the index vary due to several factors, so it is a bit of trial and error.
   3. You might have to manually UPDATE STATS and/or REBUILD the index to keep it up to date since the base data is changing quite frequently
   4. Be sure to keep in mind that the index, while helping the SELECT, will hurt the UPDATE since it is another object that must be updated during that operation, hence more I/O. This plays into both using a Filtered Index (which shrinks as you update rows since fewer rows match the filter), and waiting a little while to add the index (if it's not going to be super helpful in the beginning, then no reason to incur the additional I/O).

UPDATE: Please see my answer to a question that is related to this question for the full implementation of what is suggested above, including a mechanism to track status and cancel cleanly: sql server: updating fields on huge table in small chunks: how to get progress/status?