---
tags: 鐵人賽
---

# Day 20, 如何在MySQL避免競爭條件

MySQL是一個很常見的關聯式資料庫，在任何牽扯到交易和金錢的場景都很容易看到MySQL的身影。原因在於MySQL等關聯式資料在執行操作時具有ACID的保證。

關於ACID的內容，在鐵人賽剛開始時的[分散式交易](https://ithelp.ithome.com.tw/articles/10287751)就已經介紹過了，因此這邊就不贅述。

但在這篇文章中有一個必須要強調的重點，也就是ACID中的Isolation在MySQL等關聯式資料庫上有分為四個等級。

- 讀未提交(Read Uncommitted)
- 讀已提交(Read Committed)
- 可重複讀(Repeatable Read)
- 可序列化(Serializable)

這四個等級的具體內涵就不細說，但要知道的是，想完全避免競爭條件(racing condition)，那只有可序列化才能辦到。但可重複讀是MySQL的預設隔離等級，這是因為可序列化會強迫所有的操作進行排隊，以此來避免競爭，可想而知的是，效能悲劇。

但可重複讀會產生許多實務上使用關聯式資料庫的陷阱，若是沒有仔細關注，很容易就產生嚴重問題，輕則分數算錯，重則賠大錢。因此，這篇文章會解釋三種在可重複讀等級下避免競爭條件的手段。

## 使用情境

讓我們用一個最常見的案例進行說明。

有一張資料表稱為`bank`。

| name | money |
| -------- | -------- |
| A | 100 |
| B | 100 |

假設，A和B兩個人都有100塊，接著A想要轉帳70塊給B兩次。

## 單純設計：確認餘額完直接轉帳

首先，我們試著採取一個單純的作法，確認餘額完直接轉帳，讓我們將這個單純設計寫成偽碼：

A_owned = `select money from bank where name = A`
B_owned = `select money from bank where name = B`
if A_owned >= 70:
    `update bank set money = A_owned - 70 where name = A`
    `update bank set money = B_owned + 70 where name = B`

這是最單純的設計，非常直覺也容易實作。但是，這有兩個問題：
1. 在更新後餘額錯誤
2. 轉帳兩次後，餘額變成負數

讓我們分別來看這兩個問題。

第一個問題的根源是，當A「同時」發起兩次轉帳，兩次轉帳都會看到`A_owned`是100。讓我們用時序圖來解釋。

A1 -> DB: select money from bank where name = A
A2 -> DB: select money from bank where name = A

DB --> A1: 100
DB --> A2: 100

A1 -> DB: update bank set money = 30 where name = A
Note over DB: 30 
A2 -> DB: update bank set money = 30 where name = A
Note over DB: 30

最終，我們會看到到A轉帳兩次70塊後居然餘額還有30塊，這就是經典的競爭條件。

那該如何解決？

## 競爭條件的解法

在MySQL上解決競爭條件有三種常見的作法：
1. 原子更新
2. 交易加鎖
3. 版本控制

### 原子更新

這使用情境的問題在於使用了不穩定的資料。

為了避免這種事發生，我們不應該使用之前拿出來的結果作為更新基準，反而應該使用當下的值。怎麼這麼神奇？

其實並不困難，只需要將原來的指令：
> update bank set money = A_owned - 70 where name = A

改成原子更新即可。
> update bank set money = money - 70 where name = A

仔細看上面的指令，我們使用`money`來更新`money`而不是我們已經拿出來的`A_owned`。這就是原子更新。

### 交易(Transaction)加鎖

在介紹第二種方法前，我必須先說，許多人對於「交易」都有個誤會。交易並不是無所不能，而且也無法避免競爭條件。把交易當成萬靈丹是個錯誤認知。

交易要能無所不能的前提是隔離等級是可序列化，但我們已經知道預設等級是可重複讀了，因此交易並沒有這麼厲害。

尤其是，在MySQL最常使用的儲存引擎(InnoDB)實作中，可重複讀無法解決更新丟失(Lost Updates)和寫入偏斜(Write Skew)。因此，競爭條件依然會產生。

順帶一提，Postgres的可重複讀是能解決更新丟失的，但同樣無法解決寫入偏斜。

如果這樣說，那交易到底能做什麼？

交易是為了確保整批資料庫操作的完整性，在發生問題時我們可以整批回滾，而不會殘留部分結果。

有了這樣的認知後，讓我們來看看交易加鎖該怎麼做。

start transaction
A_owned = `select money from bank where name = A for update`
if A_owned >= 70:
    `update bank set money = A_owned - 70 where name = A`
commit

藉由使用`for update`，我們可以在選取的行上加一個互斥鎖，如果有其他交易也需要這些行，那就必須要等鎖釋放掉。整個流程如下。

A1 -> DB: start transaction
A1 -> DB: select money from bank where name = A for update
A2 -> DB: start transaction
A2 -> DB: select money from bank where name = A for update

DB --> A1: 100
A1 -> DB: update bank set money = 30 where name = A
Note over DB: 30 
A1 -> DB: commit


DB --> A2: 30
A2 -> DB: commit

從上圖可以知道，`A2`必須要等`A1`的交易結束才會拿到結果，而`A1`已經將餘額更新成30了，所以`A2`不會進行第二次轉帳，而是什麼也不做就結束。

### 版本控制

這個解決方案相對複雜且難懂，事實上，這個解決方案是藉由資料庫引擎的MVCC機制來達成交易同步的。

版本控制有兩種常見的形式，讓我們先從第一種比較複雜的講起。

首先，我們先修改資料表，並且加入一個新的欄位，version，來記錄每一行的修改次數。

| name | money | version |
| -------- | -------- | -------- |
| A | 100 | 1 |
| B | 100 | 1 |

在更新資料前，我們必須取得原始的版本，接著連同版本一起進行更新。

A_owned, old_ver = `select money, version from bank where name = A`
if A_owned >= 70:
    `update bank set money = 30, version = version + 1 where name = A and version = old_ver`

在更新資料時也必須同時比對版本，只有與預期一致才可以進行更新，否則操作就會失敗。讓我們繼續以時序圖來解釋。

A1 -> DB: select money, version from bank where name = A
DB --> A1: 100, 1

A2 -> DB: select money, version from bank where name = A
DB --> A2: 100, 1

A1 -> DB: update bank set money = 30, version = version + 1 where name = A and version = 1
DB --> A1: 1
note over DB: 30, 2

A2 -> DB: update bank set money = 30, version = version + 1 where name = A and version = 1
DB --> A2: 0

當`A1`更新完後，資料庫回傳1表示有一行被修改了，也就是說更新成功。但`A2`拿到0，換句話說，沒有行被修改，也意味著失敗。

第二種作法會再單純一點，也是運用版本控制的想法，但不需要新增加一個欄位。

只需要將原本的指令：
> update bank set money = A_owned - 70 where name = A
做些修改，套用版本的概念即可。
> update bank set money = A_owned - 70 where name = A and A_owned = 100

我們把`A_owned`當成版本的一部分，當`A_owned`為100時，才進行更新，反之就失敗。這樣的做法與`version`有異曲同工。

### 總結一下

我們有三種解法可以解決競爭條件了，但是版本控制有點難懂，而交易加鎖的效能則是最差的。交易加鎖其實就是強迫交易進入可序列化，而這樣的同步機制正如前述，效能很差。

因此，原子交易是比較好的方案，並且能套用在大部分的使用情境。

雖然，我們已經可以解決更新丟失的問題，但還有一個問題依然沒有解決，那就是餘額變成負數。

透過原子更新，我們不再會有錯誤餘額，兩次轉帳都能夠成功，但都能成功也意味著`100 - 70 - 70 = -40`，餘額是負數這是不被允許的。

## 最終解法

我們不僅要用原子更新解決更新丟失，還必須確保餘額不會變成負數，因此還需要動一些手腳。

我的建議是，那些不該為負數的欄位就應該在定義欄位時加入條件限制，例如`UNSIGNED`。此外，我們需要加入交易來保證A轉出兩次和B收到兩次都是正確的。

最後，整個解法如下。

`start transaction`
A_owned = `select money from bank where name = A`
if A_owned >= 70:
    `update bank set money = money - 70 where name = A`
    `update bank set money = money + 70 where name = B`
`commit`

將兩個更新都改為原子更新，並且用上交易來確保整個操作完整。就算`if A_owned >= 70`無法阻止`A2`的操作，但`A2`會因為`A1`違反正整數條件而被迫失敗，因為同屬一個交易。

最終，A只會被扣一次款且B也只會收到一筆。而系統能夠在兼顧效能和正確性的情況下，依然保有單純的實作。