Lock wait timeout exceeded

问题1：线上mysql更新报错

Update All error:【SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
The SQL being executed was: UPDATE `amz_email` SET `is_read`=1 WHERE `id`='28785'】

一开始发现是int => sting 未走索引引起来，于是调整SQL为

UPDATE `amz_email` SET `solved_type`=2, `response_needed`=0, `as_status`=1 WHERE `id`=28773

系统继续报错

Update All error:【SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
The SQL being executed was: UPDATE `amz_email` SET `solved_type`=2, `response_needed`=0, `as_status`=1 WHERE `id`=28773】

一 查看innodb的锁 显示正常
select * from information_schema.innodb_locks

image.png

二 查看事务提交情况
select * from information_schema.INNODB_TRX; 发现有条件事务一直未提交

企业微信截图_16696950592704.png

三 查看show  PROCESSLIST;发货12956643这条回话状态为sleep状态

企业微信截图_16696950666785.png

kill 12956643 这条会话 服务恢复正常