Rotating Header Image

It’s Halloween In September

So like most of my blog posts this is about something I didn’t know about till now… something called “The Halloween Effect”.

Calm down TronGuy… it’s not THAT kid of Halloween Effect.

I recently got asked if I had ever come across “The Halloween Effect” and if I knew what it was…

I’m a firm believer in not trying to sound like I know what I’m talking about when I have absolutely no clue so I gave my “sorry I haven’t heard of that but I can find out more information on it for you” answer.

So I BINGed (notice how I BINGed instead of GOOGLEd… that was for you @SQLGal) on SQL Halloween Effect and I came across an MSDN blog by Ian Jose

Background on Halloween Protection.

“Halloween protection” in database systems refers to a solution for a problem that can occur in update queries. The problem occurs when an update itself affects the rows selected for update.  For example, imagine a company wants to give every employee a 10% pay increase. If the update query is unlucky enough to walk the salary index then the same row may be selected, updated, the update moves the row ahead in the index, and then the row will be redundantly selected again and again.  This problem is corrected by isolating the rows chosen from the effects of the update itself.  For example, a SPOOL operation which stores all the rows to be updated outside of the context and any index can provide the necessary isolation. SORTs are also sufficient for isolation purposes.

OK so there is my context but I wanted MORE!

So I kept BANGing away and found this article by Craig Freedman:

Halloween Protection

Which has a working example, output, explainations, and it even goes through how “Halloween Effect” got it’s name.

I don’t like to rehash something that has already been explained so well but I still thought I would do a post on it so that if you ever get asked if you know what the “Halloween Effect” is you can sound alot smarter then me 😉


Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

One Comment

  1. Shaun says:

    Very good. I especially like Craig’s call-out that a developer can do this all on their own 🙂 So now the question is “Is there a way to find such code in existing code logic?”

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.