Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Halloween Protection Expand / Collapse
Author
Message
Posted Wednesday, October 31, 2012 4:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:32 AM
Points: 45, Visits: 338
GilaMonster (10/31/2012)
Andrew Diniz (10/31/2012)
After all, it is possible to observe 'Halloween Protection' in DELETE and INSERT plans too


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.
Post #1379229
Posted Wednesday, October 31, 2012 4:32 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:33 AM
Points: 900, Visits: 1,489
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
Post #1379232
Posted Wednesday, October 31, 2012 5:08 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 3,964, Visits: 5,203
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”
Post #1379244
Posted Wednesday, October 31, 2012 5:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:35 AM
Points: 1,825, Visits: 2,175
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
Post #1379246
Posted Wednesday, October 31, 2012 5:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:11 AM
Points: 216, Visits: 273
GilaMonster (10/31/2012)
Andrew Diniz (10/31/2012)
After all, it is possible to observe 'Halloween Protection' in DELETE and INSERT plans too


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?
Post #1379249
Posted Wednesday, October 31, 2012 5:37 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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.
Post #1379255
Posted Wednesday, October 31, 2012 6:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 2:24 AM
Points: 196, Visits: 164
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
Post #1379273
Posted Wednesday, October 31, 2012 6:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:03 AM
Points: 11,194, Visits: 11,167
GilaMonster (10/31/2012)
Andrew Diniz (10/31/2012)
After all, it is possible to observe 'Halloween Protection' in DELETE and INSERT plans too


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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1379275
Posted Wednesday, October 31, 2012 6:29 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 12, 2014 4:19 AM
Points: 701, Visits: 1,145
Thanks for the great question. Had to do a little reading to get the answer.
Post #1379278
Posted Wednesday, October 31, 2012 6:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:32 AM
Points: 45, Visits: 338
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 ) It's questionable to use that as the explanation why DELETE's don't apply to the Halloween Problem.
Post #1379279
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse