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.

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.

Two examples of hot spots are sequential number allocation and insert in sequence.