TiDBTiDB Docs Dash 2024

Jan 9 - Jan 12 (UTC)
Join us to improve docs and win a prize!
Sign InTry Free

UNLOCK STATS

UNLOCK STATS is used to unlock the statistics of a table or tables.

Synopsis

UnlockStatsStmt
UNLOCKSTATSTableNameList
TableNameList
TableName,
TableName
Identifier.Identifier

Examples

Create table t, and insert data into it. When the statistics of table t are not locked, the ANALYZE statement can be successfully executed.

mysql> create table t(a int, b int); Query OK, 0 rows affected (0.03 sec) mysql> insert into t values (1,2), (3,4), (5,6), (7,8); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> analyze table t; Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> show warnings; +-------+------+-----------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------+ | Note | 1105 | Analyze use auto adjusted sample rate 1.000000 for table test.t | +-------+------+-----------------------------------------------------------------+ 1 row in set (0.00 sec)

Lock the statistics of table t and execute ANALYZE. From the output of SHOW STATS_LOCKED, you can see that the statistics of table t have been locked. The warning message shows that the ANALYZE statement has skipped table t.

mysql> lock stats t; Query OK, 0 rows affected (0.00 sec) mysql> show stats_locked; +---------+------------+----------------+--------+ | Db_name | Table_name | Partition_name | Status | +---------+------------+----------------+--------+ | test | t | | locked | +---------+------------+----------------+--------+ 1 row in set (0.01 sec) mysql> analyze table t; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------+ | Note | 1105 | Analyze use auto adjusted sample rate 1.000000 for table test.t | | Warning | 1105 | skip analyze locked table: t | +---------+------+-----------------------------------------------------------------+ 2 rows in set (0.00 sec)

Unlock the statistics of table t and ANALYZE can be successfully executed again.

mysql> unlock stats t; Query OK, 0 rows affected (0.01 sec) mysql> analyze table t; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> show warnings; +-------+------+-----------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------+ | Note | 1105 | Analyze use auto adjusted sample rate 1.000000 for table test.t | +-------+------+-----------------------------------------------------------------+ 1 row in set (0.00 sec)

MySQL compatibility

This statement is a TiDB extension to MySQL syntax.

See also

Was this page helpful?

Download PDFRequest docs changesAsk questions on Discord
Playground
New
One-stop & interactive experience of TiDB's capabilities WITHOUT registration.
Products
TiDB
TiDB Dedicated
TiDB Serverless
Pricing
Get Demo
Get Started
© 2024 PingCAP. All Rights Reserved.
Privacy Policy.