November 10, 2009

Sequential Insert Hotspot Relief

A common problem database administrators (DBAs) try to avoid when designing an application is a primary key or index that is for ever increasing like a sequence number or timestamp. DBAs go out of their way to avoid keys like this because of locking contention issues. During the insert process, DB2 acquires a lock on the index page. If multiple transactions with a sequential key are inserting at the same time, you'll more than likely experience a slowdown--and in the worst case, you'll get deadlock or timeout failures. This seems to be even more of a problem when online transactions are processing in a data sharing environment.

DB2 9 for z/OS offers relief in this area by allowing the insert of the sequential column through a new RANDOM option on both the CREATE INDEX and ALTER INDEX statements. When RANDOM is used on the column in an index, DB2 stores the key values in random locations within the index tree. This reduces the chance of ever-increasing sequential inserts being on the same page, which decreases the chance of having lock contention.

Randomly ordered indexes can be used with equality lookups on a specific column. In addition, randomly ordered key columns can be used in non-matching index scans, while index-only access can be used in random key columns.

Here's an example of a CREATE INDEX using the RANDOM clause:

CREATE INDEX creator.table_name_idx1
       ON creator.table_name        
       (COL1                       ASC    
       ,COL2                       RANDOM)   

RANDOM cannot be specified on a key column that is varying in length with the NOT PADDED option. If you've taken advantage of RANDOM key columns in your application, please take a moment and tell us of your experience in Comments.