Lock Management Category Parameters
This document describes the relevant parameters in the lock management category of system configuration parameters.
There are two deadlock detection mechanisms in YMatrix:
- Deadlock detection mechanism:
- (Local) deadlock: Refers to two (or more) transactions mutually holding the locks that the other wants. For example, if transaction 1 obtains an exclusive lock on table A and attempts to obtain an exclusive lock on table B, while transaction 2 already holds an exclusive lock on table B and is requesting an exclusive lock on table A, neither transaction can proceed.
- Local deadlocks occur within a specific Segment node instance, where different transactions directly wait for each other. Postgres processes have a robust mechanism to automatically detect deadlock situations and resolve them by aborting one of the transactions to allow the others to complete.
- Global deadlock detection mechanism:
- Global deadlock: This refers to a situation where no local deadlocks exist within any individual Segment node instance in the YMatrix cluster, but a cyclic wait occurs between different Segments, preventing all global transactions involved in the deadlock from progressing.
- The global deadlock detection mechanism is designed for distributed database clusters. It requires the Master to configure the global deadlock detection process, periodically collect the waiting relationships of database object locks across all Segments (and the Master), and use the GDD (Global Deadlock Detector) to detect whether a deadlock has occurred. If a deadlock is detected, it employs predefined strategies (such as terminating the youngest transaction) to resolve the deadlock.
Notes!
To ensure system stability and security, please exercise caution when manually modifying relevant parameters.
deadlock_timeout
This is the amount of time (in milliseconds) spent waiting on a lock before deadlock detection is performed.
- Deadlock detection is relatively expensive, so the server does not run it every time it waits for a lock.
- Assuming that deadlocks are rare in a production application and that only a short wait is required before deadlock detection begins, increasing this value reduces the time wasted on unnecessary deadlock detection but also slows down the reporting of actual deadlock errors.
- If no unit is specified, the value is in milliseconds. The default is
1000, which may be the minimum value you want in practice. On a high-load server, you may need to increase it. The ideal setting for this value should exceed your typical transaction time, thereby reducing the likelihood of starting deadlock checks before the lock is released.
- When
log_lock_waits is set, this parameter also determines the amount of time to wait before logging lock waits. If you want to investigate lock delays, you may want to set a value smaller than the normal deadlock_timeout.
| Data type |
Default value |
Value range |
Setting category |
| int |
1000 |
1 to INT_MAX |
segment; system; restart; superuser |
gp_enable_global_deadlock_detector
Control whether to enable the YMatrix database global deadlock detector to manage concurrent UPDATE and DELETE operations on HEAP tables to improve performance.
- The default is
off, and global deadlock detection is disabled.
- If the global deadlock detector is disabled (default), the YMatrix database will perform concurrent update and delete operations on heap tables serially.
- If the global deadlock detector is enabled, concurrent updates are allowed, and the global deadlock detector determines when a deadlock exists and interrupts the deadlock by terminating one or more backend processes associated with the latest transaction involved.
- If the table has a primary key or unique index,
INSERT operations can also trigger a global deadlock.
- Management statements such as
CREATE / ALTER / DROP can also trigger deadlocks.
| Data Type |
Default |
Setting Category |
| boolean |
off |
master; system; restart |
gp_global_deadlock_detector_period
Specify the detection cycle (in seconds) for the global deadlock detector (GDD) background process.
| Data type |
Default value |
Value range |
Setting category |
| int |
120 |
5 ~ INT_MAX |
master; system; reload |
max_locks_per_transaction
Shared lock tracking locks on max_locks_per_transaction * (max_connections + max_prepared_transactions) objects.
- At any given time, no more than this parameter's number of distinct objects can be locked.
- This parameter controls the average number of object locks allocated per transaction. Individual transactions can lock more objects, up to the total number of locks that the lock table can accommodate for all transactions.
- This parameter does not represent the number of rows that can be locked, so this value is not unlimited.
- If you have queries that use many different tables within a single transaction (e.g., querying a parent table with many child tables), you may need to increase this value.
- This parameter can only be set at server startup.
- When running a standby server, you must set this parameter to a value greater than or equal to the value on the primary server. Otherwise, queries will not be allowed on the standby server.
| Data type |
Default value |
Value range |
Setting category |
| int |
128 |
10 ~ INT_MAX |
segments; system; restart |
max_pred_locks_per_page
This parameter controls how many rows the predicate lock can lock on a single page before it is promoted to cover the entire page.
| Data type |
Default value |
Value range |
Setting category |
| int |
2 |
0 ~ INT_MAX |
segments; system; reload |
max_pred_locks_per_relation
This parameter controls the number of pages or tuples that a predicate lock can lock on a single relation table before it is promoted to cover the entire relation table.
- A value greater than or equal to
0 indicates an absolute limit, while a negative value indicates the absolute value of this setting divided by max_pred_locks_per_transaction.
| Data type |
Default value |
Value range |
Setting category |
| int |
-2 |
INT_MIN ~ INT_MAX |
segments; system; reload |
max_pred_locks_per_transaction
Shared predicate lock tables track locks on up to max_pred_locks_per_transaction * (max_connections + max_prepared_transactions) objects (such as tables).
- At any given time, no more than this parameter value number of distinct objects can be locked.
- This parameter controls the average number of object locks allocated per transaction. Individual transactions can lock more objects, up to the total number of locks that the lock table can accommodate for all transactions.
- This parameter does not represent the number of rows that can be locked, so this value is not unlimited.
- Typically, the default value has been proven sufficient in testing, but if you need to use many different tables in a serializable transaction (e.g., querying a parent table with many child tables), you may need to increase this value.
- This parameter can only be set at server startup.
| Data type |
Default value |
Value range |
Setting category |
| int |
64 |
10 to INT_MAX |
segments; system; restart |
