MySQL 悲觀鎖、樂觀鎖

假設有一張表，儲存一個iPhone商品跟剩餘庫存量。

假設A/B兩個人同時間進來買商品，如果沒有任何防護機制，資料庫中的商品剩餘庫存量有可能會記錄錯誤，比如iPhone庫存剩10個，此時兩個人"同時"進來搶，由於是同時，所以select到的in memory庫存量都會是10，A/B都會把庫存量-1(10-1)再update資料表，這種情況下剩餘庫存量會是9而不是8，就會發生記錄錯誤的問題。

所以資料庫必須有一些機制來保證資料的正確性，來避免上述所說的問題。

悲觀鎖 Pessimistic Lock

• 使用資料庫 Transaction 機制來強迫執行順序，確保每次都只有一個事務執行。

每次下SQL時都確保同時只會有一個事務執行，也就是等A SELECT出庫存量並且-1 UPDATE庫存量之後，才釋放出Lock換B執行，這時候B SELECT拿到的庫存數量就會是9，9-1=8再去UPDATE庫存量，也就可以解決數據不正確的問題。

模擬悲觀鎖:

mysql> begin;
mysql> select stock from `product` where id = 1 for update; 
mysql> update `product` set stock = stock -1 where id = 1;
mysql> commit;

可開啟兩個mysql console視窗模擬，A視窗下了for update 會馬上跑出SELECT結果，這時候B視窗也下了這行，會發現沒有馬上執行，必須等待A視窗commit後釋放出lock，B視窗才會繼續執行，等到commit後才又把lock釋放出來。

• 注意：上述需要支援 Transaction，本文是用InnoDB儲存引擎，MyISAM 是不支援 Transaction 的，如果是用MyISAM會發現for update根本就不會lock住。
• 悲觀鎖適合寫多讀少的情境。
• 這種方法的缺點是，如果同時出現大量的讀取操作，由於Lock機制，會需要等待時間，因此可能會有效能問題。

樂觀鎖 Optimistic Lock

• 因為樂觀，所以DB不會上鎖，但是在update的時候會檢查，在這期間有沒有人更新過資料，一般會加一個version或timestamp欄位來判斷，這種作法並不是利用DB的Transaction，而是一種人為上鎖的機制。

模擬樂觀鎖: 新增一個version欄位

mysql> SELECT stock, version FROM `product` where id = 1;
mysql> update `product` set stock = stock-1, version = version + 1 where id = 1 and version = 0;

兩個視窗同時都會拿到stock=10, version=0的資料，A視窗update資料的時候卡上version=0條件，會更新成功。但這時候B視窗一樣會卡上version=0的條件來更新資料，會發現根本找不到version=0的資料，因為早就被A改成version=1了，也就更新失敗了，這時候應該要通知B使用者購買失敗。

試想如果沒有多version這個欄位當作where條件去更新的話，這筆row data兩個視窗都會更新成功，也就是沒有判斷這段期間有沒有別人更新過，如果庫存剩一個的話，就會發生賣超的問題，剩餘庫存量還記錄為-1。

• 樂觀鎖適合讀多寫少的情境。
• 這種方法不適合用於寫多讀少的情況下，因為同時很多併發發生的時候，會有很多update失敗需要重試的狀況發生，消耗的資源開銷會比悲觀鎖更多。

Laravel中使用悲觀鎖(上述的模擬悲觀鎖):

try {
　　DB::beginTransaction();
　　$data = Product::where('id', 1)->lockForUpdate()->first();
　　if($data->stock <= 0){  //庫存不足
　　　　return "庫存不足";
　　}

　　$data->decrement('stock', 1);
　　DB::commit();
}
catch (Exception $e) {
　　DB::rollback();
}

transaction簡潔寫法:

$result = DB::transaction(function () {
　　$data = Product::where('id', 1)->lockForUpdate()->first();
　　if($data->stock <= 0){  //庫存不足
　　　　return false;
　　}

　　$data->decrement('stock', 1);
　　return true;
});
if(!$result){
　　return '庫存不足';
}

• ->lockForUpdate()就是SQL中FOR UPDATE的意思。
• ->lockForUpdate()要搭配transaction語法一起使用，不然沒有效果。

LOCK IN SHARE MODE

這種鎖法對應到Laravel的->sharedLock()。

比較一下跟FOR UPDATE鎖法差別:

• LOCK IN SHARE MODE: 不會阻止其它transaction讀取同一行。
• FOR UPDATE: 會阻止其它transaction讀取同一行(一般沒加鎖的select還是可以讀取，不影響)。
• 共同之處是兩者都會阻止同一行資料被其它transaction update。

所以如果是超賣的情境，不適合用LOCK IN SHARE MODE，因此使用哪一種lock方法，還是要視系統情境而定，不能亂用。

本筆記參考:

1. https://www.codeprj.com/zh/blog/bb96e01.html
2. https://www.gushiciku.cn/pl/gKzx/zh-tw
3. https://ithelp.ithome.com.tw/articles/10271229
4. https://www.readfog.com/a/1634624578764509184
5. https://medium.com/dean-lin/%E7%9C%9F%E6%AD%A3%E7%90%86%E8%A7%A3%E8%B3%87%E6%96%99%E5%BA%AB%E7%9A%84%E6%82%B2%E8%A7%80%E9%8E%96-vs-%E6%A8%82%E8%A7%80%E9%8E%96-2cabb858726d
6. https://www.w3help.cc/a/202108/569509.html
7. https://twgreatdaily.com/uTc0AHEBnkjnB-0zi2R_.html
8. https://blog.51cto.com/u_9443450/2394262
9. https://ithelp.ithome.com.tw/articles/10267450
10. https://kknews.cc/zh-tw/news/z9aqnva.html
11. https://blog.csdn.net/weixin_39947314/article/details/113720708
12. https://learnku.com/articles/36846
13. http://www.yangxg.com/blog/id/1530105560
14. https://learnku.com/articles/57959
15. https://www.sunzhongwei.com/using-laravel-sharedlock-and-lockforupdate-for-table-row-locks