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.

**Table 1** PGXC_LOCKWAIT_DETAIL columns
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 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:
1

begin;select * from t1;
Connect to the CN in another window and truncate table t1.
1

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)