Benjamin Nevarez Rotating Header Image

Halloween Protection

Halloween Protection

Halloween protection refers to a problem found on update operations more than thirty years ago at the IBM Almaden Research Center in San Jose, California. Researchers were testing an 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 salary 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 optimizer had selected the salary index and had updated some records more than once, until they reached the $25,000 salary. Since the salary index was used to scan the records, when the salary column was updated, some records were moved within the index and were scanned again later, updating those records more than once. The problem was called Halloween protection as it was discovered in Halloween, around 1976 or 1977.

Update operations have a read section followed by an update section. To avoid the Halloween problem the read and update sections must be separated, the read section must be completed on its entirety before running the write section. I will show you how SQL Server avoids that problem in the next example. Run the following statement to create a new table on the AdventureWorks database

SELECT * INTO dbo.Product

FROM Production.Product

Run the following UPDATE statement which produces the next execution plan

UPDATE dbo.Product

SET ListPrice = ListPrice * 1.2


In this case no Halloween protection is needed; the statement updates the ListPrice column which is not part of any index, so updating the data does not move any row. Now, to show the problem I will create a clustered index on ListPrice column

CREATE CLUSTERED INDEX cix ON dbo.Product(ListPrice)

Run the same UPDATE statement again. The query will show a similar plan but this time including a table spool, which is a blocking operator, to separate the read from the write section. A blocking operator is needed as it has to read all the rows before producing any output row to the next operator. In the example the table spool separates the clustered index scan from the clustered index update, as shown on the next execution plan. The spool operator scans the original data and saves a copy of it in a hidden spool table in tempdb before it is updated. Usually a table spool operator is used to avoid the Halloween problem as it is a cheap operator, however, if the plan already includes another operator that can be used, for example a sort, the table spool operator is not needed and the sort can perform the same job instead.


Finally drop the table you have just created

DROP TABLE dbo.Product