正式数据库最近抛出Lock wait timeout exceeded; try restarting transaction异常,而且一段时间内的db连接数飙升到200+,很多访问都很慢,超时8s以上,无法定位原因,只能从日志入手,最后分析到可能存在的原因
1-最近加了个需求,A模块通过http请求访问B模块,B模块再访问C模块,然后再返回结果,可能由于没有设置http超时时间导致服务超时等待
2-A模块会频繁的更新某一个表和写入这个表数据,想到数据库默认的事务隔离级别是可重复读,若没有索引的情况下,写入或更新会锁定整张表,导致其它线程写入处于等待状态,最后导致接口访问超时
从网上看到几种解决办法:
通过show variables like 'innodb_lock_wait_timeout' 查看数据库默认等待时间,为50,无问题
通过 SELECT @@tx_isolation; 查看当前数据库隔离级别,为READ-COMMITTED
通过 SHOW FULL PROCESSLIST; 查看当前数据库的线程情况:
通过 SELECT * FROM information_schema.INNODB_TRX; 看下里面是否有正在锁定的事务线程,查看trx_mysql_thread_id ,
看看ID是否在show full processlist里面的sleep线程中,如果是,就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了,我们需要手动kill掉。
KILL 110110;
以上办法都没能解决问题,最后只能在那个更新很频繁的表里面多增加几个字段的索引,问题貌似解决了,具体原因可能是之前没有索引是表级锁,会造成线程等待,加索引之后,根据索引更新数据或查询数据时,变成行级锁,不影响其它线程读写
最后总结:
InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!
在实际应用中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能