Insert in sequence
In situations where many transactions are inserting rows in the same table space, consider the sequence of the inserted rows.
If you base a clustering index on a field with a time stamp, or a sequential number, DB2 tries to insert all rows adjacent to each other. The pages where the rows are inserted can then be considered a hot spot.
Note that in the clustering index, all inserts are also in the same page, within a given period.
If there is more than one index and the nonclustering index is
used for data retrieval, the risk of deadlock between index and data
is increased. In general terms, the INSERT obtains the X-locks
(exclusive
locks) in the following order:
- Clustering index leaf page
- Data page
- Nonclustering index leaf page
- Nonclustering index leaf page
- Data page
A solution to the deadlock risk is to spread the rows by choosing another index as clustering.
The general methods of how to handle deadlock situations are described in Handling deadlocks in the CICS DB2 environment.