工作记录-------双 11场景下库存更新 SQL 优化

背景：双 11 期间，系统的行为是要尽可能多地卖出商品，尽可能多地收订单，又不能超过库存。
在这种高并发、大流量场景下，整个系统的瓶颈点必然在数据库上，本篇文章就库存更新这一场景下讨论如何优化事务 SQL。

在文章开始之前，我们做出如下约定：

• 业务服务器与数据库服务器在同机房中；
• 网络请求耗时 3ms，语句处理时间 0.2ms，所有操作都是成功的；
• 请求数量达到数据库瓶颈，在此基础上，TPS（每秒处理的消息数) = 1000ms / lockTime（非锁时间都可并发操作）。

两种事务代码，谁更优？

我们售卖一件商品的流程是什么呢？首先要先查询一下商品是否还有库存？如果有库存，我们就创建一个订单，商品库存减去 1（为了方便分析，我们只讨论这种比较简单的情况）。并且，我们还知道上述操作应当封装在一个事务中，如果其中一步失败了，就应当进行回滚。

基于上述的描述，我们有如下两种事务代码的编写方式。
写法 1

begin;

select stock from goods where id = 1;

if (stock > 0) {
    insert into order values(...); 
    update goods set stock = stock - 1 where id = 1 and stock > 0; // 行锁开始
}

if (updateCount > 0) {
    commit; // 网络请求 1
} else {
    rollback;
}
// 行锁结束


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

写法 2

begin;

select stock from goods where id = 1;

if (stock > 0) {
    update goods set stock = stock - 1 where id = 1 and stock > 0; // 行锁开始
    insert into order values(...); // 网络请求 1
}

if (updateCount > 0) {
    commit; // 网络请求 2
} else {
    rollback;
}
// 行锁结束


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

两种写法的区别就是一个先插入再更新，另一个是先更新再插入；

从 TPS 的角度来考虑的话，第一种写法更优（先插入再更新），TPS 比第二种高得多

为什么呢？首先，我们要知道上面的每一条数据库操作语句，包括最后的 commit 或者 rollback 都要由业务服务向数据库发送网络请求，并且要等待数据库返回语句执行结果（同步）

计算两种写法的 TPS
在计算之前，我们还要再回顾一下关于数据库中锁的基础知识。
1、update 命令会施加一个 X 型记录锁，X 型记录锁是写写互斥的。如果 A 事务对 goods 表中 id = 1 的记录行加了记录锁，B 事务想要对这行记录加记录锁就会被阻塞。
2、insert 命令会施加一个插入意向锁，但插入意向锁是互相兼容的。如果 A 事务向 order 表 insert 一条记录，不会影响 B 事务 insert 一条记录。
3、记录锁要等到事务提交之后才会释放！
好的，基于最开始的约定，代码的注释，以及基础知识，我们可以来计算了。

写法 1 的 TPS：
commit 网络请求 1 次，commit 语句执行一次，我们在这里可以先忽略语句执行耗时。

TPS = 1000ms / 3ms = 333.33

写法 2 的 TPS
insert、commit 共两次网络请求，两条语句执行，我们也忽略语句执行耗时。
TPS = 1000ms / 6ms = 166.67

我们可以看到两者的 TPS 差了 2 倍。试想一下，如果事务中有更多的数据库操作，写法 2 的 TPS 会进一步降低。

继续优化

写法 1 是否还有进一步优化的空间呢？update 执行成功与否数据库是知道的，如果省去 commit 这个网络请求，那么 TPS 是多少呢？

TPS = 1000ms / 0.2ms = 5000（0.2ms 是 commit 语句执行时间）

总结

当我们在编写一个事务的时候，加行锁的操作应在不影响业务的情况下，尽可能地靠近 commit 语句，这样单行记录的行锁时间才会更短，TPS 会更高。