How to Check Table Lock Status in TiDB?

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tidb如何查看锁表情况？

| username: panqiao

MySQL can view it through SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;. How can it be viewed in TiDB?

2
| username: dba远航 | Original post link

It depends on the MySQL version. In version 5.7, there are three related tables, but in version 8, there is no INFORMATION_SCHEMA.INNODB_LOCKS, only INNODB_TRX.

3
| username: panqiao | Original post link

How do I query in MySQL 8?

4
| username: panqiao | Original post link

How do I check TiDB 6.5.5? I’m a bit anxious about troubleshooting right now.

5
| username: tidb菜鸟一只 | Original post link
6
| username: dba远航 | Original post link

Try running SHOW OPEN TABLES WHERE In_use > 0;.

7
| username: zhanggame1 | Original post link

Look at these three:

DATA_LOCK_WAITS

Pessimistic lock waits currently occurring on all TiKV nodes in the cluster;

Only users with PROCESS privileges can query;

Real-time retrieval from all TiKV nodes;

If the cluster is large and heavily loaded, there is a potential risk of performance jitter when viewing this table.

DEADLOCKS

Provides information on several recent deadlock errors on the current TiDB node;

By default, it holds information on the last 10 deadlock errors.

Deadlock issue troubleshooting:

select * from information_schema.deadlocks;

TIDB_TRX

Returns information on all transactions currently executing on TiDB;

Only users with PROCESS privileges can query.

If DDL is stuck, check: mysql.tidb_mdl_view view, which can be used to view information related to the currently blocked DDL.

8
| username: panqiao | Original post link

Thank you for the explanation.

9
| username: panqiao | Original post link

Okay, I’ll give it a try.

10
| username: panqiao | Original post link

Let me take a look.

11
| username: wangccsy | Original post link

A beginner is learning.

12
| username: 双开门变频冰箱 | Original post link

Newbie learning :+1:

13
| username: yulei7633 | Original post link

show open tables where in_use>0;
This is used in MySQL to check if there are any locked tables, and it works well here too.

14
| username: lemonade010 | Original post link

Learned,
DATA_LOCK_WAITS
DEADLOCKS
TIDB_TRX

15
| username: kelvin | Original post link

They are all general-purpose.

16
| username: 小龙虾爱大龙虾 | Original post link

There are no table locks in TiDB, right? The table lock feature hasn’t reached GA, right? Reference: TiDB 功能概览 | PingCAP 文档中心
Analysis related to row lock issues: TiDB 锁冲突问题处理 | PingCAP 文档中心
Metadata lock: 元数据锁 | PingCAP 文档中心

17
| username: panqiao | Original post link

Okay, let me take a look.

18
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.