【纪实】记一次因为update语句导致的心惊胆战！

现象

一个万里无云的上午，正想好好轻松一下，突然客户在群里疯狂的Q我，你们提供的数据怎么有那么多缺漏的啊，估计此时客户心里面在想，你们还行不行了？这么一点的数据就顶不住了！

想摸鱼的我赶紧提起神来，熟练地打开程序日志往下拉，一堆堆的报错显示在略显拥挤的屏幕上，屏幕上显示着：”MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction“，这让我心头一颤，这个错误竟然是头一次见，看来又得是一阵腥风血雨了。

原因

这个报错提示更新语句已经超过了锁等待的最大时间了，随后看了一下数据库用的是innodb的默认锁等待时间也就是50秒，按道理说这个时间已经是足够业务上的任何操作了，不过这里已经超过了50s的时间还是没有获得锁，那只能说明还有针对于同个数据行的更新操作尚未完成。

冷静了10秒钟，思来想去也没有想到到底是怎么引起的，只能跟着日志提示的报错行打开代码看了，但是打开代码后看了一下感觉也没有什么问题，之前也是这么写的，这下子怎么会突然之间就报错了呢？（经典想法之以前没问题的啊，怎么现在出问题！）

那觉得代码没有问题的话，就只能从mysql的慢日志去着手了，从服务器上面把最近三天的慢日志全部拉下来，再打开我的分析工具pt-query-digest，直接把日志分析一波再说。

这看一下排名前10的慢语句中竟然有5条是报错的那个表产生的，瞬间汗颜，完全没想到他竟然承受了这么大的压力。

再进一步看日志，看排名第一耗时的，发现该语句的平均查询时间都已经99s了，占用锁的最大时间刚好是50s，但是如果算上事务提交的时间，妥妥的超过了最大的锁的最大等待时间，这就难怪它会爆出那么多锁等待超时的异常了。

这么分析下来，大概有两个点造成了异常：

1，查询时间过长；

2，更新时间过长。

拉一条更新语句来分析一下，看看它的执行计划，这一分析下来，也挺好玩的，走了index索引，也就比全表扫描好那么一丢丢了，50多万的数据预计扫描33万行，简直是博爱了，这查询时间长也说的过去了。

可这跟更新的时间有啥关系啊？这就要说到innodb的更新步骤了。

在innodb的可重复读级别下，数据更新分为以下步骤：

1. 扫描符合WHERE条件的行，将这些行的主键值记录到undo日志中。
2. 对符合WHERE条件的行加排他锁，此时其他客户端的读请求都被阻塞。
3. 更新这些行的数据值，更新完成后，提交事务或将事务回滚。

如果where条件走的不是索引列，更会导致全表被锁住，其他客户端的写请求将会被全部阻塞，如果事务超过50s没有结束的话，就会导致其他客户端的请求都超时，从而导致业务崩溃！

而恰巧地是，报错的语句的确不是用索引列作为where查询条件，所以每次的更新都是全表扫描并锁住全表，因此导致了大量的异常！

解决办法

知道了原因之后，怎么解决呢？

解决的思路有以下两种：

1，where条件列加上索引；

2，优化代码逻辑，where条件列使用主键作为查询条件；

对于第一种办法，需要字段重复的字符不多，重复字符太多会增加I/O的负担也会造成索引文件变得很大，反而会降低整体的查询速率，但是我的查询条件前面字符几乎相同，所以这个列是不可能用作索引列的，那只能用第二种方法了。

反思

使用update更新语句的时候，不能使用非索引列来作为查询条件，因为在innodb的可重复读级别下会锁住全表，如果锁占用时间过长，会导致其他客户端的写操作全部超时，造成业务崩溃，然后被提桶跑路。