Minimizing Contention Among Transactions › Minimizing Contention › Minimizing Contention for Entities and Areas

Minimizing Contention for Entities and Areas

Guidelines

Consider the following guidelines for minimizing contention for database entities and areas:

• Minimize the use of one-of-a-kind (OOAK) entities.

  To reduce contention for an OOAK entity used for maintaining a control number (like the next order number in an order-entry system), you can manufacture the control number. For example, instead of storing the number in the database, you could determine the number dynamically from the date and time at which each order is placed.

• Avoid placing heavily-used entities in the same area. If several heavily-used entities are placed in the same database area, the area may become a source of database contention. When heavily-used entities are stored in the same area, programs may have to contend for storage space, and internally-maintained control structures such as those used for CALC processing.

  To minimize area contention, you can assign each heavily-used entity to a separate area in the database, as shown in the following diagram.

For further information on assigning entities to database areas, see Chapter 15, "Determining the Size of the Database".

• Place large indexes in separate areas. To avoid contention for space and because indexes are typically heavily used, place them in separate areas.
• Avoid long-running update transactions. Application programs that perform many updates often set many record locks. To lessen the possibility of abnormal termination as a result of setting too many locks or being involved in a deadlock, the programmer can commit database changes to release locks at intervals throughout the processing.

  This technique should be used with caution, since the commit function also causes a checkpoint to be written to the journal file. Following the unsuccessful execution of a DML function, a transaction is rolled back only to the point of the last checkpoint. Thus the existence of a checkpoint resulting from a commit statement would prohibit the system from performing a rollback to the beginning of the transaction.

• Separate frequently used and updated entities. If an entity creates excessive contention among application programs, you can segment the entity into two or more entities. For example, if the EMPLOYEE entity were a source of contention, you could break the entity into EMPERS and EMPAY. EMPERS might contain all personal information about each employee, while EMPAY could contain professional information. The two entities could then be assigned to different database areas and use different indexes.

  By segmenting employee data, you could eliminate contention between those programs that access employee personal information and those programs that only require access to professional information, as shown in the following diagram.

• Include several levels for each frequently-updated sorted index. While sorted indexes with very few levels can be used to optimize performance in retrieval applications, they sometimes cause contention between application programs that perform update functions.

  If a sorted index will be updated frequently, make sure that the index consists of at least three levels. For further information on sizing a sorted index, see Chapter 15, "Determining the Size of the Database".

• Schedule the execution of batch update jobs. In some situations, you should consider scheduling programs that execute batches of updates to reduce contention. By executing update programs one at a time, you can ensure that these programs do not have to contend for the same database resources.
• Ready areas in shared update mode. If an application program readies an area in protected or exclusive mode, other programs can be placed in a wait state. Therefore, whenever possible, programs updating a limited number of entities before a commit should ready areas in shared update mode. The shared update mode allows multiple transactions under the same central version to access the area concurrently, thereby reducing area locking and contention.