故障案例---innodb表出现大量的Waiting for table level lock

来源：互联网发布：淘宝app怎么看卖家信誉编辑：程序博客网时间：2024/05/22 07:40

故障现象

show full processlist发现大量的innodb表出现Waiting for table level lock，业务将近不可用

原因分析

1 一开始当然是认为这是myisam引擎导致的，扫了一圈发现该db下确实有一个myisam表；

2 不过故障时无论是show processlist还是innodb_trx等结果看，都没有发现这个myisam表的记录；

3 排查了一圈又没有其他发现，于是又将怀疑的对象转到这个唯一存在的myisam表

4 鬼使神差地查了下information_schema.triggers表，发现了大量的触发器，触发器的意思大概是：无论什么表做什么更新，都将最后的时间戳写入到这个myisam表

5 基本断定原因就是它了，触发器更新myisam表，导致show processlist显示innodb处于Waiting for table level lock

故障模拟

1 sysbench准备一个表的数据，数据量很少即可，一个表即可

sysbench --report-interval=1 --num-threads=50 --max-time=2000 --test=sysbench/tests/db/oltp.lua --mysql-user='root' --mysql-password='mysql19-=' --oltp-table-size=50000 --mysql-host=10.9.158.2 --mysql-port=3306 --mysql-db=xx --max-requests=500000 --oltp-tables-count=1 prepare

2 准备完数据，登陆db创建1个myisam表

CREATE TABLE `tri_update` ( `tablename` char(64) NOT NULL DEFAULT '', `uptime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`tablename`)) engine =myisam;

3 创建1个更新触发器

delimiter $

mysql> CREATE trigger sbtest1_after_update after update on sbtest1 for each row begin update tri_update set uptime = now() where tablename = 'sbtest1'; end$
Query OK, 0 rows affected (0.00 sec)

4 sysbench压测

5 登陆db show processlist查看

可以看到故障就复现了，所有卡住的线程都是update SQL，并且显示的并不是myisam表中的tri_update，这无疑给故障排查带来了极大的困难。

6 我们进一步分析故障的原因，将引擎换成innodb，实际上性能也是存在问题的，但是效果会好很多（这个应该是buffer pool之类起的作用，行锁等待总比表锁好太多），因为这个触发器的功能是永远地更新同一条记录，存在行锁等待

alter table tri_update engine =innodb;

但是这个时候可以看到，显示的SQL就是真正的瓶颈SQL了，即触发器中的update SQL

通过查看innodb_trx表也能非常明显地得知当前处于锁等待

总结

1 别用myisam，别用myisam！

2 业务层优化，避免同行更新，废弃这种逻辑，或者采用其他的方案，比如将一行拆成1000行，当A表更新时，随机更新B表的其中一行，避免大量的行锁竞争

3 算是一个小bug，为啥myisam情况下show出来的不是实际在执行的SQL

阅读全文

0 0