Nonstop Database, HiRDB Version 9 UAP Development Guide
Non-locking of index key values is when index key values are not locked. In this case, only the table data is locked.
When non-locking of index key values is applied, index key values cannot be locked during retrieval processing that uses an index. Also, in table update processing (row insertion, row deletion, or column value updating), index key values for the index defined in the update-target column cannot be locked.
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 9 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 9 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. The following figure 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
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 for transaction settlement during insertion or update processing of table data for which the uniqueness constraint was specified, apply non-locking of index key values. To give priority to attempting insertion or update processing even if waiting is involved, also apply non-locking of index key values or lock the uncommitted data to be deleted by specifying WAIT in the pd_lock_uncommited_delete_data operand. For details about locking uncommitted data to be deleted, see 3.4.7 Locking uncommitted data to be deleted.
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.
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.
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.