February 09, 2010

Tackling the Halloween Problem

The Halloween Problem is the name given when a database update operation causes a change to the physical location of a row, potentially allowing the row to be processed in an infinite loop. See this wikipedia entry for more.

The problem occurs when rows are updated using an index that contains a column being updated. As the column is updated, the current index entry is deleted, creating the possibility that a new index could be inserted ahead of the current entry. When this happens, the row appears repeatedly.

Even existing production applications that are running properly are susceptible to The Halloween Problem. The reason for the sudden poor behavior is usually due to an access path change. Say the application undergoes a REBIND, which would change the access path. The statement could be using an index that doesn't contain the column being updated. After the access path change, an index with the column being updated is used.

The typical code execution is:
1)    Open CURSOR
2)    Fetch from cursor
3)    Update the row
4)    Fetch next row

Standard Rule of Thumb: Declare cursor with FOR UPDATE and then use the UPDATE WHERE CURRENT OF statement. When DB2 recognizes that a column will be updated, it avoids the index containing the updated column.

What sort of programs are vulnerable to The Halloween Problem? Look for read only cursors that are using an index containing a column being updated somewhere in the program. Even if the program is running fine, a problem could occur when the statistics are changed and the program undergoes a REBIND, which, as noted, creates a new access path.

Make sure the cursors in your programs aren't read only -- replace ORDER BY or FOR FETCH ONLY statements with FOR UPDATE OF (column name). This insures that the proper locks are taken for concurrency and prevents DB2 from processing indexes that contain columns to be updated.

There are a number of solutions to The Halloween Problem. Does your shop have a standard practice for dealing with this issue? Please take a moment and share your tips and best practices in Comments.