Halloween Problem

Hi,
According to this page:
http://support.microsoft.com/kb/294860/EN-US
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:
http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
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,
Leila
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
Halloween Problem?
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
situation.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:uIddyxRVGHA.4884@.TK2MSFTNGP10.phx.gbl...
> Hi,
> According to this page:
> http://support.microsoft.com/kb/294860/EN-US
> 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:
> http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
> 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,
> Leila
>
|||Thanks Hal,
Actually yes! This is an academic question which I'm interested(curious) in
its answer.
By your explanation I conclude that HP can occur when:
A) Rows are relocated (in index for example) and the update is using that
index
(http://blogs.msdn.com/ianjo/archive/...31/521078.aspx)
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
applied).
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:
use tempdb
go
drop table emp
go
create table emp(
eid int primary key,
salary int)
go
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
update emp
set salary=salary*1.1
from emp emp2 with(index(a))
where eid=emp2.eid
-- Halloween=No, Because index 'a' is not used
update emp
set salary=salary*1.1
from emp emp2
where eid=emp2.eid
-- Halloween=Yes, Solved by Sort
update emp
set eid=eid*1.1
Thanks,
Leila
"Hal Berenson" <hberenson@.predictableit.com> wrote in message
news:eIWuOTSVGHA.5652@.TK2MSFTNGP09.phx.gbl...
> 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
> Halloween Problem?
> 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 situation.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uIddyxRVGHA.4884@.TK2MSFTNGP10.phx.gbl...
>