Querying InnoDB Lock Waits

Function

Query InnoDB lock waits. Currently, this function is supported only for MySQL DB instances.

URI

GET /v3/{project_id}/instances/{instance_id}/innodb-locks

**Table 1** Path parameters
Parameter	Mandatory	Type	Description
project_id	Yes	String	Project ID. To obtain this value, see Obtaining a Project ID.
instance_id	Yes	String	Instance ID.

**Table 2** Query parameters
Parameter	Mandatory	Type	Description
db_user_id	Yes	String	Database user ID

Request Parameters

**Table 3** Request header parameters
Parameter	Mandatory	Type	Description
X-Auth-Token	Yes	String	User token.
X-Language	No	String	Language. The value can be: zh-cn en-us

Response Parameters

Status code: 200

**Table 4** Response body parameters
Parameter	Type	Description
innodb_trx	Array of InnodbTrx objects	Information about the transaction that holds or waits for a lock.
innodb_lock_waits	Array of InnodbLockWaits objects	Mapping between the lock requested by each transaction and the lock that blocks the request.
count	Integer	Number of transactions that hold or wait for a lock.

**Table 5** InnodbTrx
Parameter	Type	Description
trx_id	String	Transaction ID.
trx_state	String	Transaction status.
trx_started	String	Time when the transaction was started.
trx_wait_started	String	Time when the transaction wait was started.
trx_mysql_thread_id	String	Session ID, same as the ID returned by the ListProcesses API.
trx_query	String	SQL statement executed by the transaction.
trx_tables_locked	String	Number of tables with row locks.
trx_rows_locked	String	Number of rows locked (approximate).
trx_rows_modified	String	Number of rows inserted or modified by the transaction.
trx_isolation_level	String	Isolation level.
innodb_wait_locks	Array of InnodbLock objects	Lock waits
innodb_hold_locks	Array of InnodbLock objects	Held locks.

**Table 6** InnodbLock
Parameter	Type	Description
lock_id	String	Lock ID.
lock_trx_id	String	Transaction ID.
lock_mode	String	Lock mode. The value can be S[,GAP], X[,GAP], IS[,GAP], IX[,GAP], AUTO_INC, or UNKNOWN.
lock_type	String	Lock type. The value can be RECORD or TABLE. RECORD indicates a row lock and TABLE indicates a table lock.
lock_table	String	Name of the table that has been locked or contains locked records.
lock_index	String	Index that has been locked if lock_type is RECORD. If lock_type is Table, null if returned.
lock_space	String	Index that has been locked if lock_type is RECORD. If lock_type is Table, null if returned.
lock_page	String	Page number of the locked record if lock_type is RECORD. If lock_type is Table, null if returned.
lock_rec	String	Heap number of the locked record if lock_type is RECORD. If lock_type is Table, null if returned.
lock_data	String	Primary key locked by the transaction. If lock_type is Table, null if returned.

**Table 7** InnodbLockWaits
Parameter	Type	Description
requesting_trx_id	String	ID of the transaction that requests the lock.
requested_lock_id	String	ID of the lock requested.
blocking_trx_id	String	ID of the blocking transaction.
blocking_lock_id	String	ID of the blocking lock.

Status code: 400

**Table 8** Response body parameters
Parameter	Type	Description
error_code	String	Error code. Minimum length: 8 Maximum length: 36
error_msg	String	Error message. Minimum length: 2 Maximum length: 512

Status code: 500

**Table 9** Response body parameters
Parameter	Type	Description
error_code	String	Error code. Minimum length: 8 Maximum length: 36
error_msg	String	Error message. Minimum length: 2 Maximum length: 512

Example Requests

Querying InnoDB lock waits

GET https://das.cn-north-1.myhuaweicloud.com/v3/054e292c9880d4992f02c0196d3ea468/instances/da304cd5bbb944de828759bc7be3d3fein01/innodb-locks?db_user_id=2c250598-1e3c-4d31-bc19-be1d866247e6

Example Responses

Status code: 200

{
  "innodb_trx" : [ {
    "trx_id" : "1080883393",
    "trx_state" : "LOCK WAIT",
    "trx_started" : "2021-01-14 18:49:26",
    "trx_wait_started" : "2021-01-14 18:49:26",
    "trx_mysql_thread_id" : "115602",
    "trx_query" : "DELETE FROM adfasdf WHERE  id =1",
    "trx_tables_locked" : "1",
    "trx_rows_locked" : "1",
    "trx_rows_modified" : "0",
    "trx_isolation_level" : "REPEATABLE READ",
    "innodb_wait_locks" : [ {
      "lock_id" : "1080883393:27750:3:2",
      "lock_trx_id" : "1080883393",
      "lock_mode" : "X",
      "lock_type" : "RECORD",
      "lock_table" : "`abc`.`adfasdf`",
      "lock_index" : "GEN_CLUST_INDEX",
      "lock_space" : "27750",
      "lock_page" : "3",
      "lock_rec" : "2",
      "lock_data" : "0x0000005528EC"
    } ],
    "innodb_hold_locks" : [ ]
  }, {
    "trx_id" : "1080882971",
    "trx_state" : "LOCK WAIT",
    "trx_started" : "2021-01-14 18:49:25",
    "trx_wait_started" : "2021-01-14 18:49:25",
    "trx_mysql_thread_id" : "115598",
    "trx_query" : "DELETE FROM adfasdf WHERE  id =1",
    "trx_tables_locked" : "1",
    "trx_rows_locked" : "1",
    "trx_rows_modified" : "0",
    "trx_isolation_level" : "REPEATABLE READ",
    "innodb_wait_locks" : [ ],
    "innodb_hold_locks" : [ {
      "lock_id" : "1080882971:27750:3:2",
      "lock_trx_id" : "1080882971",
      "lock_mode" : "X",
      "lock_type" : "RECORD",
      "lock_table" : "`abc`.`adfasdf`",
      "lock_index" : "GEN_CLUST_INDEX",
      "lock_space" : "27750",
      "lock_page" : "3",
      "lock_rec" : "2",
      "lock_data" : "0x0000005528EC"
    } ]
  } ],
  "innodb_lock_waits" : [ {
    "requesting_trx_id" : "1080883393",
    "requested_lock_id" : "1080883393:27750:3:2",
    "blocking_trx_id" : "1080882971",
    "blocking_lock_id" : "1080882971:27750:3:2"
  } ],
  "count" : 2
}