业务优化案例一则
本文记录解决业务层面sql被kill的案例。
一 背景
开发同学反馈一个业务功能偶尔会报如下错误:
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: UPDATE xxx SET stock_qty = stock_qty - ? WHERE id = ? and stock_qty >= ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLQueryInterruptedException: Query execution was interrupted看到关键字Query execution was interrupted,我知道是sql 在执行过程中因为执行时间超过阈值被kill。 跟开发同学沟通了解到业务场景如下:
用户会设置发送一定量的短信,该系统没法送一条短信就要扣减一次。发送短信是并发的，所以扣减也是并发的。类似库存扣减的动作。
接下来我们看看如何解决这个问题。
二 分析
2.1 猜测
在有赞的数据库运维体系里面，每个实例会部署相应的sql-killer工具，实时处理耗时比较长的查询。有哪些因为会导致sql变慢呢？
sql 语句本身索引不合理，导致执行缓慢。
使用合理的索引但是获取的数据量非常多，依然会慢查。
网络丢包重传导致sql变慢,被kill。
并发比较高的场景，请求排队处理，等待时间长。
扣减对应的sql 如下:
UPDATE xxx SET stock_qty = stock_qty - ? WHERE id = ? and stock_qty >= ?update 语句使用主键索引，排除原因1,2。然后检查对应出现问题时间的网络丢包情况，发现无任何网络丢包重传,排除原因3。结合开发反馈扣减的动作是并发处理，于是我打算从获取锁时间长着手。
2.2 分析&优化
检查对应时间点的数据库监控,出现问题时刻数据库监控获取行锁的时间有比较大的增长，获取行锁的次数有抖动，与发生报错的时间吻合。
详细询问开发的业务的代码实现，扣减逻辑大概的流程是这样的:
开启事务。
查询库存并做库存是否满足的判断，select 语句。
扣减短信库存， update 语句。耗时约2ms
记录发送短信的日志流水 insert 语句。耗时约2ms
提交或者回滚。
使用数据模型推导该业务模型下update持有锁的时间:
update 耗时1ms+网络耗时1.5ms+insert耗时1ms+网络耗时1.5ms约等于5ms
根据MySQL的2阶段锁原则,事务从步骤3开始持有锁，一直到步骤5，事务结束才会释放锁，在次期间其他会话会一直等待锁释放。如果我们更换扣减语句执行顺序:
开启事务。
查询库存并做库存是否满足的判断,select 语句。
记录发送短信的日志流水,insert 语句。
扣减短信库存,update 语句。
提交或者回滚。
再次使用数据模型推导该业务模型下update持有锁的时间:
update 耗时1ms+网络耗时1.5ms约等于2.5ms
单个事务持有锁的时间是优化之前的1/2。(其实大致的计算方式是这样的，并发越高，排队获取锁等待的时间就越长，最终一个sql等待超过阈值被kill)
2.4 验证
和开发同学沟通，针对调整事务语句执行顺序前后对业务进行压测。调整之后innodb获取行锁的时间明显减少。
三 相关知识
MySQL的加锁原则，就是2PL (二阶段锁):Two-Phase Locking。就是说锁操作分为两个阶段:加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。下面，仍旧以MySQL为例，来简单看看2PL在MySQL中的实现。
2PL就是将加锁/解锁分为两个完全不相交的阶段。加锁阶段：只加锁，不放锁。解锁阶段：只放锁，不加锁。 (登博的MySQL 加锁处理分析)
四 总结
本文的优化是基于二阶段锁提交的基础知识，通过调整sql执行顺序减少事务持有锁的时间，进而避免sql 超时。但是还是有一个遗留点，如果发生短信的扣减库存再增加怎么办？ 这个其实和秒杀类似了，需要排队处理请求。