解决锁超时Lock wait timeout异常

异常情况

Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
Query is: delete from link_info  where  ip= ? , parameters ['200.168.100.89']

解决思路

1、查看innodb_lock_wait_timeout = 8正常，调大也是指标不治本
2、打开锁超时日志
找到my.cnf文件，修改参数：
innodb_lock_wait_log = OFF改为ON
然后到用户log目录vim innodb_lock_wait.log

[2022-10-09T11:07:55.474982]||||0||||#WARN:DESC=lock_wait_time:748ms,
req_thd_id:1933, req_trx_id:69924555, req_trx_seq:0, req_gtm_gtid:0, req_sql:[delete from link_info
                 where ip = '200.168.100.89'
                 and agent_port = 45702],
blk_thd_id:1539, blk_trx_id:69924419, blk_trx_seq:0, blk_gtm_gtid:0, blk_sql:[delete from link_info
                 where ip = '200.168.31.138'
 blk_thd_id:1801, blk_trx_id:69924433, blk_trx_seq:0, blk_gtm_gtid:0, blk_sql:[delete from link_info
                 where ip = '200.168.31.138'
                 ...
                 ...
                 ...

由日志可见，第一个请求线程是req等待线程，其余后面的都是blk堵塞线程。分析都是delete同一张表，考虑是高并发请求下，sql没有走索引，delete语句按行扫描，其余语句只能等待其执行完才能执行，效率低下导致锁超时。

解决方案

原表仅有自增id作为主键，在其基础上给ip加上索引，锁超时问题不再出现。