Querying Lock Wait Sessions

Function

This API is used to query lock wait sessions by node ID and session ID. Before calling this API:

Learn how to authenticate it.
Obtain the required region and endpoint.

Calling Method

For details, see Calling APIs.

Authorization Information

Each account has all the permissions required to call all APIs, but IAM users must be assigned the required permissions.

If you are using role/policy-based authorization, see Permissions and Supported Actions for details on the required permissions.

If you are using identity policy-based authorization, the following identity policy-based permissions are required.

Action	Access Level	Resource Type (*: required)	Condition Key	Alias	Dependency
gaussdbformysql:instance:listProcesses	List	instance *	g:EnterpriseProjectId g:ResourceTag/<tag-key>	gaussdb:instance:listProcesses	-

URI

GET /v3/{project_id}/instances/{instance_id}/nodes/{node_id}/process/lock-wait

**Table 1** URI parameters
Parameter	Mandatory	Type	Description
project_id	Yes	String	Definition Project ID of a tenant in a region. To obtain this value, see Obtaining a Project ID. Constraints N/A Range The value contains 32 characters. Only letters and digits are allowed. Default Value N/A
instance_id	Yes	String	Definition Instance ID, which uniquely identifies an instance. To obtain this value, see Querying DB Instances. Constraints N/A Range The value contains 36 characters with a suffix of in07. Only letters and digits are allowed. Default Value N/A
node_id	Yes	String	Definition Node ID. To obtain this value, see Querying Details of a DB Instance. Constraints N/A Range The value contains 36 characters. Only letters and digits are allowed. Default Value N/A

**Table 2** Query parameters
Parameter	Mandatory	Type	Description
pid	Yes	Integer	Definition Process ID of the session. To obtain this value, see Querying User Session Threads on a Node. Constraints The value cannot be left blank. Range The value must be a positive integer. Default Value N/A

Request Parameters

**Table 3** Request header parameters
Parameter	Mandatory	Type	Description
X-Auth-Token	Yes	String	Definition User token. To obtain this value, call the IAM API for obtaining a user token. The value of X-Subject-Token in the response header is the token value. Constraints N/A Range N/A Default Value N/A
X-Language	No	String	Definition Request language type. Constraints N/A Range en-us: English zh-cn: Chinese Default Value en-us

Response Parameters

Status code: 200

**Table 4** Response body parameter
Parameter	Type	Description
abnormal_root_cause	AbnormalRootCause object	Definition Root cause information, including root cause analysis for lock waits or long-running transactions.

**Table 5** AbnormalRootCause
Parameter	Type	Description
abnormal_type	String	Definition Exception type. Range LOCK_WAIT: lock wait
lock_root_cause	LockRootCause object	Definition Lock root cause information.

**Table 6** LockRootCause
Parameter	Type	Description
locked_pid	Integer	Definition ID of the locked session. Range N/A
locked_sql	String	Definition SQL statement being executed by the locked session. Range N/A
wait_seconds	Integer	Definition Wait duration, in seconds. Range N/A
lock_db	String	Definition Database where the lock is located. Range N/A
lock_table	String	Definition Table where the lock is located. Range N/A
lock_index	String	Definition Index where the lock is located. Range N/A
lock_mode	String	Definition Lock mode. Range IX: table-level exclusive lock X,REC_NOT_GAP: exclusive record lock X,GAP: gap lock X: row-level exclusive lock
lock_data	String	Definition Identifier of the locked data row. Range N/A
blocker_pid	Integer	Definition Blocking session ID. Range N/A
blocker_state	String	Definition Blocking transaction status. Range RUNNING LOCK WAIT ROLLING BACK COMMITTING
blocker_age	Integer	Definition Blocking transaction duration, in seconds. Range N/A
blocker_rows_locked	Integer	Definition Number of rows locked by the blocking session. Range N/A
blocker_rows_modified	Integer	Definition Number of rows modified by the blocking session. Range N/A
blocker_current_sql	Array of strings	Definition List of SQL statements being executed by the blocking session.
blocker_host	String	Definition Blocking host. Range N/A
blocker_command	String	Definition Blocking command. Range N/A
blocker_thread_id	Integer	Definition Blocking thread ID. Range N/A

Status code: 400

**Table 7** Response body parameters
Parameter	Type	Description
error_code	String	Error code.
error_msg	String	Error message.

Example Request

Querying lock wait sessions

GET https://{endpoint}/v3/4879de6859e345c780f1a22d8bc6f229/instances/889e005a27254666813a78dba117c0b2in14/nodes/889e005a27254666813a78dba117c0b2no01/process/lock-wait?pid=483563

Example Response

Status code: 200

Success.

{
  "abnormal_root_cause" : {
    "abnormal_type" : "LOCK_WAIT",
    "lock_root_cause" : {
      "locked_pid" : 483563,
      "locked_sql" : "UPDATE t_lock_test SET val = 'leaf' WHERE id = 2",
      "wait_seconds" : 20,
      "lock_db" : "test",
      "lock_table" : "table1",
      "lock_index" : "30",
      "lock_data" : "12",
      "lock_mode" : "X,REC_NOT_GAP",
      "blocker_state" : "LOCK WAIT",
      "blocker_age" : 300,
      "blocker_rows_locked" : 20,
      "blocker_host" : "192.168.1.100:54321",
      "blocker_command" : "Sleep",
      "blocker_thread_id" : 20,
      "blocker_pid" : 4834469,
      "blocker_rows_modified" : 1,
      "blocker_current_sql" : [ "UPDATE t_lock_test SET val = 'middle' WHERE id = 3" ]
    }
  }
}