Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Halloween Protection


Halloween Protection

Author
Message
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4111 Visits: 72512
bitbucket-25253 (10/30/2012)
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


Same here... I tried to think of what statement I'd want to protect against the most... and it was a toss up between update and delete.... sadly I picked the wrong one.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Divine Flame
Divine Flame
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1575 Visits: 2802
Nice interesting question Steve:-)


Sujeet Singh
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21155 Visits: 18259
Happy Halloween All



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10787 Visits: 12023
craig 81366 (10/31/2012)
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.

You can differ as much as you like, but what is called the Halloween Problem (and its soluttion Halloween Protection) is a matter of real history which is on the record, not about what you choose to think it's about.

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.

Actually, that's nonsense. It isn't possible to compute the AVG aggregate without a spool, the purpose of the spool is to allow AVG to be computed, nothing else. It is of course rather a good thing that the spool just happens to ensure that the Halloween Problem can't arise, so that no Halloween protection is needed in this case. One side effect of that good thing is that that 36 years ago (or was it only 35?) at this time of year the System R team were not discussing an issue with delete, but an issue with update only, because the delete issue didn't exist; and the Halloween Problem is the name that they gave to the problem they discussed that day, and that is a problem with update only, and Halloween Protectiopn means protectin from that update only problem, not anything else. At the 1995 System R/SQL reunion surviving members of the team talked about the early development of the language now called SQL, and Pat Selinger (she may still have been Pat Griffiths when she and Morton Astrahan identified this problem, or maybe she was already married by then) gave a description of this particular thing. Paul McJones did a transcript of the whole reunion, published on the web later that year, and a couple of years later made it available as an SRC technical note, which is currently accessible at his wesite and (in a Russian version) at CITForum.ru. The relevant passage is here.

I guess my point is that if it's not the problem that those two people brought to the design issues meeting on Halloween that year, or about the solution to those issues, it is not the Halloween Problem, or Halloween Protection. Calling it that would be about as accurate as calling the Battle of the Nile the Battle of the Plate - unhistorical nonsense.

Tom

Revenant
Revenant
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5839 Visits: 4722
All, have a scary Halloween!

Steve, thanks for the question!
craig 81366
craig 81366
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 486
L' Eomot Inversé (10/31/2012)
craig 81366 (10/31/2012)
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.

You can differ as much as you like, but what is called the Halloween Problem (and its soluttion Halloween Protection) is a matter of real history which is on the record, not about what you choose to think it's about.

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.

Actually, that's nonsense. It isn't possible to compute the AVG aggregate without a spool, the purpose of the spool is to allow AVG to be computed, nothing else.

Really?
Then why when I run the following by itself, does the plan not spool?
SELECT AVG(COL) FROM TABLE_NAME


An average can be calculated and maintained incrementally without spooling data.


... 36 years ago (or was it only 35?) at this time of year the System R team were not discussing an issue with delete, but an issue with update only ... Paul McJones did a transcript of the whole reunion, published on the web later that year, and a couple of years later made it available ... The relevant passage is here.

Thanks for that interesting piece of history, and for the link. There's definitely some interesting reading there.
However, I didn't see anything that claimed the Halloween Problem was considered exclusive to the UPDATE statement; in spite of that being how it was discovered. In fact, Pat Selinger went on to say: "An interesting footnote is that we just discovered another one of these as sort of a variation on that, in the latest work that we did having to do with referential integrity and things like that, where the referential integrity relationships were going to trigger off the same kind of nonstop behavior."
In a nutshell, the problem boils down to: a data operation is expected to behave in a predetermined way, but is not due to a side effect of its work unexpectedly affecting its own behaviour recursively.


I guess my point is that if it's not the problem that those two people brought to the design issues meeting on Halloween that year, or about the solution to those issues, it is not the Halloween Problem, or Halloween Protection. Calling it that would be about as accurate as calling the Battle of the Nile the Battle of the Plate - unhistorical nonsense.

In contrast to your historical analogy, consider this:
If someone were to observe a ball and decide: Due to your properties of "roundedness", I dub thee sphere.
And later someone else observes that the Earth has the same properties of "roundedness", you're also a sphere. Would that be wrong?
D.Oc
D.Oc
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1017 Visits: 6480
Well,

Happy Halloween!!!

-------------------------------------------------------------
"It takes 15 minutes to learn the game and a lifetime to master"
"Share your knowledge. It's a way to achieve immortality."

Kick6Tiger
Kick6Tiger
SSChasing Mays
SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)

Group: General Forum Members
Points: 641 Visits: 761
I stumbled across Freedman's explanation in my search on the subject and was thrown by the options as well. Either way, I selected the UPDATE choice as the most likely candidate.

UPDATE: Found the patent and the update is referring to an update plan (any data modification).
http://www.google.com/patents/US6122644

Further down the doc there is a section that states:
38. The method of claim 18 wherein said generating step includes: generating an update plan wherein said update operator is an insert operator.
39. The method of claim 18 wherein said generating step includes: generating an update plan wherein said update operator is a delete operator

Here is also a KB article that specifically indicates that an INSERT, UPDATE, or DELETE statement may need Halloween protection. http://support.microsoft.com/kb/294860

Aigle de Guerre!
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10787 Visits: 12023
This probably a pointless argumnent, because it isn't about anything technical, just about historical and terminological accuracy, and it does appear that quite a few people have adopted historically inaccurate terminology (which is a pretty frequent phenomenon - for example we call Heath's normal form, published in 1971, "Boyce-Codd Normal Form" after a paper published several years later, although the earlier paper was certainly known to the senior of the authors of the later paper). But I'm going to make the argument for historical accuracy (especially since several of the people involved are still alive) anyway (if I were in touch with any of them I would ask them for their views, but unfortunately I'm not).
craig 81366 (10/31/2012)
L' Eomot Inversé (10/31/2012)

Actually, that's nonsense. It isn't possible to compute the AVG aggregate without a spool, the purpose of the spool is to allow AVG to be computed, nothing else.

Really?
Then why when I run the following by itself, does the plan not spool?
SELECT AVG(COL) FROM TABLE_NAME



Because a displayed MS SQL query plan only shows spool operations when they are not blatantly obvious (which I think is eminently sensible); no other reason. If you think you can deliver a result for a query for AVG without first reading all the records, then think again, because you are horribly wrong. You can't deliver the first value of AVG until you have done that. Then, why read the records again? Sure, you could track changes in AVG as the table changed, if you wanted to and had an awful lot of compute power to spare, but poeple neither wanted to nor had that power to spare so in the early versions of SQL (or of SEQUEL, or whatever it was called before it became SEQUEL) AVG was not recomputed. It isn't today, not because some additional spool operation, beyond what is required to compute AVG in the first place, is introduced, but because AVG has to be computed.

An average can be calculated and maintained incrementally without spooling data.
It can't be computed for the first time without a spool (ie reading all the input before going to the next step).

... 36 years ago (or was it only 35?) at this time of year the System R team were not discussing an issue with delete, but an issue with update only ... Paul McJones did a transcript of the whole reunion, published on the web later that year, and a couple of years later made it available ... The relevant passage is here.

Thanks for that interesting piece of history, and for the link. There's definitely some interesting reading there.
However, I didn't see anything that claimed the Halloween Problem was considered exclusive to the UPDATE statement; in spite of that being how it was discovered. In fact, Pat Selinger went on to say: "An interesting footnote is that we just discovered another one of these as sort of a variation on that, in the latest work that we did having to do with referential integrity and things like that, where the referential integrity relationships were going to trigger off the same kind of nonstop behavior."

So "something that's "a sort of variation on that" is an instance of "that"? YG&LF, or perhaps GHU - what else can I say? Why did she say "a sort of variation on that" rather than just "another instance of that" or "another example of that", I wonder? Perhaps she though the various possible phrases had different interpretations, and chose one which can't reasonably nbe taken to mean what you want it to mean precisely because what you want it to mean is not what she intended to say? I don't know, I wasn't there at that meeting, but I'm not the one trying to attribute to her a meaning which what she actually said cleafrly doesn't imply.
In contrast to your historical analogy, consider this:
If someone were to observe a ball and decide: Due to your properties of "roundedness", I dub thee sphere.
And later someone else observes that the Earth has the same properties of "roundedness", you're also a sphere. Would that be wrong?

So all spheroids are spheres, are they? They are all round - any corners would make them not spheroids. Even on a technical, as opposed to historical, basis your argument doesn't fly. Of course if in your sphere example the properties of roundness concerned require both that there be a single focus and that all diameters be the same (either of those is a consequence of the other in some geometries, but not in all) then yes, the new thing would be a sphere. That's rather a big and nasty requirement on the properties, I guess the equivalent condition wrt the Halloween Problem would be that it involved an insert statement, so you are seem to be comparing tautologies with contradictions - not usually a useful passtime.

Tom

TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10787 Visits: 12023
Meow Now (10/31/2012)
I stumbled across Freedman's explanation in my search on the subject and was thrown by the options as well. Either way, I selected the UPDATE choice as the most likely candidate.

UPDATE: Found the patent and the update is referring to an update plan (any data modification).
http://www.google.com/patents/US6122644

Isn't life wonderful. Here we have a problem identified by IBM back in the 70s, and solved by them back in the 70s, back in the days when the US sopreme Court was still sufficiently sane to assert that software patents were illegal. Then MS gets a patent on solving it in 2000, with a patent that has no non-obvious invention in it (at least nothing non-obvious to anyone familiar with the state of the art in, I think, the late 70s) after the law has been changed (by magic, or perhaps by the appointment of supreme court judges favoured by presidents whose campaign funds came from industrialists who wanted software patents).
We all knew the US patent system had become insane some time ago. What none of us outside the US know is why you pretend that your plutocracy is a democracy, claiming that the people rule when in fact only the money rules, as clearly evidenced by the changes in the last few decades to your copyright system and your patent system (amongst many other indicators).

Tom

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