DATA_LOCK_WAITS

The DATA_LOCK_WAITS table shows the ongoing lock-wait information on all TiKV nodes in a cluster, including the lock-wait information of pessimistic transactions and the information of optimistic transactions being blocked.

USE information_schema; DESC data_lock_waits;
+------------------------+---------------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+---------------------+------+------+---------+-------+ | KEY | text | NO | | NULL | | | KEY_INFO | text | YES | | NULL | | | TRX_ID | bigint(21) unsigned | NO | | NULL | | | CURRENT_HOLDING_TRX_ID | bigint(21) unsigned | NO | | NULL | | | SQL_DIGEST | varchar(64) | YES | | NULL | | | SQL_DIGEST_TEXT | text | YES | | NULL | | +------------------------+---------------------+------+------+---------+-------+

The meaning of each column field in the DATA_LOCK_WAITS table is as follows:

  • KEY: The key that is waiting for the lock and in the hexadecimal form.
  • KEY_INFO: The detailed information of KEY. See the KEY_INFO section.
  • TRX_ID: The ID of the transaction that is waiting for the lock. This ID is also the start_ts of the transaction.
  • CURRENT_HOLDING_TRX_ID: The ID of the transaction that currently holds the lock. This ID is also the start_ts of the transaction.
  • SQL_DIGEST: The digest of the SQL statement that is currently blocked in the lock-waiting transaction.
  • SQL_DIGEST_TEXT: The normalized SQL statement (the SQL statement without arguments and formats) that is currently blocked in the lock-waiting transaction. It corresponds to SQL_DIGEST.

KEY_INFO

The KEY_INFO column shows the detailed information of the KEY column. The information is shown in the JSON format. The description of each field is as follows:

  • "db_id": The ID of the schema to which the key belongs.
  • "db_name": The name of the schema to which the key belongs.
  • "table_id": The ID of the table to which the key belongs.
  • "table_name": The name of the table to which the key belongs.
  • "partition_id": The ID of the partition where the key is located.
  • "partition_name": The name of the partition where the key is located.
  • "handle_type": The handle type of the row key (that is, the key that stores a row of data). The possible values ​​are as follows:
    • "int": The handle type is int, which means that the handle is the row ID.
    • "common": The handle type is not int64. This type is shown in the non-int primary key when clustered index is enabled.
    • "unknown": The handle type is currently not supported.
  • "handle_value": The handle value.
  • "index_id": The index ID to which the index key (the key that stores the index) belongs.
  • "index_name": The name of the index to which the index key belongs.
  • "index_values": The index value in the index key.

In the above fields, if the information of a field is not applicable or currently unavailable, the field is omitted in the query result. For example, the row key information does not contain index_id, index_name, and index_values; the index key does not contain handle_type and handle_value; non-partitioned tables do not display partition_id and partition_name; the key information in the deleted table cannot obtain schema information such as table_name, db_id, db_name, and index_name, and it is unable to distinguish whether the table is a partitioned table.

Example

select * from information_schema.data_lock_waits\G
*************************** 1. row *************************** KEY: 7480000000000000355F728000000000000001 KEY_INFO: {"db_id":1,"db_name":"test","table_id":53,"table_name":"t","handle_type":"int","handle_value":"1"} TRX_ID: 426790594290122753 CURRENT_HOLDING_TRX_ID: 426790590082449409 SQL_DIGEST: 38b03afa5debbdf0326a014dbe5012a62c51957f1982b3093e748460f8b00821 SQL_DIGEST_TEXT: update `t` set `v` = `v` + ? where `id` = ? 1 row in set (0.01 sec)

The above query result shows that the transaction of the ID 426790594290122753 is trying to obtain the pessimistic lock on the key "7480000000000000355F728000000000000001" when executing a statement that has digest "38b03afa5debbdf0326a014dbe5012a62c51957f1982b3093e748460f8b00821" and is in the form of update `t` set `v` = `v` + ? where `id` = ?, but the lock on this key was held by the transaction of the ID 426790590082449409.

Was this page helpful?