# SQL 樂觀鎖悲觀鎖

###### tags: `mysql`

## 悲觀鎖 —Pessimistic Lock

**優點：**
使用 transaction 機制控制執行順序，對於sql 安全性比較高。

**缺點：**
因為透過 transaction 機制，db command 會有唯一性，導致除了讀取以外的所有db 操作都無法執行容易卡死，造成系統吞吐量下降，不良的系統體驗。

結論：一但有人執行事務操作，其餘的人無法執行transaction
## 樂觀鎖 — Optimistic Lock

**優點：**
因為沒有鎖頭，所有db command都可以執行，解決系統低吞吐的狀況，因為樂觀鎖是人為操作，會根據要執行的db加一個verion版本號當作交易的一致性，例如A與Ｂ同時要購買手機，這時A最先完成，因為version沒有被修改所以判定沒被交易過，等到A交易完成就修改verion號碼，這時B來購買發現version已經被更改過了，所以交易失敗，所以樂觀鎖的好處是，儘管你有多個db cammand近來，最終修改db只會在最後的validate階段，這也是為什麼可以解決大量吞吐問題。

**缺點：**
因為這是人為控制db所以不適合處理所有的業務邏輯，再加上用樂觀所得執行邏輯相對複雜不容易migrate，而且因為所有的db cammand都會被執行，容易造成sql command 不一致的狀況，就不符合ACID 原則了，所以sql安全性就相對比較低。

結論：所有user都可以進行db 事務操作，但只有一人會成功

### 事務衝突問題
悲觀鎖的重點在於鎖頭的釋放，如果多線程同時進入，先進入的會先拿到鎖之後另一個線程會進行等待，等A執行完畢後，Ｂ才會繼續執行，這也是為什麼悲觀所會造成低吞吐的原因。

相反樂觀鎖因為每次request都會進行sql操作，所以多線程也會執行相同的順序，最後只會在validate判斷是否修改 sql data


**來源**
https://tech.meituan.com/2018/11/15/java-lock.html

## 應用場景（高級面試題）
### 悲觀鎖：
1. 秒殺場景，避免超賣狀況

第一部查詢商品庫存
select quantity from items where id =1001
第二部如果庫存大於０，根據商品訊息產生訂單
insert into orders (item_id) values(1001);
第三部修改商品庫存
update items set quantity = quantity-num where id =1001

以上如果並發量小沒問題，反之可能存在以下風險。

| Ａ user | Ｂ user |
| -------- | -------- |
| step 1 (查詢還有100台手機) | step 1 (查詢還有100台手機) |
| -------- | -------- |
| | step ２ (生成訂單) |
| -------- | -------- |
| step ２ (生成訂單) | |
| -------- | -------- |
| | step 3 (庫存減１) |
| -------- | -------- |
| step 3 (庫存減１) | |

這時假設A跟B同時購買60台，這樣最終商品庫存會變成-20

### 解決方式

確保Ａ完成解鎖後，Ｂ在進行解鎖

第一部查詢商品庫存
select quantity from items where id =1001 for update
第二部如果庫存大於０，根據商品訊息產生訂單
insert into orders (item_id) values(1001);
第三部修改商品庫存
update items set quantity = quantity-num where id =1001

for update 是sql語法，告訴sql必須等待id = 1001的操做都完成後再繼續執行下一人的事務。

**備注很重要記得看！！**

請務必在你需要做悲觀鎖頭的欄位加上索引，否則被sql檢索過的data都會被加上悲觀鎖，因為sql查詢會是一層一層疊加，假設我今天沒有加index的話，今天我要操作id = 1001 ，因為sql會一行一行去掃描，直到找到id = 1001的資料，這時會發現我明明是做id = 1001的交易，但在他前面的id = 1000也會無法進行操作。

### 樂觀鎖：

相較於悲觀鎖是利用sql的程序去實現，樂觀鎖則是應用邏輯去實作，邏輯去比對version 異同。

第一部查詢商品庫存
select quantity from items where id =1001
第二部如果庫存大於０，根據商品訊息產生訂單
insert into orders (item_id) values(1001);
第三部修改商品庫存
update items set quantity = quantity-num ,version=version + 1  where id =1001 and version = #{version}

如果有人已經先修改過version這樣第三部的遭做你就無法進行修改了。

**備注：**
如果你的sql使用主從架構做讀寫分離，如果數據沒有即時同步，會造成update永遠都是失效的狀況，這時解決方式建議讀跟寫都在主機去做使用，從機拿來做資料備份就好。

但上面還會遇到一個問題是，如果大量操作db的話會造成多次失敗的操作，對業務邏輯上表現不太好

**解決方式 ：**

第一部查詢商品庫存
select quantity from items where id =1001
第二部如果庫存大於０，根據商品訊息產生訂單
insert into orders (item_id) values(1001);
第三部修改商品庫存
update items set quantity = quantity-num ,version=version + 1  where id =1001 and quantity-num >0

這樣會讓每次修改都成功不會有超賣狀況