The Halloween Problem

35 years ago today, a group of database professionals ran into a problem they coined “The Halloween Problem”. The group was writing a query that was supposed to give every employee who did not earn at least $25,000 a year, a 10 percent raise. When the query was completed, every single employee had a salary of $25,000. The update did not return any errors.

After deep research the team discovered that each updated record was incorrectly visible again to the query execution engine which made the rows available for updating again and again until each salary was $25,000 for each row.

There are several versions of this story available online.

1. Many sites talk about the update occurring on data used by the index and the update physically moves the rows in which they are made available again for updating.
2. Other sites talk about the query optimizer generating an incorrect plan in which rows were incorrectly made available for update.

There is also talk that table spools help prevent the Halloween problem, but I have not researched this.

Happy Halloween!!