SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Halloween Protection


Halloween Protection

Author
Message
craig 81366
craig 81366
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 Visits: 490
GilaMonster (10/31/2012)
Andrew Diniz (10/31/2012)
After all, it is possible to observe 'Halloween Protection' in DELETE and INSERT plans too Hehe


No, the Halloween problem is exclusively for updates. You can't insert or delete a row multiple times, you can an update and that's the problem. You may see spools in insert or delete plans, but they are not there for halloween protection.



I beg to differ.
Consider a DELETE statement of the form:


DELETE TABLE_NAME
WHERE COL < CONST



as opposed to


DELETE TABLE_NAME
WHERE COL < (SELECT AVG(COL) FROM TABLE_NAME)


You'll see a spool in the second DELETE, and that spool is essential to protecting against deleting more rows than expected due to the fact that the AVG changes as a result of rows already deleted.
Basically, the DELETE condition depends on data in the table, and the condition changes as rows are deleted.
Conceptually this is exactly the same as Halloween Protection for UPDATES.

Consider another example:


DELETE TABLE_NAME
WHERE COL1 NOT IN (SELECT COL2 FROM TABLE_NAME)


using data

COL1 COL2
1 2
2 3
3 4


By deleting the row where COL1=2, we now have COL1=2 also unexpectedly matching the filter.
Similarly deleting COL1=2 could result in COL1=3 being deleted.
Without Halloween Protection, the DELETE statement empties the table.

So although Halloween Protection typically applies to UPDATE, it can apply to DELETE; and similar cases can be found for INSERT.
Andre Guerreiro
Andre Guerreiro
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2313 Visits: 1515
Thank you for this question. I knew the answer from the top of my head because I've read about it not so long ago. Excellent opportunity for this question to come up! ;-)

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17572 Visits: 7423
Good question, thanks Steve.
Actually encountered this very issue while repairing a "broken" cobol program on the mainframe a few years ago, where the developer changed the ordinal positions of the columns in the data set (by adding a column in ordinal position 1, which shifted all the other columns on) but did not change the variables in the cursor accordingly (using select * to populate the variables, of course) - really played havoc with the customer limits every time one was changed...

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Thomas Abraham
Thomas Abraham
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3879 Visits: 2256
Nakul Vachhrajani (10/31/2012)
Very nice question! With a little thought, I was certain about the answer. While this was the first time I heard about "halloween protection", the chain of thought I used was Halloween = ghosts & goblins. Ghosts = phantom. We all know about "phantom updates" and hence the answer had to be what it is.


I had the same thought, figuring it had something to do with ghosts. That was the only Halloween related term that seemed to be related to databases. Well, zombie might be too, but that only relates to DBA's that have been working too long without sleep. ;-)

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Andrew Diniz
Andrew Diniz
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 293
GilaMonster (10/31/2012)
Andrew Diniz (10/31/2012)
After all, it is possible to observe 'Halloween Protection' in DELETE and INSERT plans too Hehe


No, the Halloween problem is exclusively for updates. You can't insert or delete a row multiple times, you can an update and that's the problem. You may see spools in insert or delete plans, but they are not there for halloween protection.



Ok. Thanks for clarifying.

The scenarios I alluded to (and illustrated by craig 81366 above) seem to be resolved in a similar fashion to 'Halloween Protection'.

I assume, then, that the QO's introduction of blocking operators in INSERT and DELETE statements to address similarly undesirable effects was already in place when the 'Halloween bug' was discovered. Is this why 'Halloween Protection' relates to UPDATE statements only?
vk-kirov
vk-kirov
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4366 Visits: 4408
craig 81366 (10/31/2012)
GilaMonster (10/31/2012)

You can't insert or delete a row multiple times

I beg to differ
...
that spool is essential to protecting against deleting more rows than expected

"Delete a row multiple times" and "Delete more rows than expected" – there is a big difference, I believe.
DrKiller
DrKiller
Old Hand
Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)

Group: General Forum Members
Points: 366 Visits: 245
vk-kirov (10/31/2012)
craig 81366 (10/31/2012)
GilaMonster (10/31/2012)

You can't insert or delete a row multiple times

I beg to differ
...
that spool is essential to protecting against deleting more rows than expected

"Delete a row multiple times" and "Delete more rows than expected" – there is a big difference, I believe.


Yeah, it is all about using the exact SAME row more than once... If you INSERT the 'same' row more than once, it becomes 2 different rows in the database. If you DELETE the 'same' row more than once, you are essentially only deleting it once... Once the row is deleted, it is deleted :-)
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36770 Visits: 11361
GilaMonster (10/31/2012)
Andrew Diniz (10/31/2012)
After all, it is possible to observe 'Halloween Protection' in DELETE and INSERT plans too Hehe


No, the Halloween problem is exclusively for updates. You can't insert or delete a row multiple times, you can an update and that's the problem. You may see spools in insert or delete plans, but they are not there for halloween protection.

This is a common misconception. Internally, all data-changing operations are referred to as updates. This includes insert, delete, update, and merge. Confusing for SQL people perhaps, but that's the way it is. This usage in Ian's post seems to have misled Steve today because Halloween Protection is most definitely required (and a strategy computed) for update plans (I/U/D/M) in general, not just updates. There were several bugs (in SQL Server 7/2000 I think) that related to incorrect HP for inserts and deletes with self-joins, for example.

Craig Freedman states this explicitly at the end of the following entry:

http://blogs.msdn.com/b/craigfr/archive/2008/02/27/halloween-protection.aspx

edit: Adding KB http://support.microsoft.com/kb/810026



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
(Bob Brown)
(Bob Brown)
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1067 Visits: 1145
Thanks for the great question. Had to do a little reading to get the answer.
craig 81366
craig 81366
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 Visits: 490
vk-kirov (10/31/2012)
craig 81366 (10/31/2012)
GilaMonster (10/31/2012)

You can't insert or delete a row multiple times

I beg to differ
...
that spool is essential to protecting against deleting more rows than expected

"Delete a row multiple times" and "Delete more rows than expected" – there is a big difference, I believe.

It seems to me that you're quibbling about how a questionable statement by GilaMonster differs from mine.
Perhaps take a look at the very last sentence of Craig Freedman's (not me) blog post:
http://blogs.msdn.com/b/craigfr/archive/2008/02/27/halloween-protection.aspx
He says he's been working for the SQL Server team for quite a few years, so probably can be considered authoritative on the matter. ;-)

Edit: To clarify... it's not the statement "cannot delete a row multiple times" that I consider questionable. (Obviously :-P) It's questionable to use that as the explanation why DELETE's don't apply to the Halloween Problem.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search