Sign InTry Free

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.

Download PDFRequest docs changesAsk questions on TiDB Forum
Was this page helpful?
Open Source Ecosystem
TiDB
TiKV
TiSpark
Chaos Mesh
© 2022 PingCAP. All Rights Reserved.