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

在文章開(kāi)始之前，我們做出如下約定：

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

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

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

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

寫(xiě)法 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; // 更新庫(kù)存
}
if (updateCount > 0) {
    commit;
} else {
    rollback;
}

寫(xiě)法 2

begin;
select stock from goods where id = 1;
if (stock > 0) {
    update goods set stock = stock - 1 where id = 1 and stock > 0; // 更新庫(kù)存
    insert into order values(...); // 插入訂單表
}
if (updateCount > 0) {
    commit;
} else {
    rollback;
}

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

公布答案

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

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

還記得我們?cè)谧铋_(kāi)始做的約定嗎？在計(jì)算兩種寫(xiě)法的 TPS 之前，我再給上面的代碼加些注釋?zhuān)屇愀菀桌斫狻?/p>

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

寫(xiě)法 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; // 行鎖開(kāi)始
}
if (updateCount > 0) {
    commit; // 網(wǎng)絡(luò)請(qǐng)求 1
} else {
    rollback;
}
// 行鎖結(jié)束

寫(xiě)法 2 注釋版

begin;
select stock from goods where id = 1;
if (stock > 0) {
    update goods set stock = stock - 1 where id = 1 and stock > 0; // 行鎖開(kāi)始
    insert into order values(...); // 網(wǎng)絡(luò)請(qǐng)求 1
}
if (updateCount > 0) {
    commit; // 網(wǎng)絡(luò)請(qǐng)求 2
} else {
    rollback;
}
// 行鎖結(jié)束

計(jì)算兩種寫(xiě)法的 TPS

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

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

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

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

好的，基于最開(kāi)始的約定，代碼的注釋?zhuān)约盎A(chǔ)知識(shí)，我們可以來(lái)計(jì)算了。

寫(xiě)法 1 的 TPS

commit 網(wǎng)絡(luò)請(qǐng)求 1 次，commit 語(yǔ)句執(zhí)行一次，我們?cè)谶@里可以先忽略語(yǔ)句執(zhí)行耗時(shí)。

TPS = 1000ms / 3ms = 333.33

寫(xiě)法 2 的 TPS

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

TPS = 1000ms / 6ms = 166.67

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

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

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

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

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

總結(jié)

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

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