mysql锁表排查问题步骤

1 确定等待获取锁的进程和sql

select * from information_schema.processlist where info is not null order by time desc

1.1 查询样例

 stae会显示：Waiting for table metadata lock

1.2 kill掉进程

kill 192

1.3 查询是什么进程持有锁

SHOW ENGINE INNODB STATUS

 1.4 找到TRANSACTION部分

------------
TRANSACTIONS
------------
Trx id counter 108476
Purge done for trx's n:o < 103006 undo n:o < 0 state: running but idle
History list length 2374
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 192, OS thread handle 0x7ff11af7f700, query id 46735 10.23.16.109 dbapp init
/* ApplicationName=DBeaver 22.0.1 - SQLEditor <Script-27.sql> */ SHOW ENGINE INNODB STATUS
---TRANSACTION 108066, not started
MySQL thread id 213, OS thread handle 0x7ff1204e0700, query id 45404 127.0.0.1 dbapp Waiting for table metadata lock
create OR replace view `bigdata-web`.assets_information_view(id,group_id, group_name,ip, port,system_name, data_src_name,instance_id) as
SELECT
    rel.assets_id id,
    g.id AS group_id,
    g.NAME AS group_name,
    SUBSTRING_INDEX( a.ip_port, ':', 1 ) AS ip,
    SUBSTRING_INDEX( SUBSTRING_INDEX( a.ip_port, ':', 2 ), ':', - 1 ) AS PORT,
    a.system_name,
    a.data_src_name,
    a.instance_id
FROM
    `bigdata-web`.dsc_assets_information_rel rel,
    `bigdata-web`.dsc_assets_information a,
     `bigdata-web`.dsc_groups g
WHERE
    a.state = 1
    AND a.group_id = g.id
    AND rel.assets_information_id = a.id
---TRANSACTION 108474, not started
MySQL thread id 154, OS thread handle 0x7ff1205a6700, query id 46732 72.118.1.40 dbapp
---TRANSACTION 108475, not started
MySQL thread id 150, OS thread handle 0x7ff11b0c9700, query id 46733 10.11.42.241 dbapp
---TRANSACTION 107937, not started
MySQL thread id 106, OS thread handle 0x7ff11b98d700, query id 44824 10.23.16.109 dbapp
---TRANSACTION 0, not started
MySQL thread id 107, OS thread handle 0x7ff11b94b700, query id 46116 10.23.16.109 dbapp
---TRANSACTION 108405, not started
MySQL thread id 57, OS thread handle 0x7ff11bc63700, query id 46489 72.118.1.103 dbapp
---TRANSACTION 108399, not started
MySQL thread id 56, OS thread handle 0x7ff11be31700, query id 46460 72.118.1.103 dbapp
---TRANSACTION 108401, not started
MySQL thread id 54, OS thread handle 0x7ff11bce7700, query id 46469 72.118.1.103 dbapp
---TRANSACTION 108402, not started
MySQL thread id 52, OS thread handle 0x7ff11bd6b700, query id 46474 72.118.1.103 dbapp
---TRANSACTION 108403, not started
MySQL thread id 50, OS thread handle 0x7ff11bdef700, query id 46479 72.118.1.103 dbapp
---TRANSACTION 108400, not started
MySQL thread id 51, OS thread handle 0x7ff11bdad700, query id 46465 72.118.1.103 dbapp
---TRANSACTION 108404, not started
MySQL thread id 53, OS thread handle 0x7ff11bd29700, query id 46484 72.118.1.103 dbapp
---TRANSACTION 108064, ACTIVE 837 sec
13 lock struct(s), heap size 2936, 1084 row lock(s)
MySQL thread id 212, OS thread handle 0x7ff12045c700, query id 45392 127.0.0.1 dbapp
Trx read view will not see trx with id >= 108065, sees < 102727
---TRANSACTION 102727, ACTIVE 4428 sec
MySQL thread id 55, OS thread handle 0x7ff11bca5700, query id 36149 72.118.1.103 dbapp
Trx read view will not see trx with id >= 102728, sees < 102728
--------
FILE I/O
--------

可以看到下面sql正等待锁

 当前服务正持有锁

 将该服务停止或者排查该服务下等待锁的表的业务

2 TRANSACTION部分参数详解

• Trx id counter：这是当前的交易ID计数器。每个新的交易都会被分配一个独特的ID，从上一个ID增加。

• Purge done：这指示了多少已完成的交易已经从系统中清除。清除过程是指移除不再需要的旧交易信息。

• LOCK STRUCT(S)：这是当前被交易持有的锁的数量。锁用于防止多个交易同时修改相同的数据，这可能会导致冲突。

• ROW LOCK(S)：这是当前被交易锁定的行的数量。

• UNDO LOG ENTRIES：这是与交易相关联的未做日志条目的数量。未做日志用于存储由交易所做的更改的信息，如果需要的话可以用来回滚交易。

• MySQL thread ID：这是与交易关联的MySQL线程的ID。

• OS thread handle：这是与MySQL线程关联的操作系统线程的句柄。

• Query ID：这是与交易关联的查询的ID。