Belated Trick or Treat: It's good to be INSENSITIVE on Halloween
Do you know what the Halloween Problem is?
I learned about it first in 1997 when learning SQL from Don Chamberlin.
Learning from Don meant I implemented inline SQL Function support. He designed and implemented the quality assurance breaking within minutes what I believed to be bullet proof design. "Learning from failure" is how you call that I think.
The story of the Halloween Problem is nicely recounted on Wikipedia. In it's essence it involves:
This is generally unintended and quite possibly leads to an infinite loop.
Being Canadian I imagine this phenomenon to be about as enjoyable as aiming the exhaust of my snow thrower straight forward.
DB2 defends against such effects by injecting so-called dams into the query execution which separate the action from the scan as necessary.
Let's see if we can find one:
CREATE TABLE T(c1 INT); INSERT INTO T VALUES (1), (2), (3);
There it is! TEMP(4) is the dam preventing the scan over T from going on forever when we insert into that same table.
Unsurprisingly this works:
INSERT INTO T SELECT * FROM T; SELECT * FROM T ORDER BY c1; C1
What will happen if we change things up and introduce procedural logic?
That is we separate the scan from the action so they reside in different SQL statements:
Well, this is taking a bit long, ... too long. In fact we must interrupt the query with CTRL-C:DELETE FROM T; INSERT INTO T VALUES (1), (2), (3);
SQL0431N User defined function "SQL
We have just seen the Halloween Problem in action.
Every row we scanned got re-inserted at the end of the table.
That's where we found and re-inserted it forever.
Is there anything we can do about this?
Before DB2 9.7.2 there are several things that can be done:
So in DB2 9.7.2 we decided to provide an option to dam any result set in DB2 directly.
Scrollable cursors, a more complex variation of a simple result set, are from the ground up designed to deal with the changing of tables upon which they are designed.
A scrollable cursor can be:
Its meaning is that the cursor result set shall be completely evaluated at OPEN.
--#SET TERMINATOR @ BEGIN FOR row AS cur INSENSITIVE CURSOR FOR SELECT * FROM T DO INSERT INTO T VALUES(row.c1); END FOR; END @ --#SET TERMINATOR ; SELECT * FROM T ORDER BY c1; C1
Great, that worked as expected. The FOR loop is no longer impacted by the modification to the very table it is reading from.
Now, you may decide that this behavior is something you want to have always enabled, or always enabled for certain routines.
DB2 provides for that.
To make all cursors used within SQL PL (or PL/SQL) INSENSITIVE (Unless they are defined as FOR UPDATE) you can use the routine compilation options either at the instance or the session level:
DELETE FROM T; INSERT INTO T VALUES (1), (2), (3);
Please be advised that materializing the result sets of cursors on OPEN does consume both memory and CPU resources.
So I do not recommend turning it on always.