In computing, the Halloween Problem refers to a phenomenon in
database
In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases sp ...
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
Donald D. Chamberlin is an American computer scientist who is one of the principal designers of the original SQL language specification with Raymond F. Boyce, Raymond Boyce. He also made significant contributions to the development of XQuery.
C ...
,
Pat Selinger, and
Morton Astrahan in 1976, on
Halloween
Halloween or Hallowe'en (less commonly known as Allhalloween, All Hallows' Eve, or All Saints' Eve) is a celebration observed in many countries on 31 October, the eve of the Western Christian feast of All Saints' Day. It begins the observanc ...
day while working on a query that was 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.
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.
* ''(archive)'
A well-intentioned query and the Halloween Problem Los Alamos National Laboratory, Anecdotes, IEEE Annals of the History of Computing
Databases
{{database-stub