MySQL 悲觀鎖、樂觀鎖

2022/05/25閱讀時間約 8 分鐘

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

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

如果商品庫存剩1個，明明是A先買到，這時候就已經沒庫存了，可是系統卻告訴B恭喜你搶到了，這種情況就更糟糕了，只剩下一支iPhone，卻跟兩個人(甚至更多人)都說有買到，也就是發生所謂的超賣問題。

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

悲觀鎖 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方法，還是要視系統情境而定，不能亂用。

本筆記參考:

為什麼會看到廣告

Vic Lin的沙龍

20會員

161內容數

前往沙龍

留言0

查看全部

發表第一個留言支持創作者！