Halloween Protection

Question

Halloween Protection

Steve Jones - SSC Editor

SSC Guru

Points: 736534
More actions
October 30, 2012 at 8:09 pm

#75666

Comments posted to this topic are about the item Halloween Protection
October 31, 2012 at 5:08 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 54 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Hmm caught me on that one with Halloween coming up in the USA tomorrow thought this was one of those joke questions ... OH well so be it

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

TomThomson SSC Guru Points: 104773 More actions · Answer 2

Nice question.

I wonder how many people here ever used System R. I didn't, for one.

Now, was it Halloween 1976 or Halloween 1977 when it acquired its name (or, more exactly, when the problem got the name "the Halloween Problem")?

Tom

kalyani.k478 Default port Points: 1400 More actions · Answer 3

ha...nice question....

thought this a fun ques category,,,n guessed the answer...got it right though:)

Finally learnt the concept through the link..thanks:)

demonfox SSCertifiable Points: 6289 More actions · Answer 4

cool ; i didn't know ....

nice question

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Lokesh Vij SSChampion Points: 10836 More actions · Answer 5

Unaware of "Halloween Protection", I thought this to be some kind of a funny question [cooked-up] initially 🙂

Got to learn something new, which I was unaware of!

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 6

Great question! Forced me to do some research.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Andrew Diniz SSCommitted Points: 1852 More actions · Answer 7

Nice one! I came across this last year whilst studying for an exam.

Shouldn't the options, CREATE, DELETE, INSERT, UPDATE and SELECT be checkboxes though as opposed to radio buttons? After all, it is possible to observe 'Halloween Protection' in DELETE and INSERT plans too :hehe:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 9

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.

This one has to be the best of the fun series - simply because it teaches something new. Further reading into "Halloween protection" gave me some great insight into what it really is and how can we prevent it.

Happy Halloween, everyone! (And for those who got caught in the storm, I pray that you did not face any major losses).

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

craig 81366 SSC Eights! Points: 808 More actions · Answer 10

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 SSCertifiable Points: 7319 More actions · Answer 11

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

Thomas Abraham SSChampion Points: 10761 More actions · Answer 12

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

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Andrew Diniz SSCommitted Points: 1852 More actions · Answer 13

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?