Page contention

When designing applications and databases, consider the impact of having many transactions accessing the same part of a table space. The term hot spot is often used to describe a small part of the table space, where the access density is significantly higher than the access density for the rest of the table space. Two examples of hot spots are sequential number allocation and insert in sequence.

If the pages are used for SELECT processing only, there is no concurrency problem. The pages are likely to stay in the buffer pool, so little I/O activity takes place. However, if the pages are updated frequently, you may find that you have concurrency problems, because the pages are locked from first update until syncpoint. Other transactions using the same pages have to wait. Deadlocks and timeouts often occur in connection with hot spots.

Sequential number allocation

If you use one or more counters to supply your application with new sequential numbers, consider the following points.

  • You should calculate the frequency of updates for each counter. You should also calculate the elapsed time for the update transaction, measured from update of the counter until commit. If the update frequency multiplied by the calculated elapsed time exceeds about 0.5 in peak hours, the queue time can be unacceptable.
  • If you are considering having more than one counter in the same table space, you should calculate the total counter busy time.
  • If the counters are placed in the same row, they are always locked together.
  • If they are placed in different rows in the same table space, they can be in the same page. Since the locks are obtained at the page level, the rows are also locked together in this case.
  • If the rows are forced to different pages of the same table space (for example by giving 99% free space) it is still possible that the transactions can be queued.

    When for example row 2 in page 2 is accessed, a table space scan can occur. The scan stops to wait at page number 1, if this page is locked by another transaction. You should therefore avoid a table space scan.

  • If an index is defined to avoid the table space scan, it is uncertain whether it can be used. If the number of pages in the table space is low, the index is not used.
  • A solution is then to have only one counter in each table space. This solution is preferred, if more than one CICS® system is accessing the counters.
  • If only one CICS system is accessing the counters, a BDAM file can be an alternative solution. However, the possibility of splitting the CICS system into two or more CICS systems at a later time can make this solution less attractive.

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.