2.2.17 Operands related to lock

73) pd_lck_deadlock_info = Y | N
Specifies whether deadlock information, timeout information, and locked resource management table information is to be output. These types of information are output to a directory named $PDDIR/spool/pdlckinf. For details about deadlock, timeout, and locked resource management table information, see the HiRDB Version 9 System Operation Guide.
Y:
Outputs deadlock information, timeout information, and locked resource management table information.
N:
Does not output deadlock information, timeout information, or locked resource management table information.
Relationship to other operands
  • When N is specified in the pd_lck_deadlock_check operand, this operand is assumed to be N.
  • This operand is related to the pd_lck_deadlock_check operand.
74) pd_lck_wait_timeout = lock-release-wait-time
~<unsigned integer>((0-65535))<<180>> (seconds)
Specifies in seconds the maximum amount of time to wait for lock release (the maximum amount of time in lock release wait status).This is the elapsed time from when a lock release request is placed in wait status until it is released.
If the wait status is not released within the specified amount of time, the SQL statement will return an error. When 0 is specified, lock wait time will not be monitored and waiting will continue until the wait status is released.
Relationship to client environment definition
  • PDLCKWAITTIME
    The value of this operand can be modified for a client. To do so, specify PDLCKWAITTIME in the client environment definition. For details about PDLCKWAITTIME, see the HiRDB Version 9 UAP Development Guide.
  • PDCWAITTIME
    By setting each operand so that the following relationship is satisfied, timeout information can be output when the wait status is not released even after the lock-release wait time comes.
    PDCWAITTIME value specified in the client environment definition > pd_lck_wait_timeout value (PDLCKWAITTIME value if the lock-release wait time is specified for each client)
    For details about PDCWAITTIME, see the HiRDB Version 9 UAP Development Guide. For details about timeout information, see the HiRDB Version 9 System Operation Guide.
Relationship to other operands
  • This operand is related to the following operands:
    [Figure]pd_lck_deadlock_check
    [Figure] pd_lck_deadlock_check_interval
    [Figure] pd_lck_deadlock_info
75) pd_nowait_scan_option = LOCK | NOLOCK
Specifies the processing method to be used when WITHOUT LOCK NOWAIT search is executed.
LOCK:
When a WITHOUT LOCK NOWAIT search is executed, locking is applied on a row-by-row basis. Only those rows that are not yet being updated or have already been updated by other transactions can be referenced. A row that is being updated by another transaction cannot be referenced until the updating process is finished.
NOLOCK:
When a WITHOUT LOCK NOWAIT search is executed, locking is not applied. Even those rows being updated by other transactions can be referenced. Note, however, that when a row being updated is referenced, it might not be possible to receive the search target row in some cases.
Note
When you specify LOCK for this operand and update a non-fixed table or execute a WITHOUT LOCK NOWAIT search, the number of locked resources increases by 1 per transaction. Furthermore, the base row log volume for each piece of data that is output by an update operation using an UPDATE statement for a non-fixed table increases by 628 bytes.
Effects on individual estimation formulas
If the value of the pd_nowait_scan_option operand is changed, the following estimation formula is affected:
HiRDB Version 9 Installation and Design Guide:
  • Determining the amount of base row log information under Amount of system log information output during table data updating
76) pd_lck_queue_limit = trigger-for-issuing-warning-about-number-of-users-waiting-for-lock-release
~<unsigned integer>((0-500))<<10>>
Specifies the number of users waiting for lock release of a resource at which time a warning message is to be issued. When the number of users who are waiting for the same resource to be released reaches the specified value, the KFPS00446-W warning message is issued. This message is output to the message log file and the syslogfile.
When 0 is specified, no warning message is issued.
77) pd_deadlock_priority_use = Y | N
Specifies whether deadlock priorities are to be used.
Y:
Use priority control to determine the program that is to be executed first when deadlock occurs. When deadlock occurs, the SQL of the program with the lower priority is terminated with an error. If both programs have the same priority, the SQL of the program that executed its transaction most recently is terminated with an error. The deadlock priorities are specified with the PDDLKPRIO operand of the client environment definition. For details about the PDDLKPRIO operand of the client environment definition, see the HiRDB Version 9 UAP Development Guide.
N:
Terminate with an error the SQL of the program that executed its transaction most recently.
78) pd_command_deadlock_priority = 32 | 64 | 96 | 120
Specifies the deadlock priority value of a command.
The value specified for this operand is effective for the following operation commands:
  • pdhold -b (reference-possible backup hold)
  • pdhold -s (synchronizes holds)
  • pddbchg (switches the replica status for a replica RDAREA)
  • pdorbegin (commits a database for online reorganization)
  • pdorend (performs reflection processing for online reorganization)
For details about how to change the deadlock priority value of a command, see the HiRDB Version 9 System Operation Guide.
Condition
Y must be specified for the pd_deadlock_priority_use operand.
Specification guideline
If a deadlock occurs in a system that allows a job program to make a retry, you can increase the priority of the operation command to cause an error in the job program. The smaller the specified value, the higher the priority during a lock.
79) pd_key_resource_type = TYPE1 | TYPE2
Specifies the method of creating a locked resource for an index key value. For details about how to create a locked resource for an index key value, see the HiRDB Version 9 UAP Development Guide.
TYPE1:
Create a locked resource for an index key value by using bit shifts and exclusive-OR operations.
TYPE2:
Create a locked resource for an index key value by using byte-order exclusive-OR operations.
Specification guidelines
  • As a rule, TYPE1 is specified. If the key length exceeds 10 bytes, lock conflicts might occur due to synonyms in the locked resource for the index key value. Specification of TYPE1 can prevent this.
  • TYPE2 is used for HiRDB Version 5.0, 05-02 or versions earlier than 4.0, 04-05. A user with an upgraded system is recommended to specify TYPE2 if the earlier method does not cause lock conflicts due to synonyms in the locked resource for the index key value.
80) pd_indexlock_mode = {KEY | NONE}
Specifies the locking method for a B-tree index.
KEY: Lock using index key value.
NONE: Do not lock using index key value (executes index key value no lock).
For details about index key value no lock, see the HiRDB Version 9 UAP Development Guide.
Specification guidelines
Hitachi normally recommends NONE. However, in the event of updating a unique index (adding a deleted key value), either specify KEY or consider operating the system so that there are no residual entries.
Relationship to other operands
If the pd_inner_replica_control operand is specified, NONE is assumed as this operand's value.
This operand is related to the pd_lock_uncommited_delete_data operand. For details, see the explanation of the pd_lock_uncommited_delete_data operand.
81) pd_lock_uncommited_delete_data = WAIT | NOWAIT
Specifies the lock release wait control mode for uncommitted delete data.
WAIT:
Use the mode that waits for lock release until the completion status is determined for a transaction that is performing a deletion or update during a search for a line being deleted by a DELETE statement or before an index key being updated by an UPDATE statement is updated, and then returns the search result based on the data after the transaction determination is completed. This also guarantees a uniqueness constraint with the same mode as index key value lock. For details about index key value locks, see the HiRDB Version 9 UAP Development Guide.
NOWAIT:
Use the mode that returns the search result without waiting until the completion status is determined for a transaction that is performing a deletion or update during a search for a line being deleted by a DELETE statement or before an index key being updated by an UPDATE statement is updated.
Notes
In the following cases, this operand is ignored even though WAIT is specified:
  • During searching or updating a dictionary table
  • During searching or updating without defining an index in a table for which the WITHOUT ROLLBACK option is specified.
When WAIT is specified in this operand, the following restrictions apply to the usable size of the HiRDB file that constitutes the table storage RDAREA of the table for which the index is defined:
Page size of table storage RDAREA (bytes)pd_lock_uncommited_delete_data specification
WAITNOWAIT
4,09632 GB64 GB
6,14448 GB
Other64 GB
If the HiRDB file already exceeds the usable size or you intend to use a file that exceeds the usable size, modify the page size of the table storage RDAREA to at least 8,192 bytes. In the following cases, continuing operation without modifying the page size will result in an error and the KFPA19176-E warning message will be output:
  • When allocating an area that will exceed the usable size when a row is added
  • When updating or deleting a row stored in a page of an area that exceeds the usable size
Relationship to client environment definition
When YES is specified in the PDLOCKSKIP operand of the client environment definition, this operand is ignored even if WAIT is specified, and search processing using condition evaluation without locking will take priority. For this reason, row deletion does not wait for lock release. However, the indexing for the deleted rows will remains as residual entries.
Relationship to other operands
Some combinations of this operand and the pd_indexlock_mode operand cannot be specified, as shown below:
pd_lock_uncommited_delete_data specificationpd_indexlock_mode specification
NONEKEY
WAITYN#1
NOWAITYY
NOWAIT (with history of having specified WAIT)YN#2
Legend:
Y: Combination can be specified.
N: Combination cannot be specified.
#1
If specified, the KFPS01857-E error message is output when HiRDB starts.
#2
If HiRDB is started with WAIT specified in this operand, subsequently specifying NOWAIT in this operand and KEY in the pd_indexlock_mode operand will result in output of invalid search results when the database is accessed. In this case, execute the database initialization utility (pdinit) to initialize HiRDB, and then specify the operand again.
82) pd_dbreuse_remaining_entries = ALL | NONE
If non-locking of index key values is used and index key values are deleted, unneeded entries remain in the index storage pages. In such a case, if INSERT, UPDATE, or DELETE statements are executed in multiple transactions, a lock might occur even when the statement is attempting to manipulate different index key values.
The purpose of this operand is to suppress row identifiers (the values assigned by the system to uniquely identify individual rows) from being reused when row data is inserted, thereby reducing the frequency of locking.
ALL:
Reuse row identifiers.
If multiple INSERT, UPDATE, or DELETE statements are executed concurrently and the row identifier of a row to be manipulated or inserted is the same as the row identifier of a remaining entry, locking might occur, even if the target of manipulation is a different index or key value.
NONE:
Suppress row identifiers from being reused until the maximum row identifier (255) defined for each page is exceeded.
This can reduce the frequency of locking when different index key values are manipulated by multiple transactions.
Application criterion
If indexes are to be deleted or inserted and a key value insertion processing attempts to reuse a remaining entry that has resulted from index deletion processing, locking might occur. Use this operand if reducing the possibility that locking will occur is more important than reducing the storage efficiency. For details, see Avoiding locks caused by remaining entries (suppressing row identifiers from being reused) in the HiRDB Version 9 UAP Development Guide.
Specification guidelines
Normally, this operand does not need to be specified.
Consider specifying this operand if all the following conditions are satisfied:
  1. Non-locking of index key values is applied (NONE is specified in the pd_indexlock_mode operand or the operand is omitted).
  2. One of the following conditions is satisfied:
    [Figure]There is a table for which indexes with UNIQUE or PRIMARY specified are defined.
    [Figure]Locking of uncommitted deleted data (WAIT specified in the pd_lock_uncommited_delete_data operand) is in effect and the system contains a table with indexes defined.
  3. The INSERT, UPDATE, or DELETE statements are executed in multiple transactions on the indexes that satisfy condition 2.
The reusing of row identifiers is suppressed only if the INSERT statement is executed on a user table that satisfies the above conditions 1 and 2 and an attempt is made to add rows to the page currently in use. For any other table or operation, row identifiers are reused even if NONE is specified.
For details about the effects of this operand on the row identifier allocation method and how a lock-release wait occurs due to remaining entries, see Avoiding locks caused by remaining entries (suppressing row identifiers from being reused) in the HiRDB Version 9 UAP Development Guide.
Notes
  • Entries for indexes remain if unique index keys are updated or deleted while non-locking of index key values is in effect. If uncommitted deleted data are locked and normal index keys are updated and deleted, entries also remain.
  • If NONE is specified in this operand, the storage efficiency might decreases because the management area inside pages increases. Therefore, if you have changed this operand value, re-estimate the sizes of the RDAREA for table and the RDAREA for indexes. For details about the formulas for estimating these sizes, see Calculating the number of table storage pages in the HiRDB Version 9 Installation and Design Guide.
    To prevent a decrease in storage efficiency, release free pages periodically by using the free page release utility (pdreclaim). For details, see Reusing used free pages in the HiRDB Version 9 System Operation Guide.
    For details about cases where storage efficiency decreases, see Avoiding locks caused by remaining entries (suppressing row identifiers from being reused) in the HiRDB Version 9 UAP Development Guide.
  • If NONE is specified in this operand, but all 255 row identifiers have been assigned, the row identifiers of the entries remaining in the page are reused sequentially starting with the smallest row identifier. Therefore, if processing that inserts a new index key value attempts to use a remaining entry's row identifier and that remaining entry is still locked (the transaction that performed deletion or update processing has not yet been decided), locking occurs.
    To prevent a decrease in storage efficiency, release free pages periodically by using the free page release utility (pdreclaim). For details, see Reusing used free pages in the HiRDB Version 9 System Operation Guide.
Relationship to other operands
This operand is related to the following operands:
  • pd_indexlock_mode
  • pd_lock_uncommited_delete_data
83) pd_lck_deadlock_check = Y | N
Specifies whether checking for deadlock is to be performed.
Y: Checks for deadlocks.
N: Does not check for deadlocks.
Specification guidelines
In transaction systems that do not generate deadlocks, specifying N in this operand can improve SQL execution performance. This is especially true when the interval check mode is used as the deadlock detection method, because lock performance can deteriorate whenever a deadlock is detected if the number of pool partitions for locks is increased. Consequently, we recommend that you specify N in this operand in the case of designing a transaction system that does not generate deadlocks.
In a transaction system that does have potential to generate deadlocks, specify Y in this operand. Specifying N would mean that when a deadlock did occur, SQL would not terminate until the time specified in the pd_lck_timeout operand has elapsed. Also, because HiRDB does not output deadlock information, it might become impossible to determine what caused the deadlock.
Notes
When you specify N in this operand, a transaction that generates a deadlock will not result in an error. Instead, the transaction will be canceled on the basis of one of the following.
  • The maximum time for checking lock release wait time elapses and SQL returns an error.
  • The maximum wait time of the HiRDB client elapses and the request returns an error to the UAP.
Relationship to client environment definition
This operand is related to the following client environment definitions.
  • PDCWAITTIME
  • PDLCKWAITTIME
Relationship to other operands
This operand is related to the following operands:
  • pd_lck_deadlock_info
  • pd_lck_pool_partition
  • pd_fes_lck_pool_partition
  • pd_lck_deadlock_check_interval
  • pd_lck_wait_timeout
84) pd_lck_deadlock_check_interval = deadlock-check-interval
~<unsigned integer>((1-2000000000))<<1000>>(msec)
Specifies the interval for performing checking during monitoring for occurrence of deadlocks in the interval check mode.
Condition
Conditions for this operand depend on the server.
For a single server, back-end server, or dictionary server, both the following conditions must be satisfied:
  • Y is specified in the pd_lck_deadlock_check operand
  • 2 or greater is specified in the pd_lck_pool_partition operand
For a front-end server, both the following conditions must be satisfied:
  • Y is specified in the pd_lck_deadlock_check operand
  • 2 or greater is specified in the pd_fes_lck_pool_partition operand
Specification guidelines
Specify a small value in this operand to reduce the time between occurrence and detection of a deadlock.
Notes
  • If too small a value is specified in this operand, system performance might be degraded.
  • If too large a value is specified in this operand, a transaction might be canceled for the following reasons.
    [Figure]The maximum time for checking the lock release wait time elapses and the SQL statement returns an error.
    [Figure]The maximum wait time of the HiRDB client elapses and the request returns an error to the UAP.
Relationship to client environment definition
This operand is related to the following client environment definition.
  • PDCWAITTIME
Relationship to other operands
This operand is related to the following operands:
  • pd_lck_deadlock_check
  • pd_lck_pool_partition
  • pd_fes_lck_pool_partition
  • pd_lck_wait_timeout