MySQL 悲觀鎖、樂觀鎖

悲觀鎖 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