Re: Halloween Problem - 03-31-2006 , 07:39 PM
Is there a practical reason for this question, or is it an academic one? In
other words, do you have an update where you think you are seeing the
The Halloween Problem is a classic database problem wherein the membership
in the set you are reading is changed by your own update operation, causing
you to see the same row repeatedly. The theoretical problem is independent
of the underlying rdbms implementation. The explanation in the first KB
article is incomplete in describing the problem. For example, even if rows
did not actually move a query plan that referenced any index incorporating a
column that is also being modified might be subject to the HP. In early
rdbms products it was up to the user to avoid making update requests that
would cause the Halloween Problem. In newer products, such as SQL Server
7.0/2000/2005 the Query Optimizer takes care of the problem via a technique
called Halloween Protection. When it sees that the update you are
performing could change one of the inputs it generates a plan that won't
have that problem. For example, it will spool the impacted inputs to a
temporary workfile before performing any updates and then read from the
temporary workfile rather than the underlying data that is being updated..
The second KB article is just pointing out a situation where the Query
Optimizer was not producing the correct plan. I didn't look at it in depth,
but I suspect that the reason Loop Join didn't have the problem is that the
optimizer was generating the correct Halloween Protection for that
Hal Berenson, President
"Leila" <Leilas (AT) hotpop (DOT) com> wrote
Halloween Problem - 04-01-2006 , 05:38 PM
According to this page:
Halloween problem occurs where the physical location of a row within a table
changes due to a modification operation. As a result, the same row may be
revisited multiple times within the context of a single logical operation.
I found an example of Halloween problem in this page:
In this example, physical location of a row does not change during the
update operation. The "test" table has one index (clustered). The rows will
be moved only if the clustered key is updated (which does not occur here).
1) How does Halloween problem happen here?
2) Why Loop Join is not encountered with this problem?
Thanks in advance,
Re: Halloween Problem - 04-02-2006 , 05:10 AM
Actually yes! This is an academic question which I'm interested(curious) in
By your explanation I conclude that HP can occur when:
A) Rows are relocated (in index for example) and the update is using that
B) Update command accesses the rows that have been updated during itself
(which must be isolated)
According to KBs, HP is eliminated after installing SQL Server 2000 SP1. I
was trying this on a instance without SP.
The plan of update command in second KB which I mentioned is different from
instance with SP (This sample causes item B).
But what ever I tried to simulate HP by the cause of rows' relocation (item
A), the plan used a table spool which I think prevents HP (even no SP was
Do you have any sample that simulates that (Maybe my own experminet is
wrong)? or type A is always detected by the query optimizer and is prevented
even without SP?
I also include my code that used for testing item A:
drop table emp
create table emp(
eid int primary key,
insert emp select 1,100
insert emp select 2,200
insert emp select 3,300
insert emp select 4,400
create index a on emp(salary)
-- Halloween=Yes, Solved by Table Spool
from emp emp2 with(index(a))
-- Halloween=No, Because index 'a' is not used
from emp emp2
-- Halloween=Yes, Solved by Sort
"Hal Berenson" <hberenson (AT) predictableit (DOT) com> wrote