使用SHOW PROCESSLIST和SHOW ENGINE INNODB STATUS排查mysql锁等待问题

现象：

mysql 查某表一直不能结束，查别的表没有问题。已知之前刚刚alter此表想把它的一个字段长度增长，但是这个操作一直没有结束。现在应该怎么办?

方案:

使用 SHOW PROCESSLIST; 查看当前所有活动的SQL线程，找出是否有长时间运行的查询或锁定。可配合SHOW ENGINE INNODB STATUS语句查看InnoDB锁等待情况。如果发现有阻塞的进程，可以通过 KILL [thread_id]; 来终止它。

具体经过：

SHOW PROCESSLIST结果如下。可以看到ID87337为我发起的修改字段操作，此时它正处于等待元数据锁的状态。此外也有多个对此表的操作都卡在相同状态：

通义的相关解释：Waiting for table metadata lock 状态并不一定意味着死锁，但它确实表明有多个会话在等待获取表的元数据锁。这种情况通常发生在以下几种场景：
长时间运行的事务：
如果有一个长时间运行的事务持有表的元数据锁（例如正在进行 ALTER TABLE、INSERT、UPDATE 或 DELETE 操作），其他试图访问同一表的操作将被阻塞，直到该事务完成。
未提交的事务：
如果某个事务对表进行了修改但没有提交或回滚，其他会话将无法获得所需的锁，导致它们处于等待状态。
DDL操作：
DDL（数据定义语言）操作如 ALTER TABLE 会隐式地对表加锁，阻止其他会话同时进行某些类型的访问，直到 DDL 操作完成。

然后我应该是把87337kill掉了，然后重新发起了一个alter命令，但这个新的命令仍然一直卡住不能结束。于是我重新调用SHOW PROCESSLIST，看到了以下结果。可看到新的命令ID为87360，又处在等待元数据锁的状态：

为了查看InnoDB锁等待情况，我使用命令SHOW ENGINE INNODB STATUS，返回的信息如下：

SHOW executed successfully=====================================
2025-01-17 02:30:02 7fe54c0cb700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 15 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2620 srv_active, 0 srv_shutdown, 10442746 srv_idle
srv_master_thread log flush and writes: 10445361
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2876
OS WAIT ARRAY INFO: signal count 2877
Mutex spin waits 683, rounds 3861, OS waits 113
RW-shared spins 2768, rounds 82977, OS waits 2761
RW-excl spins 4, rounds 70, OS waits 2
Spin rounds per wait: 5.65 mutex, 29.98 RW-shared, 17.50 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 71256
Purge done for trx's n:o < 71216 undo n:o < 0 state: running but idle
History list length 747
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 71242, not started
MySQL thread id 87347, OS thread handle 0x7fe54c047700, query id 704919 123.158.64.155 root
---TRANSACTION 71239, not started
MySQL thread id 87342, OS thread handle 0x7fe54d49d700, query id 704918 123.158.64.155 root
---TRANSACTION 71234, not started
MySQL thread id 87346, OS thread handle 0x7fe54c14f700, query id 704916 123.158.64.155 root
---TRANSACTION 71233, not started
MySQL thread id 87344, OS thread handle 0x7fe5446ae700, query id 704915 123.158.64.155 root
---TRANSACTION 71230, not started
MySQL thread id 87348, OS thread handle 0x7fe54445c700, query id 704914 123.158.64.155 root
---TRANSACTION 71231, not started
MySQL thread id 87350, OS thread handle 0x7fe5441c8700, query id 704922 123.158.64.155 root
---TRANSACTION 71247, not started
mysql tables in use 1, locked 2
MySQL thread id 87360, OS thread handle 0x7fe544732700, query id 704927 123.158.64.155 root Waiting for table metadata lock
alter table log_styleGeneration 
modify column  `prompt` varchar(1200) NOT NULL COMMENT '提示词'
---TRANSACTION 71240, not started
MySQL thread id 87345, OS thread handle 0x7fe54d4df700, query id 704920 123.158.64.155 root
---TRANSACTION 71241, not started
MySQL thread id 87349, OS thread handle 0x7fe5440c0700, query id 704921 123.158.64.155 root
---TRANSACTION 71208, not started
MySQL thread id 87327, OS thread handle 0x7fe54424c700, query id 704680 123.158.64.155 root
---TRANSACTION 71238, not started
MySQL thread id 87307, OS thread handle 0x7fe54d5a5700, query id 704917 123.158.64.155 root
---TRANSACTION 71225, ACTIVE 1229 sec
MySQL thread id 87357, OS thread handle 0x7fe54c0cb700, query id 704949 123.158.64.155 root init
SHOW ENGINE INNODB STATUS
Trx read view will not see trx with id >= 71226, sees < 71179
---TRANSACTION 71179, ACTIVE 4552 sec
MySQL thread id 87293, OS thread handle 0x7fe544354700, query id 704468 123.158.64.155 root
Trx read view will not see trx with id >= 71180, sees < 71180
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1026 OS file reads, 21448 OS file writes, 14504 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 2 merges
merged operations:insert 2, delete mark 0, delete 0
discarded operations:insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 8 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 633754370
Log flushed up to   633754370
Pages flushed up to 633754370
Last checkpoint at  633754370
0 pending log writes, 0 pending chkp writes
5866 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 216251
Buffer pool size   8191
Free buffers       6103
Database pages     2080
Old database pages 747
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 999, created 1081, written 12653
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2080, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 1, id 140622723802880, state: sleeping
Number of rows inserted 5760, updated 69, deleted 10, read 1982107
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================Execution time: 26ms

使用通义帮忙分析，从SHOW PROCESSLIST和SHOW ENGINE INNODB STATUS返回的信息都可以看到，罪魁祸首是ID为87293的SQL线程，它已经活跃了一个多小时还未结束，极可能是它占有了元数据锁，导致别的操作阻塞：

于是我KILL 87293; 一切都OK了，ID87360的alter命令也顺利结束了。至于87293如何引起的就不得而知了