mysql update语句锁全表导致的等待超时问题

背景

事情起因于一次生产的错误日志，日志的主要内容是sql执行超时，update product set num = num -1 where id=xx 这是一条根据索引更新商品表数据的sql语句，商品表product有1千多万数据，设置的数据库连接的读取超时时间是3秒，一般情况下，按照主键更新记录只需要10ms，那为什么会导致update超时呢？

查找真相

首先排除了偶尔的网络问题，因为我们发现这个根据主键更新记录超时的日志经常出现凌晨2点左右，而且经过最近5天的观察，其他时间段并没有这个超时错误日志，而且运维反馈这段时间内并没有网络的问题。
在排除了网络问题后，想到了是否是Mysql数据库负载的问题，查看数据库cpu使用率才10%，再次排除是数据库负载高的问题，答案开始变得迷茫起来
再经过前面几步的排除后，我们又重新回到了update product set num = num -1 where id=xx 这个sql本身的问题，如果数据库当时没有压力，但是执行时间又很久，那么数据库在执行这个sql时肯定在等待什么资源，一说到等待什么资源，我们立马想到了，这个根据主键更新记录需要获取到id=xx的主键索引的锁，会不会这个id=xx的锁被某个其他的长时间运行的事务持有而导致等待锁资源呢？
想到这一点之后，我们才想到查看一下数据库这个时间点的慢查询日志，突然一条根据时间字段更新记录的sql映入眼帘，

update product set buynum = 100 where update_time > xx

这条导致问题的update语句没有走索引的操作，会锁全表(也就是说会锁定所有的主键索引)，而且耗时很长，所以真相也随之浮出水面，是因为有一条update的语句锁全表导致的另一条根据主键id来更新记录的update语句执行超时

总结

那我们知道事情的真相，也就是update语句不通过索引更新记录时会长时间的锁定全表，那么如何解决这个问题呢？
方法1： 尽量的减少update全表记录时锁全表的时长，比如分批执行，虽然还是会锁全表，但是因为锁全表的时间短，可以很快释放
方法2：使用mysql安全模式set sql_safe_updates = 1;
在这种模式下，update或者delete数据要么带上索引字段(目的是为了不锁全表，而只需要锁定特定的记录)要么加上limit限制更新记录的数量(目的是为了减少锁全表的时间)

其实最重要就是要意识到update操作可能会锁表，导致其他的update语句等待，进而级联引起雪崩效应