Did you heard about Halloween Problem?
In computing, the Halloween Problem refers to a phenomenon in databases in which an update operation causes a change in the physical location of a row, potentially allowing the row to be visited more than once during the 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.
Please have a look to understand what is the meaning of this problem through an example:
-------------------------------------------------------------------------------
--- Halloween problem script ---
-------------------------------------------------------------------------------
CREATE TABLE halloweenProblem
(
Pk int NOT NULL PRIMARY KEY,
Name varchar(20),
Salary money
)
insert into halloweenProblem(pk, Name, Salary) values(1, 'Joe Blow', 20000.00)
insert into halloweenProblem(pk, Name, Salary) values(2, 'Joe Smith', 30000.00)
insert into halloweenProblem(pk, Name, Salary) values(3, 'Jane Doe', 40000.00)
insert into halloweenProblem(pk, Name, Salary) values(4, 'Boss Man', 50000.00)
DECLARE RaiseSalary CURSOR
FOR
SELECT salary, pk
FROM halloweenProblem
WHERE salary < 50000
ORDER BY pk
FOR UPDATE OF pk
OPEN RaiseSalary
FETCH RaiseSalary
WHILE ( @@fetch_status =0)
BEGIN
UPDATE halloweenProblem
SET pk=pk*10
WHERE CURRENT OF RaiseSalary
SELECT * FROM halloweenProblem
FETCH RaiseSalary
END
CLOSE RaiseSalary
DEALLOCATE RaiseSalary
DECLARE abc CURSOR FOR
SELECT salary
FROM halloweenProblem
WHERE salary < 50000
ORDER BY salary
FOR UPDATE OF salary
OPEN abc
GO
FETCH NEXT FROM abc
GO
UPDATE halloweenProblem
SET salary= salary*2
WHERE CURRENT OF abc
GO
CLOSE abc
DEALLOCATE abc
------------------------------------------------------------------------------
