Halloween problem
The Halloween problem is a phenomenon that can occur when a database is accessed, in which an update depends on what it changes due to a linked condition. It was discovered by database developers at IBM in the 1970s . The name derives from the day of discovery, October 31, when the festival of Halloween is celebrated.
Examples of the Halloween problem
For example, a Halloween problem occurs with the following applications:
- In every row in which the value of column A meets a criterion K, the value of column A should be changed.
- The original form of the Halloween problem. In this specific case, all employees of a company should be given a raise if they did not already earn more than $ 25,000. , A statement with the intention "Give every employee who earns less than $ 25,000, a raise of 10%" meant that happy employees earned all at least $ 25,000, as the system increased the salary until the condition of the update - Statements no longer worked.
- You want to add another row for each row in a table .
- A concrete example of this incarnation of the Halloween problem is a table in which each record should be duplicated. If this is where the Halloween problem arises, it manifests itself as doubling the results of the doubling in turn.
Avoidance
The SQL standard provides that all data records must be copied to a temporary table before triggers can be used. Changes then no longer affect triggers. To avoid a Halloween problem with other tasks, such temporary tables can be created manually for the duration of a database query. This means that, for example, a salary increase for all employees with a pay below a minimum salary can be carried out in such a way that in a first step all employees with a correspondingly low salary are identified and stored in a temporary table. The salary is then increased once for all employees who are included in the temporary table.
Technical details on the creation
One possible reason for a Halloween problem is that an update changes the location of a row. For example, an update may require the data in a row to be moved to the end or to another free location in a database. If the database system then simply goes through all the data records one after the other when processing the update statement, it can happen that these shifted data records are processed again.