Task 10: Halloween Problem

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
------------------------------------------------------------------------------