线上MySQL8锁表的问题

源头

最近线上出现了锁表，一个查询大表的sql语句居然执行了好几个小时，然后其他的sql语句去操作大表，然后运维人员提示出现了锁表问题。

什么情况下会出现锁表

• 锁表一般发生在insert、delete、update语句中。
• insert、update、delete等语句操作表，使用独占锁，锁住表。但是如果表数据量比较大，那么上述的操作可能会很慢，这样其他的写操作语句会阻塞。

排查锁表的情况

查看当前的连接

mysql> show processlist;

查看当前锁表的状态

mysql> SHOW OPEN TABLES;

是否存在未正常释放的锁

mysql> SHOW ENGINE INNODB STATUS;

查看当前运行的事务

SELECT * FROM information_schema.innodb_trx;

查看当前出现的锁

select * from performance_schema.data_locks;

查看当前等待的锁

select * from performance_schema.data_lock_waits;

杀死锁表的线程

kill 线程id

思考，出现锁表的根源？

锁表的出现主要在于当前的sql语句操作表，处理太慢，那么针对慢sql，我们要开启慢sql日志。

• 是否开启了慢查询日志

mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------------------------+
| Variable_name       | Value                                           |
+---------------------+-------------------------------------------------+
| slow_query_log      | OFF                                             |
| slow_query_log_file | /var/lib/mysql/izuf6ea4rfo45y44oj74jhz-slow.log |
+---------------------+-------------------------------------------------+
2 rows in set (0.00 sec)

• 开启慢查询日志
  set global slow_query_log=1;
• MySQL配置文件my.cnf配置慢查询

slow_query_log =1
slow_query_log_file=/var/log/sunpy-slow.log
long_query_time=1

1. slow_query_log 慢查询开启状态
2. slow_query_log_file 慢查询日志存放的位置(这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录)
3. long_query_time 查询超过多少秒才记录

参考

为什么MySQL中的kill命令杀死线程，却未马上杀死？
https://www.jb51.net/article/284531mdb.htm
information_schema.innodb_trx表字段的含义？
https://blog.csdn.net/sugarCYF/article/details/108432902