Lock Management

deadlock_timeout

Parameter description: Specifies the deadlock timeout interval. When the applied lock exceeds the preset value, the system will check whether a deadlock occurs. This parameter takes effect only for common locks. This parameter can be set at the PDB level.

Parameter type: integer.

Unit: millisecond

Value range: 1 to 2147483647

Default value: 1000 (that is, 1s). In the PDB scenario, if this parameter is not set, the global setting is inherited.

Setting method: This is a SUSET parameter. Set it based on instructions provided in Table 1.

Setting suggestion:

• The check for deadlock is relatively expensive. Therefore, the server does not check it when waiting for a lock every time. Deadlocks seldom occur when the system is running. Therefore, the system just needs to wait on the lock for a while before checking for deadlocks. Increasing the value of deadlock_timeout reduces the time wasted in deadlock check, but slows down reporting of real deadlock errors. On a heavily loaded server, you may need to set deadlock_timeout to a larger value. It is recommended that this value exceed the transaction time to avoid deadlock check before locks are released.
• When log_lock_waits is set to on, deadlock_timeout determines a waiting time to write the lock waiting time information during query execution to logs. To study the lock delay, you can set deadlock_timeout to a value smaller than the normal value.

Risks and impacts of improper settings: If the value is too large, the speed of reporting deadlock errors is slowed down. If the value is too small, deadlock detection starts before the lock is released.

lockwait_timeout

Parameter description: Specifies the timeout for attempts to acquire a lock. If the time spent in waiting for a lock exceeds the specified time, an error is reported. This parameter takes effect only for common locks. This parameter can be set at the PDB level.

Parameter type: integer.

Unit: millisecond

Value range: 0 to 2147483647

Default value: 1200000 (20 minutes). In the PDB scenario, if this parameter is not set, the global setting is inherited.

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1. For example, if the value is 900 without a unit, lockwait_timeout indicates 900 ms. If the value is 20min, lockwait_timeout indicates 20 minutes. If the unit is required, the value must be ms, s, min, h, or d.

Setting suggestion: Retain the default value. Alternatively, set this parameter based on the service requirements.

Risks and impacts of improper settings: A larger value indicates that the blocking duration caused by lock conflicts is longer. A smaller value indicates that more lock timeout errors may be reported.

update_lockwait_timeout

Parameter description: Specifies the maximum duration that a lock waits for concurrent updates on a row to complete when the concurrent update feature is enabled in the service. If the time spent in waiting for a lock exceeds the specified time, an error is reported. This parameter takes effect only for common locks. This parameter can be set at the PDB level.

Parameter type: integer.

Unit: millisecond

Value range: 0 to 2147483647

Default value: 120000 (2 minutes). In the PDB scenario, if this parameter is not set, the global setting is inherited.

Setting method: This is a SUSET parameter. Set it based on instructions provided in Table 1. For example, if the value is 900 without a unit, update_lockwait_timeout indicates 900 ms. If the value is 2min, update_lockwait_timeout indicates 2 minutes. If the unit is required, the value must be ms, s, min, h, or d.

Setting suggestion: Set the value based on the workload of transactions that concurrently update the same row. Generally, the execution time of TP services is less than 2 minutes. Therefore, the time for waiting for concurrent update transaction committing is short. In this case, there is a low probability that the lock times out if the default value 2 minutes is used. If a large number of concurrent transactions update the same row and the execution time exceeds 2 minutes, you can increase the value of this parameter to prevent false positives caused by lock timeout.

Risks and impacts of improper settings: A larger value indicates that the blocking duration caused by lock conflicts is longer when the same row is concurrently updated. A smaller value indicates that more lock timeout errors may be reported.

max_locks_per_transaction

Parameter description: Specifies the average number of object locks that each transaction can hold when the number of database connections reaches the maximum.

This parameter does not strictly limit the maximum number of object locks that can be held by each transaction. If the number of connections does not reach the maximum value (max_connections), the average number of object locks that can be held by each transaction increases.

Parameter type: integer.

Unit: none

Value range: 10 to 2147483647

Default value: 256 (196-core CPU/1536 GB memory, 128-core CPU/1024 GB memory, 104-core CPU/1024 GB memory, 96-core CPU/1024 GB memory, 96-core CPU/768 GB memory, 80-core CPU/640 GB memory, 64-core CPU/512 GB memory, 60-core CPU/480 GB memory, 32-core CPU/256 GB memory, 16-core CPU/128 GB memory, 8-core CPU/64 GB memory, 4-core CPU/32 GB memory); 64 (4-core CPU/16 GB memory).

Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

Setting suggestion:

• The maximum number of hash tables that can be locked by a shared lock at any time is calculated based on an assumption: max_locks_per_transaction x (max_connections + max_prepared_transactions) and max_locks_per_transaction ≥ Number of concurrent service transactions x Number of object locks added by each service transaction/(max_connections + max_prepared_transactions). In this case, an upper limit is determined for this parameter.
• Within the specified range, objects can be locked simultaneously at any time. You may need to increase the value of this parameter if many different tables are modified in a single transaction.
• Increasing the value of this parameter may cause GaussDB to request more System V-shared memory than the OS's default configuration allows, leading to database startup failure.
• When running a standby node, you must set this parameter to a value that is no less than that on the primary node. Otherwise, queries will not be allowed on the standby node.
• If this parameter is set to a smaller value and the standby node is faulty before the restart, the standby node fails to be restored. You are advised to set the parameters to the original values and restart the system to restore the environment.
• In specification degradation and DR scenarios, you are advised not to perform DDL operations frequently when modifying this parameter. Otherwise, a memory error will be reported.

Risks and impacts of improper settings: If the value is too large, the database may fail to be started.

max_pred_locks_per_transaction

Parameter description: Specifies the average number of predicate locks that each transaction can hold when the number of database connections reaches the maximum.

This parameter does not strictly limit the maximum number of predicate locks that can be held by each transaction. If the number of connections does not reach the maximum value (max_connections), the average number of predicate locks that can be held by each transaction increases.

Parameter type: integer.

Unit: none

Value range: 10 to 2147483647

Default value: 64

Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

Setting suggestion:

• The size of the shared predicate lock table is calculated under the condition that a maximum of N independent objects need to be locked at any time. N = max_pred_locks_per_transaction x (max_connections + max_prepared_transactions). Objects whose amount does not exceed the preset number can be locked simultaneously at any time. You may need to increase this value if many different tables are modified in a single transaction. This parameter can only be set at server start.
• Increasing the value of this parameter may cause GaussDB to request more System V-shared memory than the OS's default configuration allows.

Risks and impacts of improper settings: If the value is too large, the database may fail to be started.

gs_clean_timeout

Parameter description: Specifies the interval for the primary node to call the gs_clean tool to periodically clear temporary tables. When the database connection is interrupted abnormally, temporary tables may exist. In this case, you need to clear the temporary tables in the database.

Parameter type: integer.

Unit: second

Value range: 0 to 2147483

Default value: 60

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1. For example, if the value is 1 without a unit, gs_clean_timeout indicates 1s. If the value is 1min, gs_clean_timeout indicates 1 minute. The unit must be s, min, h, or d if required.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: If the value is too large, the period for clearing residual temporary tables in the database is prolonged.

partition_lock_upgrade_timeout

Parameter description: Specifies the timeout for attempts to upgrade an exclusive lock (read allowed) to an access exclusive lock (read/write blocked) on a partitioned table during the execution of some query statements. If there are concurrent read transactions running, the lock upgrade will need to wait. partition_lock_upgrade_timeout sets the waiting timeout for lock upgrade attempts. This parameter can be set at the PDB level.

Example: When you perform MERGE PARTITION and CLUSTER PARTITION on a partitioned table, temporary tables are used for data rearrangement and file exchange. To concurrently perform as many operations as possible on the partitions, exclusive locks are acquired for the partitions during data rearrangement and access exclusive locks are acquired during file exchange. The locking mode is attempt locking. If the locking is successful, a response is returned immediately. If the locking fails, the system waits for 50 ms and attempts to lock the next time. The locking timeout interval is specified by the session-level parameter partition_lock_upgrade_timeout.

Parameter type: integer.

Unit: second

Value range: –1 to 3000. –1 indicates infinite waiting. That is, the system continuously attempts to upgrade the lock until the lock is successfully locked.

Default value: 1800. In the PDB scenario, if this parameter is not set, the global setting is inherited.

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: If this parameter is set to –1, services may be blocked when the lock cannot be upgraded for a long time.

fault_mon_timeout

Parameter description: Specifies the period for detecting lightweight deadlocks.

Parameter type: integer.

Unit: minute

Value range: 0 to 1440

Default value: 5

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: If this parameter is set to a large value, deadlocks cannot be detected in a timely manner, which slows down the speed of reporting deadlock errors.

enable_online_ddl_waitlock

Parameter description: Specifies whether to block DDL operations to wait for the release of database locks, such as pg_advisory_lock.

Parameter type: Boolean.

Unit: none

Value range:

• on: enabled.
• off: disabled.

Default value: off

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: This parameter is mainly used in online OM operations and you are advised not to modify the settings.

Risks and impacts of improper settings: Changing the value of this parameter may cause unexpected results in the database. Retain the default value.

xloginsert_locks

Parameter description: Specifies the number of locks on concurrent write-ahead logging. This parameter is used to improve the efficiency of writing write-ahead logs.

Parameter type: integer.

Unit: none

Value range: 1 to 1000. If the CPU uses the NUMA architecture, the value must be an integer multiple of the number of NUMA nodes.

Default value: 16

Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

Setting suggestion: When the concurrency pressure of the Arm CPU architecture is high, performance jitter may occur due to atomic operation competition. You can decrease the value of this parameter for stable running performance.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

num_internal_lock_partitions

Parameter description: Specifies the number of internal lightweight lock partitions. It is mainly used for performance optimization in various scenarios.

• The parameter content is organized in the KV format of keywords and numbers. Different types of locks are separated by commas (,).
• The sequence does not affect the setting result. For example, "CLOG_PART=256,CSNLOG_PART=512" is equivalent to "CSNLOG_PART=512,CLOG_PART=256".
• If you set the same keyword repeatedly, the last setting takes effect. For example, if the setting is "CLOG_PART=256,CLOG_PART=2", the value of CLOG_PART is 2.

Parameter type: string.

Unit: none

Value range:

• CLOG_PART: number of Clog file controllers. The value ranges from 1 to 256.
• CSNLOG_PART: number of CSNlog file controllers. The value ranges from 1 to 512.
• LOG2_LOCKTABLE_PART: two pairs of ordinary table lock partitions. The minimum value is 4, that is, the number of lock partitions is 16. The maximum value is 16, that is, the number of lock partitions is 65536.
• FASTPATH_PART: maximum number of locks that each thread can obtain without using the main lock table. The value ranges from 20 to 10000.

Default value:

"CLOG_PART=256,CSNLOG_PART=512,LOG2_LOCKTABLE_PART=4,FASTPATH_PART=20" (196-core CPU/1536 GB memory, 128-core CPU/1024 GB memory, 104-core CPU/1024 GB memory, 96-core CPU/1024 GB memory, 96-core CPU/768 GB memory, 80-core CPU/640 GB memory, 64-core CPU/512 GB memory, 60-core CPU/480 GB memory, 32-core CPU/256 GB memory, 16-core CPU/128 GB memory, 8-core CPU/64 GB memory, or 4-core CPU/32 GB memory); "CLOG_PART=8,CSNLOG_PART=16,LOG2_LOCKTABLE_PART=4,FASTPATH_PART=20" (4-core CPU/16 GB memory)

Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

Setting suggestion:

• CLOG_PART: Increasing the value of this parameter improves the Clog writing efficiency and transaction commit performance, but increases the memory usage. Decreasing the value of this parameter reduces the memory usage, but may increase the conflict of writing Clogs and affect the performance.
• CSNLOG_PART: Increasing the value of this parameter improves the CSNlog writing efficiency and transaction commit performance, but increases the memory usage. Decreasing the value of this parameter reduces the memory usage, but may increase the conflict of writing CSNlogs and affect the performance.
• LOG2_LOCKTABLE_PART: Increasing the value can improve the concurrency of obtaining locks in the normal process, but may increase the memory usage. When wait events occur in LockMgrLock, you can increase the value to improve the performance.
• FASTPATH_PART: When a partitioned table is read, updated, inserted, or deleted and the wait event is LockMgrLock, you can increase the value of this parameter to prevent LockMgrLock from being obtained and improve performance. It is recommended that the value be greater than or equal to that calculated using the following formula: Number of partitions x (1 + Number of local indexes) + Number of global indexes + 10. Increasing the value will increase the time for lock transfer and lock elimination, and increase the memory usage. The incremental FASTPATH memory (unit: bytes) is calculated as follows: (Incremental FASTPATH/20 x 8 + Incremental FASTPATH x 12) x Thread pool size.

Risks and impacts of improper settings: The database performance may be affected.

enable_wait_exclusive_lock

Parameter description: Specifies whether to enable the hang detection and cure function for the exclusive lock of ProcArrayLock.

Parameter type: Boolean.

Unit: none

Value range:

• on: enabled.
• off: disabled.

Default value: on

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

barrier_lock_timeout

Parameter description: Specifies the timeout interval for holding a barrier lock. This parameter can be set at the PDB level.

Parameter type: integer.

Unit: second

Value range: 0 to 3600

Default value: 30. In the PDB scenario, if this parameter is not set, the global setting is inherited.

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

enable_xid_abort_check

Parameter description: Specifies whether to verify transaction ID rollback is enabled when a transaction is committed.

Parameter type: Boolean.

Unit: none

Value range:

• on: enabled.
• off: disabled.

Default value: on

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.