2.3.14 Operands related to lock

73) pd_lck_deadlock_info = Y | N
Specifies whether or not deadlock information and timeout information are to be output. These types of information are output to a directory named %PDDIR%\spool\pdlckinf. For details about the deadlock and timeout information, see the HiRDB Version 8 System Operation Guide.
Y: Output deadlock information and timeout information.
N: Do not output deadlock information or timeout information.
74) pd_lck_wait_timeout = lock-release-wait-time
[Figure]<unsigned integer>((0-65535))<<Max(180, pd_watch_time value)>>(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 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.
Note
If this operand is omitted, the value of the pd_watch_time operand may be used as the default value in some cases. Although the pd_watch_time operand is invalid, even if it is specified for a HiRDB/Single Server, the specified value may be used as the default value for the pd_lck_wait_timeout operand in some cases. Therefore, Hitachi recommends that you omit the pd_watch_time operand for a HiRDB/Single Server.
75) pd_lck_release_detect = interval | pipe
Specifies the method to be used by HiRDB to detect lock release (method of detecting whether or not the process that locked a resource has released that lock).
interval:
Determine the lock release status by checking the lock management area at a regular interval.
pipe:
Use a pipe file (process-to-process communication pipe) to receive the lock release notice.
For a HiRDB/Single Server, the default value is pipe. For a HiRDB/Parallel Server, the default value is interval.
Specification guidelines
Following are guidelines for specifying this operand:
Specified valueHiRDB processingAdvantages and application criteria
intervalDetermines whether the lock has been released by checking the lock management area in the shared memory.
HiRDB checks the lock management area at a regular interval; this interval is specified by the pd_lck_release_detect_interval operand.
Even if the process that has locked a resource releases the lock, the release will not be detected until the next time the lock management area is checked. Consequently, a UAP that has a fast processing time per transaction may end up waiting for a long time. However, this wait does not place any load on the CPU or open any file.
If a small value is specified for the pd_lck_release_detect_interval operand, the CPU usage may increase too much, adversely affecting the throughput. Specifying interval has the effect of reducing the CPU load when a slow CPU is being used.
pipeUses a semaphore to determine whether or not the lock has been released. The process that has locked a resource sends a lock release notice to the process that is waiting for release. When the process that has locked the resource releases it, the process that is waiting for the release can detect the release
  • Throughput improves if the processing time per transaction does not exceed the value specified by the pd_lck_release_detect_interval operand.
  • CPU workload increases if lock-release waits occur frequently.
  • If a high-speed CPU is used, this method improves throughput.
76) pd_lck_release_detect_interval = lock-release-detection-interval
[Figure]<unsigned integer>((1-1000))<<10>>(Milliseconds)
Specifies the interval at which the lock management area is to be checked.
  • When 49 or less is specified
    The interval will begin at the value specified by this operand and thereafter will be 50 milliseconds.
  • When 50 or more is specified
    The interval will begin at 50 milliseconds and thereafter will be the value specified by this operand.
Condition
The interval option must be specified for the pd_lck_release_detect operand.
Specification guidelines
  • As a rule, this operand can be omitted. This operand should be specified (instead of using the default value) only when there is a performance problem.
  • If too small a value is specified, the CPU workload will increase if lock release waits occur frequently.
  • If too large a value is specified, the wait time may increase.
  • The value to be specified can be determined by referring to WAIT TIME in the statistical information related to system operation from the statistics analysis utility. If the wait time that is output in the statistical information is smaller than the value of this operand, the value of this operand should be reduced.
77) 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 may 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.
78) pd_lck_queue_limit = trigger-for-issuing-warning-about-number-of-users-waiting-for-lock-release
[Figure]<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 event log.
When 0 is specified, no warning message is issued.
79) pd_deadlock_priority_use = Y | N
Specifies whether or not 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 8 UAP Development Guide.
N:
Terminate with an error the SQL of the program that executed its transaction most recently.
80) 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)
For details about how to change the deadlock priority value of a command, see the HiRDB Version 8 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.
81) 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 8 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, exclusive competition may be caused by 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 older than 4.0, 04-05. A user with an upgraded system should specify TYPE2 if the older method does not cause exclusive competition by synonyms in the locked resource for the index key value.
  • TYPE1 should be specified if the indexed column contains character string data.
  • TYPE2 should be specified if the indexed column contains numeric data.
82) 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 8 UAP Development Guide.