mysql 事务中多条SQL的排序
场景:用户购买商品A,对应的SQL如下
- 商品A库存减1 (SQL1)
- 用户购买商品A,扣减用户金额 (SQL2)
- 插入一条交易日志 (SQL3)
这三个操作为原子操作,所以要写在一个事务中。如果有大量用户购买商品A,则商品A库存减1
为热点数据,被频繁更新。假设每条SQL的执行时间为5秒,则整个事务的执行时间为15秒,由于有大量用户购买,那么不同的执行顺序将会影响最终的执行时间,从而影响并发
把商品A库存减1 (SQL1)
放在首行(情况1)
时刻 | 事务A | 事务B | 耗时 |
---|---|---|---|
T1 | 商品A库存减1 (SQL1) | 商品A库存减1 (SQL1) 发生等待,不能执行,直到 T4 时刻 | 5s |
T2 | 用户购买商品A,扣减用户金额 (SQL2) | 5s | |
T3 | 插入一条交易日志 (SQL3) | 5s | |
T4 | 商品A库存减1 (SQL1) | 5s | |
T5 | 用户购买商品A,扣减用户金额 (SQL2) | 5s | |
T6 | 插入一条交易日志 (SQL3) | 5s |
- T1时刻,事务A执行
商品A库存减1
,给其上锁,直到事务提交(15秒以后) - 同时事务B也执行
商品A库存减1
,被阻塞,发生锁等待,这使得后面的Sql2,sql2
语句不能执行,直到15s后,事务A提交数据。 即T4时刻才开始执行。 - 事务B总共耗时30秒才完成。等待事务A的15s(T1到T3) + 自身的15s
如果把商品A库存减1 (SQL1)
放在最后,可以减少等待的时间(情况2)
时刻 | 事务A | 事务B | 耗时 |
---|---|---|---|
T1 | 用户购买商品A,扣减用户金额 (SQL2) | 用户购买商品A,扣减用户金额 (SQL2) | 5s |
T2 | 插入一条交易日志 (SQL3) | 插入一条交易日志 (SQL3) | 5s |
T3 | 商品A库存减1 (SQL1) | 商品A库存减1 (SQL1) 发生等待,不能执行,直到 T4 时刻 | 5s |
T4 | 商品A库存减1 (SQL1) | 5s |
- 扣减用户金额是针对单个用户操作,在同一时刻更新这些记录不太容易发生锁等待,所以事务A与事务B在T1时刻可以同时进行
- 插入一条交易日志 也是可以同时进行的,所以事务A与事务B在T2时刻同时进行
- 事务A T3 时刻执行完成,耗时15s,事务B 在此时被阻塞,因为要更新同一条记录,发生了锁等待,需要等待5秒
- 事务B在T4时刻,执行完成,耗时 20s(10s + 等待 5s + 5s)
当把商品A库存减1 (SQL1)
放在最后时,事务B的执行时间缩短到了20s,节省了10s,大大提高了并发度。
可以看出,锁等待时间是正在执行的事物引起锁的语句到提交的时间间隔,如果放在事务最后,那这个时间间隔会变为最少。对照上面的例子,情况1事务A从T1到T3时刻,持有锁总共15s。情况2,事务A只在T3时刻持有锁,总共5s,可见把商品A库存减1 (SQL1)
放到最后时,大大减少了时间间隔。
通过减少事务持有锁的时间,大程度的减少了事务之间的锁等待,提高了并发度。所以通常的做法是把热点更新语句放到事务的最后,这样当事务结束后,热点语句的锁可以被马上释放,减少事务锁持有的时间,其它事务等待锁释放的时间就会变短,从而使并发度得到了提高。