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 ofKEY
. See the KEY_INFO section.TRX_ID
: The ID of the transaction that is waiting for the lock. This ID is also thestart_ts
of the transaction.CURRENT_HOLDING_TRX_ID
: The ID of the transaction that currently holds the lock. This ID is also thestart_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 toSQL_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
.