大促场景下库存更新 SQL 优化

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

1. 业务服务器与数据库服务器在同机房中；
2. 网络请求耗时 3ms，语句处理时间 0.2ms，所有操作都是成功的；
3. 请求数量达到数据库瓶颈，在此基础上，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;
} else {
    rollback;
}

写法 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(...); // 插入订单表
}

if (updateCount > 0) {
    commit;
} else {
    rollback;
}

两种写法的区别就是一个先插入再更新，另一个是先更新再插入。仔细想一想，哪一种写法更优，从 TPS 的角度来考虑一下。

公布答案

第一种写法更优（先插入再更新），TPS 比第二种高得多。

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

还记得我们在最开始做的约定吗？在计算两种写法的 TPS 之前，我再给上面的代码加些注释，让你更容易理解。

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

写法 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;
}
// 行锁结束

写法 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;
}
// 行锁结束

计算两种写法的 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 语句执行时间）

当然，这个优化就要依靠你们公司的 DBA 了。

总结

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

这篇好文章是转载于：学新通技术网