In computing, the Halloween Problem refers to a phenomenon in
database
In computing, a database is an organized collection of data or a type of data store based on the use of a database management system (DBMS), the software that interacts with end users, applications, and the database itself to capture and a ...
s in which an
update operation causes a change in the physical location of a row, potentially allowing the row to be visited again later in the same update operation. This could even cause an infinite loop in some cases where updates continually place the updated record ahead of the scan performing the update operation.
The potential for this database error was first discovered by
Don Chamberlin,
Pat Selinger, and
Morton Astrahan in the mid-1970s, on
Halloween
Halloween, or Hallowe'en (less commonly known as Allhalloween, All Hallows' Eve, or All Saints' Eve), is a celebration geography of Halloween, observed in many countries on 31 October, the eve of the Western Christianity, Western Christian f ...
day, while working on
query optimization
Query optimization is a feature of many relational database management systems and other databases such as NoSQL and graph databases. The query optimizer attempts to determine the most efficient way to execute a given query by considering the po ...
.
They wrote a
SQL query supposed to give a ten percent raise to every employee who earned less than $25,000. This query would run successfully, with no errors, but when finished all the employees in the database earned at least $25,000, because it kept giving them a raise until they reached that level. The expectation was that the query would iterate over each of the employee records with a salary less than $25,000 precisely once. In fact, because even updated records were visible to the query execution engine and so continued to match the query's criteria, salary records were matching multiple times and each time being given a 10% raise until they were all greater than $25,000.
Contrary to what some believe, the name is not descriptive of the nature of the problem but rather was given due to the day it was discovered on. As recounted by Don Chamberlin:
Pat and Morton discovered this problem on Halloween... I remember they came into my office and said, "Chamberlin, look at this. We have to make sure that when the optimizer is making a plan for processing an update, it doesn't use an index that is based on the field that is being updated. How are we going to do that?" It happened to be on a Friday, and we said, "Listen, we are not going to be able to solve this problem this afternoon. Let's just give it a name. We'll call it the Halloween Problem and we'll work on it next week." And it turns out it has been called that ever since.
References
The 1995 SQL Reunion (Protokoll)The "Halloween Problem" for XML APIs Mike Champion's weblog.
Databases
{{database-stub