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:
  1. Clustering index leaf page
  2. Data page
  3. Nonclustering index leaf page
When the SELECT statement uses the nonclustered index, the S-locks (shared locks) are obtained in this order:
  1. Nonclustering index leaf page
  2. Data page
This is the opposite order to the order of the INSERT locks. Often the SELECT rate is higher for the new rows. This means that the data pages are common for the INSERT and the SELECT statements. Where the index page is also the same, a deadlock can occur.

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.



dfhtkc0071.html | Timestamp icon Last updated: Monday, 5 June 2017