前言

网站突然卡死，查什么都慢，可能是表被锁住了。怎么查谁锁的表，怎么杀掉锁表进程？

一、查看正在执行的进程

SHOW FULL PROCESSLIST;

看State列，要是 Locked 就是锁表了。

二、查看锁表信息

查看当前锁

SELECT * FROM information_schema.INNODB_TRX;

这是InnoDB正在执行的事务。

查看锁等待

SELECT * FROM information_schema.INNODB_LOCK_WAITS;

看谁在等谁的锁。

查看具体的锁

SELECT * FROM information_schema.INNODB_LOCKS;

MySQL 8.0改成了 performance_schema.data_locks。

三、找出锁表的SQL

查看长时间运行的事务

SELECT 
    trx_id,
    trx_state,
    trx_started,
    trx_mysql_thread_id,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) as run_seconds,
    trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

run_seconds 越大，跑的越久，越可能是锁表的元凶。

查看锁等待链

SELECT 
    r.trx_id as waiting_trx_id,
    r.trx_mysql_thread_id as waiting_thread,
    r.trx_query as waiting_query,
    b.trx_id as blocking_trx_id,
    b.trx_mysql_thread_id as blocking_thread,
    b.trx_query as blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

四、杀掉锁表进程

找到进程ID

从上面的查询结果里，找到 blocking_thread，就是锁表的那个连接ID。

杀掉进程

KILL 进程ID;

批量杀掉

比如杀掉所有跑了超过60秒的事务：

SELECT CONCAT('KILL ', trx_mysql_thread_id, ';') 
FROM information_schema.INNODB_TRX 
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;

把结果复制出来执行。

五、常用查询

查看谁在锁什么表

SELECT 
    t.table_name,
    t.table_rows,
    l.lock_type,
    l.lock_mode,
    l.lock_id,
    t2.trx_mysql_thread_id,
    t2.trx_query,
    t2.trx_started
FROM information_schema.INNODB_LOCKS l
JOIN information_schema.INNODB_TRX t2 ON l.lock_trx_id = t2.trx_id
JOIN information_schema.TABLES t ON l.lock_table = CONCAT(t.table_schema, '/', t.table_name);

查看行锁还是表锁

• RECORD 是行锁
• TABLE 是表锁

六、为什么会锁表

原因1：大事务没提交

开了个事务，执行了一大堆，一直没commit，锁就一直占着。

解决： 事务要短小，及时提交。

原因2：没有索引

更新/删除没走索引，会锁全表。

解决： 给where条件加索引。

原因3：DDL操作

ALTER TABLE、OPTIMIZE TABLE这些会锁表。

解决： 低峰期做，或者用pt-online-schema-change。

原因4：长查询

一条SQL跑几分钟，锁住相关行。

解决： 优化慢SQL。

七、MySQL 8.0的变化

MySQL 8.0把INNODB_LOCKS和INNODB_LOCK_WAITS废弃了，改成了performance_schema。

查看锁等待（8.0）

SELECT * FROM performance_schema.data_lock_waits;

查看锁（8.0）

SELECT * FROM performance_schema.data_locks;

常见坑

坑1：杀错进程

杀了正在正常执行的业务进程，导致业务报错。

解决： 先看清楚trx_query是什么，确认是僵尸事务再杀。

坑2：杀了又出来

杀了锁表进程，过一会儿又锁了。

原因： 程序里有死循环，或者连接池自动重连了。

解决： 找到源头，修代码。

坑3：KILL不掉

KILL 123;

进程还在，没杀掉。

原因： 事务在做rollback，需要时间。

解决： 等它回滚完，或者直接重启MySQL。

坑4：查不到锁信息

information_schema.INNODB_TRX是空的。

原因： 可能是MyISAM表，MyISAM的表锁不在InnoDB里。

MyISAM表锁查看：

SHOW OPEN TABLES WHERE In_use > 0;

坑5：锁和MDL搞混

MDL（元数据锁）不是行锁，是表结构的锁。

查看MDL锁：

SELECT * FROM performance_schema.metadata_locks;

总结

锁表排查三步：

1. SHOW FULL PROCESSLIST 看有没有Locked
2. information_schema.INNODB_TRX 找长事务
3. KILL 进程ID 杀掉锁表进程

记住： 杀之前先看清楚是什么SQL，别误杀了正常业务！

遇到问题加QQ23979811 协助处理