Halloween Protection

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715375

    Comments posted to this topic are about the item Halloween Protection

  • Ron McCullough

    SSC Guru

    Points: 63877

    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: 104767

    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

    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

    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

    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: 258927

    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

    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: 1004446

    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: 10153

    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
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • craig 81366

    SSC Eights!

    Points: 808

    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

    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

  • This was removed by the editor as SPAM

  • Thomas Abraham

    SSChampion

    Points: 10761

    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

    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?

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

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