4.2.4 Operands related to lock

13) pd_lck_pool_size = server-lock-pool-size
~<unsigned integer> (kilobytes)
  • 32-bit mode: ((1-2000000))<<16000>>
  • 64-bit mode: ((1-2000000000))<<32000>>
For a HiRDB/Single Server, specifies in kilobytes the size of the shared memory area to be used by the single server for locking (lock pool).
For a HiRDB/Parallel Server, specifies in kilobytes the size of the shared memory area to be used for locking by back-end servers and dictionary servers. Use the pd_fes_lck_pool_size operand to specify the size of the shared memory area to be used for locking by front-end servers.
The area specified by this operand is used for tables for managing locked resources. Note the following relationship between the number of tables for managing locked resources, the number of lock requests, and the lock pool size:
Number of tables for managing locked resources = number of lock requests = size of lock pool[Figure] coefficient
Specification guidelines
  • In the 32-bit mode, the amount of lock pool space required for six lock requests is 1 kilobyte.
  • In the 64-bit mode, the amount of lock pool space required for four lock requests is 1 kilobyte.
  • The following formulas can be used to determine the value to be specified for this operand:
    HiRDB typeFormula
    HiRDB/Single Server (32-bit mode)[Figure]a[Figure] value of pd_lck_pool_partition[Figure][Figure] 6[Figure][Figure] value of pd_lck_pool_partition
    HiRDB/Parallel Server (32-bit mode)[Figure]b[Figure] value of pd_lck_pool_partition[Figure][Figure] 6[Figure][Figure] value of pd_lck_pool_partition
    HiRDB/Single Server (64-bit mode)[Figure]a[Figure] value of pd_lck_pool_partition[Figure][Figure] 4[Figure][Figure] value of pd_lck_pool_partition
    HiRDB/Parallel Server (64-bit mode)[Figure]b[Figure] value of pd_lck_pool_partition[Figure][Figure] 4[Figure][Figure] value of pd_lck_pool_partition
    a: Total number of transaction lock requests to be executed concurrently by the single server. The number of lock requests depends on the SQL. For details about how to determine the total number of lock requests, see D. Determining the Number of Locked Resources.
    b: Total number of transaction lock requests to be executed concurrently by each server (dictionary server or back-end server). The number of lock requests depends on the SQL. For details about how to determine the total number of lock requests, see D. Determining the Number of Locked Resources.
    Note
    When you execute DROP TABLE or DROP SCHEMA in a definition SQL, it is especially important to have already determined in advance an appropriate value for this operand.
Tuning the specified value
See the usage rate for the locked resources management table (% OF USE LOCK TABLE) displayed in the statistical information on system operation by the statistics analysis utility.# If the maximum usage rate equals or exceeds 80%, it is recommended that the operand's value be increased in preparation for future database expansion. If the maximum usage rate does not exceed 10%, it is recommended that the operand's value be decreased to conserve shared memory space.
#: For a HiRDB/Parallel Server, see the usage rate for the locked resources management table (% OF USE LOCK TABLE) displayed in the statistical information on system operation for each server for the dictionary servers and back-end servers.
Note
  • If the value specified for this operand is too small, an SQL statement might return an error.
  • Do not specify a larger value than necessary in this operand. A large value will increase the size of the shared memory used by HiRDB, which might cause a shortage of shared memory and prevent HiRDB from starting.
  • If you do an all-item search of a table that contains many rows while locking is in units of rows, this operand's value will need to be increased to reflect the large number of items, which will increase the amount of memory required. Instead, consider making the following adjustments in the UAP:
    [Figure]Acquire locks in table units
    [Figure]If you can use the unlocked search facility, perform searches unlocked
    [Figure]Narrow the search conditions, and divide the processing into several transactions
Relationship to other operands
  • When v6compatible is specified in the pd_sysdef_default_option operand, the default for this operand is 1024.
  • This operand is related to the pd_lck_pool_partition operand.
14) pd_fes_lck_pool_size = front-end-server-lock-pool-size
~<unsigned integer> (kilobytes)
  • 32-bit mode: ((1-2000000))
  • 64-bit mode: ((1-2000000000))
This operand is applicable only to HiRDB/Parallel Server.
Specifies in kilobytes the size of the shared memory area to be used by front-end servers for locking (lock pool). Use the pd_lck_pool_size operand to specify the size of the shared memory area to be used for locking by single servers, dictionary servers, and back-end servers.
The area specified by this operand is used for tables for managing locked resources. Note the following relationship between the number of tables for managing locked resources, the number of lock requests, and the lock pool size:
Number of tables for managing locked resources = number of lock requests = size of lock pool[Figure] coefficient
Specification guidelines
  • In the 32-bit mode, the amount of lock pool space required for six lock requests is 1 kilobyte.
  • In the 64-bit mode, the amount of lock pool space required for four lock requests is 1 kilobyte.
  • Use the following formula to determine the value of this operand:
    [Figure](a + b) [Figure]value of pd_fes_lck_pool_partition[Figure][Figure] c[Figure][Figure] value of pd_fes_lck_pool_partition (kilobytes)
    a: Total number of transaction lock requests to be executed concurrently by the front-end server. The number of lock requests depends on the SQL. For details about how to determine the total number of lock requests, see D. Determining the Number of Locked Resources.
    b: (value of pd_max_users + 3) [Figure](value of pd_max_access_tables + 4)
    c: Use 6 and 4 for 32- and 64-bit modes, respectively.
Tuning the specified value
See the usage rate for the locked resources management table (%OF USE LOCK TABLE) displayed for the front-end server in the statistical information on system operation by the statistics analysis utility. If the maximum usage rate equals or exceeds 80%, it is recommended that the operand's value be increased in preparation for future database expansion. If the maximum usage rate does not exceed 10%, it is recommended that the operand's value be decreased to conserve shared memory space.
Operand default value
The default values of this operand are as follows:
[Figure]32-bit mode
{(pd_max_users value + 3) [Figure] (pd_max_access_tables value + 4)} [Figure] 6
[Figure]64-bit mode
{(pd_max_users value + 3) [Figure] (pd_max_access_tables value + 4)} [Figure] 4
Note
  • If the value specified for this operand is too small, an SQL statement might return an error.
  • Do not specify a larger value than necessary in this operand. A large value will increase the size of the shared memory used by HiRDB, which might cause a shortage of shared memory and prevent HiRDB from starting.
Relationship to other operands
This operand is related to the pd_fes_lck_pool_partition operand.
15) pd_lck_pool_partition = per-server-lock-pool-partition-count
~<unsigned integer>((1-5000))<<1>>
Specify this operand to distribute lock processing.
For a HiRDB/Single Server, specifies the number of lock pool partitions to be used in locking by the single server when distributing lock processing.
For a HiRDB/Parallel Server, specifies the number of lock pool partitions to be used in locking by back-end servers and dictionary servers when distributing lock processing. Use the pd_fes_lck_pool_partition operand to specify the number of lock pool partitions to be used in locking by front-end servers.
For details about distributing lock processing, see the HiRDB Version 9 System Operation Guide.
Tuning the specified value
For details about how to tune the number of lock pool partitions, see the HiRDB Version 9 System Operation Guide.
Notes
  • When the value set in this operand is too large, the size of the shared memory assigned to each lock pool partition becomes smaller, which might result in not enough lock pool partition capacity, causing SQL to return an error. Lock processing also takes time, so system performance declines. If this becomes an issue, specify a smaller value in this operand.
  • The lock pool size must be at least 1 kilobyte. If a value larger than the value of pd_lck_pool_size is specified, the KFPS00421-W message will be issued and the value of pd_lck_pool_size will be assumed for this operand.
Relationship to other operands
This operand is related to the following operands:
  • pd_lck_pool_size
  • pd_lck_deadlock_check_interval
Effects on individual estimation formulas
If the value of the pd_lck_pool_partition operand is changed, the following estimation formulas are affected:
HiRDB Version 9 Installation and Design Guide:
  • Processes started by HiRDB/Single Server
  • Formulas for shared memory used by a unit controller under Estimating the memory size required for a HiRDB/Single Server
  • Processes started by HiRDB/Parallel Server
  • Formulas for shared memory used by a unit controller under Estimating the memory size required for a HiRDB/Parallel Server
16) pd_fes_lck_pool_partition = front-end-server-lock-pool-partition-count
~<unsigned integer>((1-5000))<<1>>
Specifies the number of lock pool partitions to be used in locking by front-end servers when distributing lock processing. Use the pd_lck_pool_partition operand to specify the number of lock pool partitions to be used in locking by single servers, dictionary servers, and back-end servers.
For details about distributing lock processing, see the HiRDB Version 9 System Operation Guide.
Tuning the specified value
For details about how to tune the number of lock pool partitions, see the HiRDB Version 9 System Operation Guide.
Notes
  • When the value set in this operand is too large, the size of the shared memory assigned to each lock pool partition becomes smaller, which might result in not enough lock pool partition capacity, causing SQL to return an error. Lock processing also takes time, so system performance declines. If this becomes an issue, specify a smaller value in this operand.
  • The lock pool size must be at least 1 kilobyte. If a value larger than the value of pd_lck_pool_size is specified, the KFPS00421-W message will be issued and the value of pd_lck_pool_size will be assumed for this operand.
Relationship to other operands
This operand is related to the following operands:
  • pd_fes_lck_pool_size
  • pd_lck_deadlock_check_interval
Effects on individual estimation formulas
If the value of the pd_fes_lck_pool_partition operand is changed, the following estimation formula is affected:
HiRDB Version 9 Installation and Design Guide:
  • Formulas for shared memory used by a unit controller under Estimating the memory size required for a HiRDB/Parallel Server
17) pd_lck_until_disconnect_cnt = total-number-of-tables-and-RDAREAs-to-be-locked-until-disconnect-specification
~<unsigned integer>((0-140000))<<256>>
Specifies the number of resources to be locked for the tables and RDAREAs that are to be held across transactions. Based on the value specified for this operand, blocks for which lock with UNTIL DISCONNECT is specified for the tables and RDAREAs are allocated in the shared memory.
Specification guidelines
Normally, this operand need not be specified. Specification of a value other than the default value might be necessary in the following cases:
  • When the number of utilities to be executed concurrently increases
  • When a holdable cursor is used
  • When the local buffer specified in the pdlbuffer operand is used
  • When a shared RDAREA is used
  • When an SQL session-specific temporary table is used
For details about how to estimate the specification value for this operand, see C.5 Formula for determining total number of tables and RDAREAs per server locked with UNTIL DISCONNECT specified (pd_lck_until_disconnect_cnt).
Tuning the specified value
If the value specified for this operand is small, a transaction might roll back or a utility might terminate abnormally with return code 8. In such cases, the message KFPA11914-E or KFPH28001-E is output. If this occurs, increase the value of this operand.
When the value of this operand is increased, the amount of required memory space increases proportionately. The required memory size can be expressed as follows: value of this operand[Figure] 48 (64 in the 64-bit mode) bytes.
Effects on individual estimation formulas
If the value of the pd_lck_until_disconnect_cnt operand is changed, the following estimation formulas are affected:
HiRDB Version 9 Installation and Design Guide:
  • Formulas for shared memory used by a unit controller under Estimating the memory size required for a HiRDB/Single Server
  • Formulas for shared memory used by a unit controller under Estimating the memory size required for a HiRDB/Parallel Server
  • Determining the number of records in a synchronization point dump file
  • Formula 2 under Formulas for shared memory used by a single server
  • Formula 2 under Formulas for the size of the shared memory used by a dictionary server
  • Formula 2 under Formulas for the size of the shared memory used by a back-end server
18) pd_max_open_holdable_cursors = maximum-number-of-holdable-cursors-that-can-be-concurrently-open-when-LOCK-statement-with-UNTIL-DISCONNECT-specification-is-not-executed
~<unsigned integer>((16-1024))<<16>>
When you use holdable cursors for a table for which a LOCK statement with the UNTIL DISCONNECT specification is not executed, this operand specifies the maximum number of holdable cursors that can be concurrently open for each transaction.
Note
Specifying a value other than the default value for this operand increases the amount of shared memory used.
Relationship to other operands
The values specified for this operand and the following operands are used for computing the shared memory size for lock servers. For a 32-bit mode HiRDB system, if the values specified for these operands are too large, the shared memory size of the lock servers exceeds 2 GB, and as a result, HiRDB might not start. Therefore, adjust the values specified for these operands so that the shared memory size of the lock servers does not exceed 2 GB.
  • pd_max_access_tables
  • pd_max_users
  • pd_max_bes_process
  • pd_max_dic_process
  • pd_lck_hash_entry
  • pd_lck_pool_size
For details about shared memory, see the HiRDB Version 9 Installation and Design Guide.
19) pd_lck_hash_entry = lock-pool-hash-entry-count
~<unsigned integer>((0-2147483647))<<0>>
Specifies the number of hash table entries to be used in the lock pool. According to the value specified here, HiRDB allocates a lock pool in the shared memory for the unit controller in each server (single server, front-end server, back-end server, and dictionary server).
Specification guidelines
Normally, omit this operand.
Consider specifying a value for this operand in the following cases:
  • If you do not wish to change the shared memory size if possible when upgrading to version 06-02 or later, specify 11261. In this case, the same number of hash entries is allocated as in the earlier version, the hash table size inside the lock pool remains the same as before.
  • It is possible to improve performance by specifying in this operand a value greater than the recommended value shown below. However, specifying a value greater than variable a (also shown below) will not improve performance over the case in which a is specified.
    The recommended value is as follows:
    Recommended value = Largest prime number not exceeding MAX([Figure]a[Figure] 10[Figure], 11,261)
    VariableFormula for computing the variable
    aSingle server(pd_max_users value + 3) [Figure] (pd_max_access_tables value + 14) + pd_lck_pool_size[Figure] c
    Front-end serverIf pd_fes_lck_pool_size is omitted(b + 3) [Figure] (pd_max_access_tables value + 4)
    If pd_fes_lck_pool_size is specifiedpd_fes_lck_pool_size value [Figure] c
    Back-end server or dictionary server(b + 3) [Figure]10 + pd_lck_pool_size value [Figure] c
    bFront-end serverMultiple front-end serverpd_max_users value + 1
    Not multiple front-end serverpd_max_users value
    Back-end serverIf pd_max_users value > pd_max_bes_process valuepd_max_users value
    If pd_max_users value [Figure] pd_max_bes_process valuepd_max_bes_process value
    Dictionary serverIf pd_max_users value > pd_max_dic_process valuepd_max_users value
    If pd_max_users value [Figure] pd_max_dic_process valuepd_max_dic_process value
    c6 for the 32-bit mode; 4 for the 64-bit mode
Operand rules
  • If this operand and the pd_lck_hash_entry operands of the server definitions are all omitted or 0 is specified in these operands, HiRDB calculates a recommended value for each server. (However, if v6compatible has been specified in the pd_sysdef_default_option operand, the default is 11261.)
  • When a value that is neither 0 nor a prime number is specified in this operand, HiRDB assumes that the specification is the largest prime number that does not exceed the specified value.
Notes
If the value specified in this operand is too small, there might be an insufficient number of hash entries, and performance might deteriorate. If this operand is omitted, there will never be a shortage of hash entries and performance will not deteriorate due to an insufficient number of hash entries.
20) pd_dbsync_lck_release_count = global-buffer-lock-release-interval-during-synchronization-point-processing
~<unsigned integer>((0, 100-1073741824))<<10000>>
Specifies the interval for unlocking global buffers, when global buffer locking occurs during synchronization point processing.
During synchronization point processing, search processing occurs on the buffers (update buffers) and must be applied to the disk. Normally, global buffers are unlocked at a specific interval during search processing on the update buffers.
For example, if 100 is specified in this operand, a global buffer is unlocked once when search processing on 100 sectors (global buffer sectors) is completed. After that, the global buffer is locked again and search processing is resumed. In this example, unlocking occurs once every 100 sectors.
Advantage
By specifying this operand, you can adjust the global buffer lock time during synchronization point processing. When a small value is specified in this operand, the global buffer lock time becomes short and transaction performance might improve during synchronization point processing.
To obtain the global buffer pool lock time, execute the statistics analysis utility and in the global buffer pool statistical information check the item called Buffer pool lock time during synchronization point processing (SYNCL).
Specification guidelines
Normally, there is no need to specify this operand. Consider specifying this operand when both the following conditions apply:
  • Transaction performance drops during synchronization point processing.
  • A large number of buffer sectors is specified in the -n option of the pdbuffer operand.
Operand rules
  • If the specified value is in the range 1 to 99, 100 is set automatically.
  • If 0 is specified, global buffers are locked until update buffer search processing is completed.
Notes
If a small value is specified in this operand, the update buffer search time increases due to interrupts from other transactions and CPU usage rises. The global buffers updated during that time are also output during synchronization point processing. Therefore, the number of update buffers to be output during synchronization point processing increases. To obtain the number of update buffers to be output during synchronization point processing, execute the statistics analysis utility and in the global buffer pool statistical information check the item called Number of synchronization point output pages (SYNCW). As you increase the value of this operand, the lock time for a global buffer to determine the buffer to be output at a synchronization point will increase. For this reason, lock contention grows substantially during synchronization point processing, which might affect transaction performance.