Business query hangs, want to know specific lock information and read/write status

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

Original topic: 业务查询hang死,想知道具体的锁信息和读写情况

| username: TiDBer_小阿飞

[TiDB Usage Environment] Production Environment
[TiDB Version] V6.5.3
[Encountered Problem: Issue Phenomenon and Impact] Business query SQL, checking TiDB server node logs and TiKV node logs, there are lock phenomena appearing. Specific queries cannot find the reason. I hope the experts can help troubleshoot.
[Resource Configuration] 3PD + 4TiKV + 4TiDB + 3TiFlash
[Attachments: Screenshots/Logs/Monitoring] Internal network environment, can only upload blurred images, please don’t mind, experts.

Dashboard Execution Plan


TiDB Node Logs

TiKV Node Error Logs

| username: TiDBer_小阿飞 | Original post link

The key is locked (will clean up), which should be a read-write conflict.
I want to find the region through the region key command, but there is no tiup ctl in the intranet environment.
Is there any other way to find the ID?
Or any statements or methods to find conflicting reads and writes?
Is there any way to optimize similar issues?

| username: TiDBer_jYQINSnf | Original post link

The key is locked there, there’s a key, check which region the key belongs to:
pd-ctl region keys --format=hex 7AXXXXXXXX
You can find out which region it belongs to, then look in the information_schema to see which table it belongs to. Knowing the table can roughly locate it, I don’t know any other methods.

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

The SQL is written so :joy_cat:

| username: tidb狂热爱好者 | Original post link

This select won’t get stuck. I guess your select is wrapped in a transaction by the developer.

| username: TiDBer_小阿飞 | Original post link

SQL has already stopped, but TIKV is still consistently reporting errors similar to those in the image.

| username: TiDBer_C33 | Original post link

The command SELECT TIDB_DECODE_KEY('7480000000000000465F728000002E4717D540') AS decoded_key; can display the corresponding table ID and row ID.

| username: TiDBer_小阿飞 | Original post link

The db_name and table_name have been identified, thank you.