Halloween protection refers to a problem that appears in certain update operations and was found more than 30 years ago by researchers working on the System R project at the IBM Almaden Research Center. The System R team was testing a query optimizer when they ran a query to update the salary column on an Employee table. The query was supposed to give a 10 percent raise to every employee with a salary of less than $25,000, but to their surprise, no employee had a salary under $25,000 after the update query was completed. They noticed that the query optimizer had selected the salary index and had updated some records multiple times until they reached the $25,000 salary. Because the salary index was used to scan the records, when the salary column was updated, some records were moved within the index and were then scanned again later, and those records were updated more than once. The problem was called the Halloween problem because it was discovered on Halloween, probably in 1976 or 1977.
Update operations have a read section followed by an update section, and that is a crucial distinction to bear in mind at this stage. To avoid the Halloween problem, the read and update sections must be completely separated; the read section must be completed in its entirety before the write section is run. The following example shows you how SQL Server avoids the Halloween problem.
Run the following statement to create a new table:
SELECT * INTO dbo.Product FROM Production.Product
Run the following UPDATE statement, which produces the execution plan on Figure 1:
Figure 1 An update without Halloween protection
UPDATE dbo.Product SET ListPrice = ListPrice * 1.2
No Halloween protection is needed in this case because the statement updates the ListPrice column, which is not part of any index, so updating the data does not move any rows around. Now, to demonstrate the problem, let’s create a clustered index on the ListPrice column, like so:
CREATE CLUSTERED INDEX CIX_ListPrice ON dbo.Product(ListPrice)
Run the previous UPDATE statement again. The query will show a similar plan, but this time including a Table Spool operator, which is a blocking operator, separating the read section from the write section. A blocking operator has to read all of the relevant rows before producing any output rows to the next operator. In this example, the table spool separates the Clustered Index Scan from the Clustered Index Update, as shown in Figure 2.
Figure 2 An update with Halloween protection
The spool operator scans the original data and saves a copy of it in a hidden spool table in tempdb before it is updated. A Table Spool operator is generally used to avoid the Halloween problem because it is a cheap operator. However, if the plan already includes another operator that can be used, such as a Sort, then the Table Spool operator is not needed, and the Sort can perform the same blocking job instead.
Finally, drop the table you have just created.