- Docs Home
- About TiDB Cloud
- Get Started
- Develop Applications
- Overview
- Quick Start
- Build a TiDB Developer Cluster
- CRUD SQL in TiDB
- Build a Simple CRUD App with TiDB
- Example Applications
- Connect to TiDB
- Design Database Schema
- Write Data
- Read Data
- Transaction
- Optimize
- Troubleshoot
- Reference
- Cloud Native Development Environment
- Manage Cluster
- Plan Your Cluster
- Create a TiDB Cluster
- Connect to Your TiDB Cluster
- Set Up VPC Peering Connections
- Use an HTAP Cluster with TiFlash
- Scale a TiDB Cluster
- Upgrade a TiDB Cluster
- Delete a TiDB Cluster
- Use TiDB Cloud API (Beta)
- Migrate Data
- Import Sample Data
- Migrate Data into TiDB
- Configure Amazon S3 Access and GCS Access
- Migrate from MySQL-Compatible Databases
- Migrate Incremental Data from MySQL-Compatible Databases
- Migrate from Amazon Aurora MySQL in Bulk
- Import or Migrate from Amazon S3 or GCS to TiDB Cloud
- Import CSV Files from Amazon S3 or GCS into TiDB Cloud
- Import Apache Parquet Files from Amazon S3 or GCS into TiDB Cloud
- Troubleshoot Access Denied Errors during Data Import from Amazon S3
- Export Data from TiDB
- Back Up and Restore
- Monitor and Alert
- Overview
- Built-in Monitoring
- Built-in Alerting
- Third-Party Monitoring Integrations
- Tune Performance
- Overview
- Analyze Performance
- SQL Tuning
- Overview
- Understanding the Query Execution Plan
- SQL Optimization Process
- Overview
- Logic Optimization
- Physical Optimization
- Prepare Execution Plan Cache
- Control Execution Plans
- TiKV Follower Read
- Coprocessor Cache
- Garbage Collection (GC)
- Tune TiFlash performance
- Manage User Access
- Billing
- Reference
- TiDB Cluster Architecture
- TiDB Cloud Cluster Limits and Quotas
- TiDB Limitations
- SQL
- Explore SQL with TiDB
- SQL Language Structure and Syntax
- SQL Statements
ADD COLUMN
ADD INDEX
ADMIN
ADMIN CANCEL DDL
ADMIN CHECKSUM TABLE
ADMIN CHECK [TABLE|INDEX]
ADMIN SHOW DDL [JOBS|QUERIES]
ALTER DATABASE
ALTER INDEX
ALTER TABLE
ALTER TABLE COMPACT
ALTER USER
ANALYZE TABLE
BATCH
BEGIN
CHANGE COLUMN
COMMIT
CHANGE DRAINER
CHANGE PUMP
CREATE [GLOBAL|SESSION] BINDING
CREATE DATABASE
CREATE INDEX
CREATE ROLE
CREATE SEQUENCE
CREATE TABLE LIKE
CREATE TABLE
CREATE USER
CREATE VIEW
DEALLOCATE
DELETE
DESC
DESCRIBE
DO
DROP [GLOBAL|SESSION] BINDING
DROP COLUMN
DROP DATABASE
DROP INDEX
DROP ROLE
DROP SEQUENCE
DROP STATS
DROP TABLE
DROP USER
DROP VIEW
EXECUTE
EXPLAIN ANALYZE
EXPLAIN
FLASHBACK TABLE
FLUSH PRIVILEGES
FLUSH STATUS
FLUSH TABLES
GRANT <privileges>
GRANT <role>
INSERT
KILL [TIDB]
MODIFY COLUMN
PREPARE
RECOVER TABLE
RENAME INDEX
RENAME TABLE
REPLACE
REVOKE <privileges>
REVOKE <role>
ROLLBACK
SELECT
SET DEFAULT ROLE
SET [NAMES|CHARACTER SET]
SET PASSWORD
SET ROLE
SET TRANSACTION
SET [GLOBAL|SESSION] <variable>
SHOW ANALYZE STATUS
SHOW [GLOBAL|SESSION] BINDINGS
SHOW BUILTINS
SHOW CHARACTER SET
SHOW COLLATION
SHOW [FULL] COLUMNS FROM
SHOW CREATE SEQUENCE
SHOW CREATE TABLE
SHOW CREATE USER
SHOW DATABASES
SHOW DRAINER STATUS
SHOW ENGINES
SHOW ERRORS
SHOW [FULL] FIELDS FROM
SHOW GRANTS
SHOW INDEX [FROM|IN]
SHOW INDEXES [FROM|IN]
SHOW KEYS [FROM|IN]
SHOW MASTER STATUS
SHOW PLUGINS
SHOW PRIVILEGES
SHOW [FULL] PROCESSSLIST
SHOW PROFILES
SHOW PUMP STATUS
SHOW SCHEMAS
SHOW STATS_HEALTHY
SHOW STATS_HISTOGRAMS
SHOW STATS_META
SHOW STATUS
SHOW TABLE NEXT_ROW_ID
SHOW TABLE REGIONS
SHOW TABLE STATUS
SHOW [FULL] TABLES
SHOW [GLOBAL|SESSION] VARIABLES
SHOW WARNINGS
SHUTDOWN
SPLIT REGION
START TRANSACTION
TABLE
TRACE
TRUNCATE
UPDATE
USE
WITH
- Data Types
- Functions and Operators
- Overview
- Type Conversion in Expression Evaluation
- Operators
- Control Flow Functions
- String Functions
- Numeric Functions and Operators
- Date and Time Functions
- Bit Functions and Operators
- Cast Functions and Operators
- Encryption and Compression Functions
- Locking Functions
- Information Functions
- JSON Functions
- Aggregate (GROUP BY) Functions
- Window Functions
- Miscellaneous Functions
- Precision Math
- Set Operations
- List of Expressions for Pushdown
- TiDB Specific Functions
- Clustered Indexes
- Constraints
- Generated Columns
- SQL Mode
- Table Attributes
- Transactions
- Views
- Partitioning
- Temporary Tables
- Cached Tables
- Character Set and Collation
- Read Historical Data
- System Tables
mysql
- INFORMATION_SCHEMA
- Overview
ANALYZE_STATUS
CLIENT_ERRORS_SUMMARY_BY_HOST
CLIENT_ERRORS_SUMMARY_BY_USER
CLIENT_ERRORS_SUMMARY_GLOBAL
CHARACTER_SETS
CLUSTER_INFO
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
DATA_LOCK_WAITS
DDL_JOBS
DEADLOCKS
ENGINES
KEY_COLUMN_USAGE
PARTITIONS
PROCESSLIST
REFERENTIAL_CONSTRAINTS
SCHEMATA
SEQUENCES
SESSION_VARIABLES
SLOW_QUERY
STATISTICS
TABLES
TABLE_CONSTRAINTS
TABLE_STORAGE_STATS
TIDB_HOT_REGIONS_HISTORY
TIDB_INDEXES
TIDB_SERVERS_INFO
TIDB_TRX
TIFLASH_REPLICA
TIKV_REGION_PEERS
TIKV_REGION_STATUS
TIKV_STORE_STATUS
USER_PRIVILEGES
VIEWS
- System Variables
- API Reference
- Storage Engines
- Dumpling
- Table Filter
- Troubleshoot Inconsistency Between Data and Indexes
- FAQs
- Release Notes
- Support
- Glossary
DATA_LOCK_WAITS
The DATA_LOCK_WAITS
table shows the ongoing pessimistic locks waiting on all TiKV nodes in the cluster.
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
.
- Only the users with the PROCESS privilege can query this table.
- Currently, the table can only record the pessimistic lock waiting information. If an optimistic transaction (such as an autocommit transaction) is blocked by a pessimistic lock, this table will not display the corresponding lock waiting information.
- The information in the
DATA_LOCK_WAITS
table is obtained in real time from all TiKV nodes during the query. Currently, even if a query has theWHERE
condition, the information collection is still performed on all TiKV nodes. If your cluster is large and the load is high, querying this table might cause potential risk of performance jitter. Therefore, use it according to your actual situation. - Information from different TiKV nodes is NOT guaranteed to be snapshots of the same time.
- The information (SQL digest) in the
SQL_DIGEST
column is the hash value calculated from the normalized SQL statement. The information in theSQL_DIGEST_TEXT
column is internally queried from statements summary tables, so it is possible that the corresponding statement cannot be found internally. For the detailed description of SQL digests and the statements summary tables, see Statement Summary Tables.
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.
If a key comes from a table with partitioning enabled, and the information of the schema to which the key belongs cannot be queried due to some reasons (for example, the table to which the key belongs has been deleted) during the query, the ID of the partition to which the key belongs might be appear in the table_id
field. This is because TiDB encodes the keys of different partitions in the same way as it encodes the keys of several independent tables. Therefore, when the schema information is missing, TiDB cannot confirm whether the key belongs to an unpartitioned table or to one partition of a 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
.