前段时间遇到线上报大量的锁等待的错误
如下图
Lock wait timeout exceeded; try restarting transaction
首先需要说明一下锁等待错误是怎么产生的.官方的错误编码表有这个的相关解释
锁等待超时
也可以点下面链接去查看相关内容
lock wait timeout exceeded 官网解释
大白话就是一个事务等待另外一个事务执行完再操作,结果等的超过时间限制了就报错了.
但是我好奇的是为什么要去等待另外一个事务,目前猜测是这个事务中操作的用的锁和另外一个事务操作用到的锁有冲突,导致只能等待,这个和死锁是有区别的,死锁是互相持有对方的一个锁,再等待对方的另一个锁,导致循环等待,这就是死锁了.
进行下一步!
查看日志定位到接口和相关的sql执行语句
本地压测成功复现和线上一样的错误.
但是显示报错的sql 人工查看+数据库测试执行计划并没有什么不正常的地方,比如没有用到索引,或者查询时间过长等情况.
便耐着性子从头捋代码,因为代码是前辈写的,也无法甩给别人,只能自己重头熟悉,花了不少时间.这一捋,发现一个大bug:前端代码.多条数据操作没有一次发送到后台,而是循环数据数组单次发送到后台.不管其他的,我首先意识到会产生几个问题
1.循环遍历请求会在后台同时起多个请求线程来响应用户的请求.如果数据大的话,会给服务器造成很大的压力
2.因为这个接口会起一个事务,导致多次请求会产生相对应的事务,多个事务操作相同的表,也有可能操作对应的行,多个事务之间不止产生锁等待,还可能会产生死锁.
事不宜迟,先将前端请求代码改成一次请求.然后再压测发现没有产生锁等待的问题,但是出现了事务执行过长的问题,因为改成一次请求了,后台去遍历循环执行事务都在一个线程上了,不存在事务之间的冲突了,怀疑是事务中的sql语句可能有慢sql,便去梳理涉及的sql语句,发现一条更新语句 where 条件里是子查询的 ,便跑到测试库执行一下,发现慢到离谱.
首先where 里面子查询肯定是会影响性能的,但也不至于这么离谱吧,便去查看子查询的表结构,果然,表结构没加索引,主键索引都没有的那种,如果不加索引 加再多条件也是扫描全表的.可想性能有多差.
发现问题肯定是要解决的,两步走,第一步,给表加索引,执行时间已经降到一秒内了,比之前的二十多秒好很多了,然后再进一步优化子查询,可以嵌套子查询避免重复查询,也可以分两次执行,先查出来,然后再去拼接in,无非就是费劲多写一条sql的时间,两条的话可能会比一次更新慢点,视业务情况选择了.
经过上述一顿操作,果然现在正常了.
总结
工欲善其事必先利其器.你得有好的工具帮助到你.由于种种原因,线上的监控不是那么完备,导致报错解决起来比较困难.所以自己有几件趁手的工具就很重要了.这里便说一下我这次解决问题用到的.
1.查询锁等待的语句,
SELECT * FROM sys.innodb_lock_waits; #这个展示信息比较多点
SELECT * from information_schema.INNODB_TRX; #事务表,查看事务的一些信息
SELECT * from information_schema.INNODB_LOCK_WAITS;
具体字段解释百度
2.本地开发环境配置显示执行sql
这样你可以看到一个接口执行了哪些sql方便你去快速查看原因
3.本地mysql服务端配置显示执行sql
可以参考这位大佬的配置
如何配置mysql 执行log
4.修改超时等待的值,就是 去设置 innodb_lock_wait_timeout这个变量的值,设置的短一点就会更容易复现.因为线上的是会产生大量事务的,而你本地服务器没有线上那种很忙的情况,可以压测本地的mysql,也可以通过设置更短的时间来快速报错.
5.本地最好把日志级别调低,方便看到更详细的错误
其实自己解决也花了不少时间,有些事情还是想的太过于深入最后发现对自己解决问题没什么用,应该也给自己设置一个思考超时时间和行动超时时间,比如都设置半个小时,如果没有任何进展要么去尝试其他的,要么去短暂的休息一下,说不定你会产生更好的思路.
网友评论