Non-locking of index key values should normally be applied.

However, the uniqueness constraint assurance operations, remaining entries for unique indexes, and the system log size that is output during table data update must be considered when deciding whether to apply non-locking of index key values. For details about the uniqueness constraint assurance operations and remaining entries for unique indexes, see (4)(b) Remaining entries for unique indexes. For details about the system log size that is output during table data update, see the HiRDB Version 8 Installation and Design Guide.

To apply non-locking of index key values, specify NONE in the system definition for the pd_indexlock_mode operand. For details about the pd_indexlock_mode operand, see the HiRDB Version 8 System Definition manual.

If the value specified for the pd_inner_replica_control operand in the system definition is greater than 1, NONE is assumed for the system definition's pd_indexlock_mode operand regardless of the actual specification of the pd_indexlock_mode operand.

Deadlocks like the one shown in Figure 3-8 can be avoided by applying non-locking of index key values. Figure 3-12 shows an example of deadlock avoidance by applying non-locking of index key values.

Figure 3-12 Example of deadlock avoidance by applying non-locking of index key values

(a) Uniqueness constraint assurance operations for unique indexes

When non-locking of index key values is applied, the uniqueness constraint assurance operations in row addition and update are different from the operations for the index key value method (a method that does not use non-locking of index key values) in tables for which the uniqueness constraint is specified. These operational differences must be considered when non-locking of index key values is applied.

Uniqueness constraint assurance processing checks whether the data for keys to be added by using an index (unique index) is already in the table. This processing also guarantees the uniqueness of added keys.

In uniqueness constraint assurance processing, if index key entries that have the same key are found, a uniqueness error occurs immediately. Even if the other transaction operating that index key has the uncomplete status for transaction determination and rollback is possible, the HiRDB system indicates a uniqueness error immediately, without executing a lock check.

To continue processing instead of waiting during insertion or update processing of table data for which the uniqueness constraint was specified, apply non-locking of index key values. Also apply non-locking of index key values to give priority to attempting insertion or update processing even if waiting is involved.

(b) Remaining entries for unique indexes

Lock-release wait or deadlock may occur in a unique index when non-locking of index key values is applied.

When non-locking of index key values is applied to a unique index, the index key before DELETE or UPDATE statement execution is kept instead of being deleted so that the uniqueness constraint is assured. This remaining index key is called a remaining entry. Although this remaining entry is deleted at the appropriate timing after transaction determination, if an INSERT or UPDATE statement is executed for the same key as the remaining entry, an unexpectedly long wait period or deadlock may occur, depending on when the statement is executed.

To prevent these conditions, create UAPs so that they do not update columns that have the uniqueness constraint.

(c) Deadlocks that cannot be avoided even when non-locking of index key values is applied

Depending on the access sequence of the UAP, a deadlock may occur between index keys. To prevent this condition, create UAPs so that they do not update columns that have the uniqueness constraint.