Release History
Requirements

Introduction

Quick onboard

Deployment

Data Modeling

Connecting

Write data

Migration

Query

Operations and Maintenance

Performance Tuning

Troubleshooting

Reference Guide

SQL Reference

FAQ

Lock Management

This document describes the parameters in the Lock Management category of system configuration.

YMatrix implements the following two deadlock detection mechanisms:

  • Deadlock Detection Mechanism:

    • (Local) Deadlock: Occurs when two or more transactions hold locks that the others are trying to acquire. For example, if Transaction 1 holds an exclusive lock on Table A and attempts to acquire an exclusive lock on Table B, while Transaction 2 already holds an exclusive lock on Table B and is requesting one on Table A, neither transaction can proceed.
    • Local deadlocks occur within a single MXSegment instance, where different transactions wait for each other. The Postgres process includes a robust mechanism to automatically detect such deadlocks and resolve them by aborting one of the involved transactions, allowing the others to proceed.

  • Global Deadlock Detection Mechanism:

    • Global Deadlock: Occurs when no local deadlock exists within any individual MXSegment instance, but a cycle of waiting exists across multiple segments, preventing global transactions from making progress.
    • The global deadlock detection mechanism is designed for distributed database clusters. It requires the MXMaster to run a Global Deadlock Detector (GDD) process that periodically collects lock-wait relationships from all MXSegments (and the MXMaster). The GDD analyzes these relationships to detect global deadlocks. When a deadlock is detected, it is resolved using predefined policies (e.g., terminating the youngest transaction).

Note!
To ensure system stability and security, manually modifying these parameters should be done with extreme caution.

deadlock_timeout

The amount of time (in milliseconds) to wait on a lock before checking for deadlock.

  • Deadlock detection is relatively expensive. Therefore, the server does not perform deadlock checks on every lock wait.
  • Assuming deadlocks are rare in production workloads, a short waiting period before initiating deadlock detection is sufficient. Increasing this value reduces unnecessary deadlock checks, but also delays reporting of actual deadlock errors.
  • If no unit is specified, the value is interpreted in milliseconds. The default is 1000, which may be the smallest practical value. On heavily loaded servers, you may need to increase it. The optimal setting should exceed the typical transaction duration to reduce the chance of initiating deadlock checks before locks are released.
  • When log_lock_waits is enabled, this parameter also determines the waiting time before logging lock-wait events. To investigate lock delays, you may want to set a value smaller than the default deadlock_timeout.
Data Type Default Value Range Setting Scope
int 1000 1 ~ INT_MAX segment; system; restart; superuser

gp_enable_global_deadlock_detector

Enables or disables the YMatrix global deadlock detector for managing concurrent UPDATE and DELETE operations on HEAP tables to improve performance.

  • Default is off; the global deadlock detector is disabled.
  • If the global deadlock detector is disabled (default), YMatrix serializes concurrent update and delete operations on heap tables.
  • If enabled, concurrent updates are allowed. The global deadlock detector identifies deadlocks and breaks them by terminating one or more backend processes associated with the youngest transaction involved.
  • Tables with a primary key or unique index may also trigger global deadlock detection under INSERT.
  • Administrative commands such as CREATE, ALTER, and DROP may also trigger deadlocks.
Data Type Default Value Setting Scope
boolean off master; system; restart

gp_global_deadlock_detector_period

Specifies the detection interval (in seconds) for the Global Deadlock Detector (GDD) background worker process.

Data Type Default Value Range Setting Scope
int 120 5 ~ INT_MAX master; system; reload

max_locks_per_transaction

The shared lock table tracks locks on up to max_locks_per_transaction * (max_connections + max_prepared_transactions) distinct objects.

  • At any moment, no more than this number of distinct objects can be locked.
  • This parameter controls the average number of object locks allocated per transaction. Individual transactions may lock more objects, up to the total capacity of the lock table across all transactions.
  • This parameter does not limit the number of rows that can be locked, so the value is not unbounded.
  • If your workload involves queries that access many different tables within a single transaction (e.g., querying a parent table with many child partitions), you may need to increase this value.
  • This parameter can only be set at server startup.
  • When running a standby server, this parameter must be set to a value greater than or equal to that on the primary server; otherwise, queries will be disallowed on the standby.
Data Type Default Value Range Setting Scope
int 128 10 ~ INT_MAX segments; system; restart

max_pred_locks_per_page

Controls the number of rows a predicate lock can cover on a single page before it is escalated to cover the entire page.

Data Type Default Value Range Setting Scope
int 2 0 ~ INT_MAX segments; system; reload

max_pred_locks_per_relation

Controls the number of pages or tuples a predicate lock can cover in a single relation before it is escalated to cover the entire relation.

  • A value greater than or equal to 0 represents an absolute limit. A negative value means the limit is calculated as max_pred_locks_per_transaction divided by the absolute value of this setting.
Data Type Default Value Range Setting Scope
int -2 INT_MIN ~ INT_MAX segments; system; reload

max_pred_locks_per_transaction

The shared predicate lock table tracks locks on up to max_pred_locks_per_transaction * (max_connections + max_prepared_transactions) distinct objects (e.g., tables).

  • At any moment, no more than this number of distinct objects can be locked.
  • This parameter controls the average number of object locks allocated per transaction. Individual transactions may lock more objects, up to the total capacity of the lock table across all transactions.
  • This parameter does not limit the number of rows that can be locked, so the value is not unbounded.
  • In most cases, the default value has proven sufficient in testing. However, if your workload involves serializable transactions that access many different tables (e.g., querying a parent table with many child partitions), you may need to increase this value.
  • This parameter can only be set at server startup.
Data Type Default Value Range Setting Scope
int 64 10 ~ INT_MAX segments; system; restart
← Previous
Database table
Next →
Resource management