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.
Name | 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 target is |
relation | oid | OID of the locked object relationship |
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 in its system catalog |
obj_subid | smallint | Column number of a table |
virtual_transaction | text | Virtual ID of the transaction holding or awaiting this 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 |
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)