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.