The index key value no-lock option can be used for all operations. However, whether or not it is to be used should be determined by taking into consideration the operation of unique indexes, the presence of any residual entries, and the size of the index log. For an explanation of uniqueness constraint assurance processing for unique indexes and residual entries, see Section (4) Notes. For the implications of the size of the index log when the index key value no-lock option is used, see the explanation on estimating the size of the index log and the number of locked resources in the HiRDB Version 8 Installation and Design Guide.

To use the index key value no-lock option, specify NONE in the pd_indexlock_mode operand in the system common definition.

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

This section explains how to prevent deadlock by using the index key value no-lock option. Figure 3-30 shows an example of the deadlock described below. For a description of the lock mode, see Section 6.10.2 Lock modes.

Figure 3-30 Example of deadlock when the index key value no-lock option is not used

Specifying the index key value no-lock option prevents the type of deadlock shown in Figure 3-31. Figure 3-31 shows an example in which deadlock is prevented because the index key value no-lock option is being used.

Figure 3-31 Example of preventing deadlock by using the index key value no-lock option

(a) Operation of uniqueness constraint assurance processing for unique indexes

In the case of a table for which the uniqueness constraint is specified, the operation of the uniqueness constraint assurance processing that is performed during addition or updating of rows depends on whether or not the index key value no-lock option is used. Uniqueness constraint assurance processing is the processing by which locking is applied during insertion of row data or updating of column values in order to ensure the uniqueness of a key value being added; key value uniqueness is determined by checking the index (unique index) to see whether or not the keyed data to be added already exists. If uniqueness constraint assurance processing locates an index key entry that uses the same key, an error occurs. Even if another party's transaction that is manipulating the index key is incomplete, and there is a possibility of a rollback being performed, an error occurs without a lock check being performed.

If you are performing table data insertion or updating processing with the uniqueness constraint specified and you want continuation of processing to take priority over waiting, you should apply the index key value no-lock option. If the insertion or updating processing must take priority, even if doing so will create delay, you should not apply the index key value no-lock option.

(b) Residual entries in a unique index

When the index key value no-lock option is used, either lock-wait or deadlock can arise in the unique index. With a unique index in the index key value no-lock option, any index key prior to the execution of the DELETE or UPDATE statement is left intact, instead of being deleted from the index. This is to maintain the uniqueness of the index. The remaining index key is called a residual entry. Although residual entries are deleted at an appropriate time when the transaction has been settled, if the INSERT or UPDATE statement is executed on the same key as a residual entry, it is possible that an unexpected delay or deadlock will result.

These problems can be prevented by creating the UAP so that it will not update any columns that are subject to the uniqueness constraint.

A deadlock between index keys can arise depending on the order in which accesses are made by a UAP. To prevent this, you must create UAPs so that they will not update columns that are subject to the uniqueness constraint.