PGXC_LOCKWAIT_DETAIL¶
PGXC_LOCKWAIT_DETAIL displays detailed information about the lock wait hierarchy on each node in a cluster. If a node has multiple lock wait levels, the entire lock waiting hierarchy is displayed in sequence.
This view is supported only by clusters of version 8.1.3.200 or later.
Column | Type | Description |
|---|---|---|
level | Integer | Level in the lock wait hierarchy. The value starts with 1 and increases by 1 when there is a wait relationship. |
node_name | Name | Node name, corresponding to the node_name column in the pgxc_node table. |
lock_wait_hierarchy | Text | Lock wait hierarchy, in the format of node-name:process-ID->waiting-process-ID->waiting-process-ID->... |
lock_type | Text | Type of the locked object |
database | OID | OID of the database where the locked object is. |
relation | OID | OID of the relationship of the locked object. |
page | Integer | Page index in a relationship |
tuple | Smallint | Row number of a page. |
virtual_xid | Text | Virtual ID of a transaction. |
transaction_id | Xid | Transaction ID. |
class_id | OID | OID of the system catalog that contains the object. |
obj_id | OID | OID of the object within its system catalog. |
obj_subid | Smallint | Column number of a table |
virtual_transaction | Text | Virtual ID of the transaction holding or waiting for the lock. |
pid | Bigint | ID of the thread holding or awaiting this lock |
mode | Text | Lock level |
granted | Boolean | Indicates whether a lock is held. |
fastpath | Boolean | Indicates whether to obtain a lock using FASTPATH. |
wait_for_pid | Bigint | ID of the thread where a lock conflict occurs. |
conflict_mode | Text | Level of the conflicted lock held by the thread where it is |
query_id | Bigint | ID of a query statement. |
query | Text | Query statement |
application_name | Text | Name of the application connected to the backend |
backend_start | Timestamp with time zone | Startup time of the backend process, that is, the time when the client connects to the server |
xact_start | Timestamp with time zone | Start time of the current transaction |
query_start | Timestamp with time zone | Start time of the active query |
state | Text | Overall state of the backend |
waittime | Timestamp with time zone | Timestamp when the lock wait starts. This column is available only in clusters of version 9.1.0.200 or later. |
holdtime | Timestamp with time zone | Timestamp when the lock starts to be obtained. This column is available only in clusters of version 9.1.0.200 or later. |
Example¶
Connect to the DN, start a transaction, and run the following command:
begin;select * from t1;
Connect to the CN in another window and truncate table t1.
truncate t1;
In this case, truncation is blocked.
Open another window to connect to the CN and run the select * from pgxc_lockwait_detail; command.
SELECT * FROM PGXC_LOCKWAIT_DETAIL; level | node_name | lock_wait_hierarchy | lock_type | database | relation | page | tuple | virtual_xid | transaction_id | class_id | obj_id | obj_subid | virtual_transaction | p id | mode | granted | fastpath | wait_for_pid | conflict_mode | query_id | query | application_name | backend_start | xact_start | query_start | state -------+-----------+----------------------------------------------+-----------+----------+------------+------+-------+-------------+----------------+----------+--------+-----------+---------------------+-------- ---------+---------------------+---------+----------+-----------------+-----------------+-------------------+------------------------------------------------+------------------+-------------------------------+-- -----------------------------+-------------------------------+--------------------- 1 | datanode1 | datanode1:140378619314976 | relation | 16049 | 2147484411 | | | | 673638 | | | | 19/297 | 1403786 19314976 | AccessExclusiveLock | f | f | 140378619263840 | AccessShareLock | 73183493945504391 | TRUNCATE t1 | coordinator1 | 2023-03-13 12:13:52.530602+08 | 2 023-03-13 14:52:16.1456+08 | 2023-03-13 14:52:16.148693+08 | active 2 | datanode1 | datanode1:140378619314976 -> 140378619263840 | relation | 16049 | 2147484411 | | | | | | | | 23/16067 | 1403786 19263840 | AccessShareLock | t | f | | | 0 | begin;select * from t1; | gsql | 2023-03-13 14:19:26.325602+08 | 2 023-03-13 14:52:12.042741+08 | 2023-03-13 14:52:12.042741+08 | idle in transaction (2 rows)