Updated on 2023-12-29 GMT+08:00

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 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

Examples

  1. Connect to the DN, start a transaction, and run the following command:

    1
    begin;select * from t1;
    

  2. Connect to the CN in another window and truncate table t1.

    1
    truncate t1;
    

    In this case, truncation is blocked.

  3. Open another window to connect to the CN and run the select * from pgxc_lockwait_detail; command.

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    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)