PG_LOCKWAIT_DETAIL

The PG_LOCKWAIT_DETAIL view displays details of the lock wait hierarchy in the current instance. If there are multiple lock wait levels, the entire lock wait hierarchy is displayed in sequence.

This view is supported by clusters of version 8.2.1.251 or later, and clusters of version 9.1.1.100 or later.

**Table 1** PG_LOCKWAIT_DETAIL columns
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 following format: 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 awaiting this lock
pid	bigint	ID of the thread holding or awaiting this lock
mode	text	Lock level
granted	boolean	Whether a lock is held
fastpath	boolean	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 a lock conflict occurs
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	State of the backend
waittime	timestamp with time zone	Timestamp when the lock wait starts.
holdtime	timestamp with time zone	Timestamp when the lock starts to be obtained.

Example

Connect to the DN, start a transaction, and perform a query.
1 2

BEGIN; SELECT * FROM t1;
Connect to the CN in another window and truncate table t1.
1

TRUNCATE t1;
In this case, truncation is blocked.

Connect to the DN in another window and query the PG_LOCKWAIT_DETAIL view to obtain the current lock wait information, which helps diagnose the blocking cause.

       
            SELECT * FROM PG_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 |       pid       |        mode         | granted | fastpath |  wait_for_pid   |  conflict_mode  |     query_id      |          query          | application_n
ame |         backend_start         |          xact_start           |          query_start          | state  |           waittime           |           holdtime            
-------+-----------+----------------------------------------------+-----------+----------+----------+------+-------+-------------+----------------+----------+--------+-----------+---------------------+-----------------+---------------------+---------+----------+-----------------+-----------------+-------------------+-------------------------+--------------
----+-------------------------------+-------------------------------+-------------------------------+--------+------------------------------+-------------------------------
     1 | datanode1 | datanode1:140371308516512                    | relation  |    16564 |    25224 |      |       |             |        3956258 |          |        |           | 15/204              | 140371308516512 | AccessExclusiveLock | f       | f        | 140371308446552 | AccessShareLock | 77687093572143017 | TRUNCATE t1             | coordinator1 
    | 2025-02-13 15:07:35.168383+08 | 2025-02-13 15:11:23.613945+08 | 2025-02-13 15:11:23.651944+08 | active | 2025-02-13 15:11:23.65369+08 | 
     2 | datanode1 | datanode1:140371308516512 -> 140371308446552 | relation  |    16564 |    25224 |      |       |             |                |          |        |           | 13/18               | 140371308446552 | AccessShareLock     | t       | f        |                 |                 |                 0 | begin;select * from t1; | gsql         
    | 2025-02-13 14:15:58.989993+08 | 2025-02-13 15:10:40.594882+08 | 2025-02-13 15:10:40.594882+08 | active |                              | 2025-02-13 15:10:40.596243+08
(2 rows)