本篇文章討論的大促場景，指雙 11、618 期間，系統(tǒng)的行為是要盡可能多地賣出商品，盡可能多地收訂單，又不能超過庫存。在這種高并發(fā)、大流量場景下，整個系統(tǒng)的瓶頸點必然在數(shù)據(jù)庫上，本篇文章就庫存更新這一場景下討論如何優(yōu)化事務(wù) SQL。

在文章開始之前，我們做出如下約定：

1. 業(yè)務(wù)服務(wù)器與數(shù)據(jù)庫服務(wù)器在同機房中；
2. 網(wǎng)絡(luò)請求耗時 3ms，語句處理時間 0.2ms，所有操作都是成功的；
3. 請求數(shù)量達到數(shù)據(jù)庫瓶頸，在此基礎(chǔ)上，TPS = 1000ms / lockTime（非鎖時間都可并發(fā)操作）。

兩種事務(wù)代碼，誰更優(yōu)？

我們售賣一件商品的流程是什么呢？首先要先查詢一下商品是否還有庫存？如果有庫存，我們就創(chuàng)建一個訂單，商品庫存減去 1（為了方便分析，我們只討論這種比較簡單的情況）。并且，我們還知道上述操作應(yīng)當(dāng)封裝在一個事務(wù)中，如果其中一步失敗了，就應(yīng)當(dāng)進行回滾。

好的，基于上述的描述，我們有如下兩種事務(wù)代碼的編寫方式。

寫法 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;
}

兩種寫法的區(qū)別就是一個先插入再更新，另一個是先更新再插入。仔細想一想，哪一種寫法更優(yōu)，從 TPS 的角度來考慮一下。

公布答案

第一種寫法更優(yōu)（先插入再更新），TPS 比第二種高得多。

為什么呢？首先，我們要知道上面的每一條數(shù)據(jù)庫操作語句，包括最后的 commit 或者 rollback 都要由業(yè)務(wù)服務(wù)向數(shù)據(jù)庫發(fā)送網(wǎng)絡(luò)請求，并且要等待數(shù)據(jù)庫返回語句執(zhí)行結(jié)果（同步）。

還記得我們在最開始做的約定嗎？在計算兩種寫法的 TPS 之前，我再給上面的代碼加些注釋，讓你更容易理解。

1. 業(yè)務(wù)服務(wù)器與數(shù)據(jù)庫服務(wù)器在同機房中；
2. 網(wǎng)絡(luò)請求耗時 3ms，語句處理時間 0.2ms，所有操作都是成功的；
3. 請求數(shù)量達到數(shù)據(jù)庫瓶頸，則 TPS = 1000ms / lockTime（非鎖時間都可并發(fā)操作）。

寫法 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; // 網(wǎng)絡(luò)請求 1
} else {
    rollback;
}
// 行鎖結(jié)束

寫法 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(...); // 網(wǎng)絡(luò)請求 1
}
if (updateCount > 0) {
    commit; // 網(wǎng)絡(luò)請求 2
} else {
    rollback;
}
// 行鎖結(jié)束

計算兩種寫法的 TPS

在計算之前，我們還要再回顧一下關(guān)于數(shù)據(jù)庫中鎖的基礎(chǔ)知識。

1、update 命令會施加一個 X 型記錄鎖，X 型記錄鎖是寫寫互斥的。如果 A 事務(wù)對 goods 表中 id = 1 的記錄行加了記錄鎖，B 事務(wù)想要對這行記錄加記錄鎖就會被阻塞。

2、insert 命令會施加一個插入意向鎖，但插入意向鎖是互相兼容的。如果 A 事務(wù)向 order 表 insert 一條記錄，不會影響 B 事務(wù) insert 一條記錄。

3、記錄鎖要等到事務(wù)提交之后才會釋放！

好的，基于最開始的約定，代碼的注釋，以及基礎(chǔ)知識，我們可以來計算了。

寫法 1 的 TPS

commit 網(wǎng)絡(luò)請求 1 次，commit 語句執(zhí)行一次，我們在這里可以先忽略語句執(zhí)行耗時。

TPS = 1000ms / 3ms = 333.33

寫法 2 的 TPS

insert、commit 共兩次網(wǎng)絡(luò)請求，兩條語句執(zhí)行，我們也忽略語句執(zhí)行耗時。

TPS = 1000ms / 6ms = 166.67

我們可以看到兩者的 TPS 差了 2 倍。試想一下，如果事務(wù)中有更多的數(shù)據(jù)庫操作，寫法 2 的 TPS 會進一步降低。

繼續(xù)優(yōu)化

寫法 1 是否還有進一步優(yōu)化的空間呢？update 執(zhí)行成功與否數(shù)據(jù)庫是知道的，如果省去 commit 這個網(wǎng)絡(luò)請求，那么 TPS 是多少呢？

TPS = 1000ms / 0.2ms = 5000（0.2ms 是 commit 語句執(zhí)行時間）

當(dāng)然，這個優(yōu)化就要依靠你們公司的 DBA 了。

總結(jié)

當(dāng)我們在編寫一個事務(wù)的時候，加行鎖的操作應(yīng)在不影響業(yè)務(wù)的情況下，盡可能地靠近 commit 語句，這樣單行記錄的行鎖時間才會更短，TPS 會更高。

?轉(zhuǎn)自https://juejin.cn/post/7266302333634215976