Halloween is only a few days away which makes this a great time to talk about the Halloween problem. No, I’m not talking about the struggle to find the right costume or those houses that gave out raisins instead of candy. I am of course, talking about the database version of a Halloween problem.
Origin Story
The Halloween problem we’re discussing today was first encountered back in 1974. As discussed in this interview from 1995, Don Chamberlin, Pat Selinger, and Morton Astrahan were working on a query to give all employees making less than $25,000 a raise of 10%. Sounds simple enough, right?
Unfortunately, a terrifying “trick” reared its ugly head. Instead of a 10% raise being applied once, employees were repeatedly given raises until breaking the $25,000 threshold.
Avoid Becoming a Halloween Victim
If we’re making the same type of update in SQL Server, you’ll often see a spool operator that makes sure data is updated correctly.
The spool takes in the data from a nonclustered index and acts as a placeholder for changes. Without the spool, the index could be used to read and write data. This becomes a problem when a row is updated, moved further down the index, and eventually updated multiple times.
SQL Server is good about handling this for you but it’s always good to understand how the execution plan is being designed.
Some are born for Halloween, Some are just counting days ’til Christmas
Why is this scenario called the Halloween problem? While scary, the name simply comes from being discovered on Halloween. “Halloween problem” has a much better ring to it than “that problem with salary raises.” I suppose if it had come along a few months later it would be called the Christmas Problem. The catchy name has helped it to live on as something still talked about here in 2021.
Keeps Getting Scarier
If you want to take a deep dive into the Halloween problem (and how it can be an issue for more than just UPDATE statements), check out Paul White’s four part series on the subject.
Thanks for reading!
Chad Callihan 