鎖表 Table Lock

定義

在要更新資料表的資料時，MySQL會將表資料鎖定無法讀取，直到資料異動完畢，MyISAM 預設支援 Table-level lock，而 InnoDB 預設支援 Row-level lock

Table-level lock

資料表資料異動時，將「整個資料表（Table）」都鎖定住無法讀取

Row-level lock

資料表資料異動時，將「要更新的資料列（row）」都鎖定住無法讀取

注意事項

在使用 Row-level lock 時必需要 明確指定要異動資料的主鍵（Primary Key），否則將會改用 Table-level lock 去做資料表的異動

範例

假設有 user 資料表，裡面有 id 與 name 的欄位，id 是主鍵

備註

FOR UPDATE 僅適用於 InnoDB，且必須在交易區塊(BEGIN/COMMIT)中才能生效。

鎖定方式

Exclusive Lock (Write Lock, X-Lock)

• 拿到 Exclusive Lock 的 Transaction 可以寫入
• 其他 Transaction 無法讀取、無法寫入
• 資料比需不處於 Exclusive Lock 及 Shared Lock 狀態，才可以被 Exclusive Lock

SELECT * FROM `users`
WHERE `id` = '3' LOCK IN SHARE MODE

Shared Lock (Read Lock, S-Lock)

• 拿到 Exclusive Lock 的 Transaction 可以讀取
• 其他 Transaction 可以 Share Lock 讀取
• 無法被其他 Transaction Exclusive Lock
• 沒有任何 Transaction 可以寫入
• 當資料上 只有 1 個 Shared Lock，而且這個 Shared Lock 的擁有者 是 Transaction 自己本身 時，Transaction 就可以直接將這個 Shared Lock 升級變成 Exclusive Lock，直接對資料做寫入。

SELECT * FROM `users`
WHERE `id` = '3' FOR UPDATE

Range Lock 範圍鎖

對一個 range 內的資料做 Lock，主要用來避免 Phantom 幻影現象。例如如果執行下面的 MySQL 指令

SELECT * FROM `user`
WHERE height >= 170 FOR UPDATE;

• 在 height 從 170 到無限大的 range 也會被加上 Exclusive Lock
• 任何 height 介於這個 Range Lock 範圍內的資料都 不能讀取 也 不能寫入。

Range Lock 在 InnoDB Isolation Level 差異

Serializable

• 其他 Transaction 不能讀取也不能寫入 height 介於 Range Lock 內的資料

Repeatable Read (RR)

• InnoDB 在 Repeatable Read Isolation 是採用 Snapshot Isolation，讀取時都是讀取 Snapshot 內的資料，所以讀取 Range Lock 範圍內的資料是可以成功的 (讀 Snapshot 內的資料)
• 仍然 不能寫入 任何 height 介於 Range Lock 範圍內的資料。

幻影現象 Phenomena (Race Conditions)

當 Transaction 沒有獲取 範圍鎖 (Range lock) 的情況下執行 SELECT … WHERE 操作可能會發生「幻影讀 phantom read」。

Dirty Read 讀取到錯誤的資料

如果一個 Transaction 還沒有 commit，但是你卻讀得到已經更新的結果，這個情形叫做 Dirty Read。

• Transaction B 讀到 Transaction A 才剛剛更新，但是還沒有 Commit 的資料
• Transaction A 可能 再次更新資料 或是 Rollback
• 所以 Transaction B 就拿到髒掉的資料

Read Committed 或是更高的 Isolation 可以避免這個現象。

Non-repeatable Read (Read Skew) 同一交易不可重複的讀取

如果將資料庫 Isolation Level 設定為 Read Committed

如果你在同一個 transaction 裡面連續使用相同的 Query 讀取了多次資料，但是相同的 Query 卻回傳了不同的結果，這個現象稱為 Non-repeatable reads。

• Transaction B 第一次讀取時只能拿到已經被 Commit 的資料
• Transaction A 更新，但是還沒 Commit 的資料，所以拿不到最新的 Commit 資料
• Transaction B 第二次讀取時，拿到的就是 Transaction A Commit 之後的資料。

在同一個 Transaction 中，重複讀取時會拿到不一致的資料就叫做 Non-repeatable Read。

Repeatable Read 或是更高的 Isolation 可以避免這個現象。

Lost Update 失去更新的資料

兩個 Transaction 同時進行賣出 Item A 的操作

• Transaction A 直接把 Transaction A 的更新覆蓋掉了，造成 Transaction A 的更新遺失，這就是 Lost Update。

Serializable Isolation 可以避免這個現象

Repeatable Read Isolation 依照每個資料庫實作的不同而有不同的行為，有的無法避免 Lost Update (ex: MySQL InnoDB)，有的則可以 (ex: PostgreSQL)。

Phantom Read 幻影讀

同一個 transaction 連續兩次讀取時，讀取出來的 筆數跟上次不同，這個情況稱為 Phantom Read 幻影讀。

• Transaction A 一開始讀取到是 Transaction B 尚未更新的資料
• Transaction A 在自己的交易階段後讀取到的資料是 Transaction B Commit 後的資料，前後資料發生不一致現象

Serializable Isolation 可以避免這個現象。

Repeatable Read Isolation 依照每個資料庫實作的不同而有不同的行為。例如 PostgreSQL 可以完全避免 Phantom，但是 MySQL InnoDB 只能避免 Phantom Read

Write Skew

現有商品庫存是 3，兩個 Transaction 分別 同時賣出 2 個 Item A，為了確保有庫存，在賣出前會先讀取現在庫存的數字，確認數字大於 2

• 兩個 Transaction 都滿足大於 2 這個『前提』，所以繼續進行更新庫存的操作
• 最後卻導致庫存數量等於 -1，違反庫存不能小於 0 的前提，這就是 Write Skew。

所以 Write Skew 現象只有在 Serializable Isolation

Isolation Levels 資料隔離等級

MySQL

PostgreSQL

1. Read Uncommitted Isolation

• 允許讀取還沒有被 Commit 的資料

2. Read Committed Isolation

• 只允許讀取已經被 Commit 的資料

3. Repeatable Read Isolation

• 每次 Transaction 要讀取特定欄位的資料時，只要 query 條件相同，讀取到的資料就會相同
• 只要能夠避免 Dirty Read 和 Non-repeatable Read 現象就可以被稱為 Repeatable Read Isolation。

有的 Repeatable Read Isolation 還可以避免 Lost Update 或 Phantom 現象。

實作方式

對讀取過的資料都加上 Shared Lock，一直到 Transaction 結束

期間都不允許其他 Transaction 做寫入更新。

但是因為沒有做 Range Lock，通常都無法避免 Phantom 現象。

Snapshot Isolation

• 在每個 Transaction 第一次讀取資料時，對資料庫做一個概念上像是 Snapshot 的紀錄。
• Transaction 之後就都 只能看到這個 Snapshot 的內容
• 無法讀取到其他 Transaction 所做的更動

4. Serializable Isolation

• 會跟一次只讓一個 Transaction 照順序 (serially) 進行讀寫所得到的結果完全一致。
• Serializable 通常被認為是最嚴格的 Isolation Level，可以避免上述全部五種現象，但是因為 必須犧牲一些 Concurrency，效能較差。

MySQL Repeatable Read Isolation 情境討論

Repeatable Read Isolation 的 Phantom Read 幻影讀

任務是在每週的最後一天，當下分數最高的前三名玩家增加 credit，前三名玩家的 credit 都各增加 1 分

目前玩家分數狀況

Transaction A 負責處理計算前三名玩家，Transaction B 在過程中新增了一筆玩家分數資料

在步驟 6 的時候 Transaction A 還是沒有讀取到最新的玩家 Frank 的分數，因為 Repeatable Read Isolation 在 START TRANSACTION 後讀取的是 Snapshot 的資料

所以 Transaction A 會找出 Snapshot 中前三名的玩家

然後在這些玩家中，找到最低分的是 740， Transaction A 會對分書高於 740 的玩家 credit 都個加 1

最後有 4 位玩家的 credit 都被 + 1，比原本系統預計送出的 credit 還多。這種現象屬於 Write Skew，在這個例子中是因為 Phamtom 而導致的 Write Skew。

Repeatable Read Isolation 的實作都是採用 Snapshot Isolation，所以 MySQL Repeatable Read Isolation 無法避免 Phantom

如果瞭解 Snapshot Isolation 的機制，就可以知道 Snapshot Isolation 在 read-only Transaction 中才可以避免 Phantom

Snapshot Isolation 執行方式

• 會在每個 Transaction 第一次 SELECT 資料的時候，記錄下一個概念上像是時間標記的資料
• 每個 Transaction 在執行完第一次 SELECT 之後，Transaction 就只允許讀取:
  • 在這個 時間標記之前 就已經 commit 的資料
  • Transaction 自己本身 對資料庫所做的更動

在 InnoDB 的實作中，這個規則只限於 SELECT (DQL: Data Query Language) 指令，其他像是 INSERT、UPDATE 和 DELETE 等 DML (Data Manipulation Language) 指令，看到的就 不是 Snapshot

這也就是為什麼 Transaction A 在執行 UPDATE 時可以看到玩家 Frank，並且幫他增加 credit。而且在執行完 UPDATE 後，重新 SELECT 一次時，玩家 Frank 也出現在列表中 (Transaction 可以看到自己所做的更新)。

同樣是採用 Snapshot Isolation 實作 RR Isolation 的 PostgreSQL

它的 Snapshot 就不只在 SELECT 指令有效，其他像 INSERT、UPDATE 和 DELETE 等 DML 指令上也都有效。所以上面例子中的 Phantom 現象並不會在 PostgreSQL 發生。

但如果情境規則實際上要更新 Frank 分數 999 的使用者，則就無法更新到

避免 Phantom 跟 Write Skew

1. 更新指令限定更新範圍

UPDATE gamer SET credit = credit + 1
WHERE name IN ("Alice", "Bob", "Carol");

2. 使用 MySQL 的 Share Lock 或是 Exclusive Lock

Block 住其它想更改資料的 Transaction

SELECT * FROM gamer LOCK IN SHARE MODE;

UPDATE gamer SET credit = credit + 1
WHERE score >= 740;

COMMIT;

3. 將 MySQL 設定為 Serialzable Isolation

MySQL 就會自動為所有的 SELECT 都加上 LOCK IN SHARE MODE。

但這會影響效能很大，如果沒有為欄位做好 Index ，就有可能會造成 Full-Table-Lock，應該盡量避免使用。

Repeatable Read Isolation 的 Lost Update 失去更新的資料

總共有 10 個商品，Transaction A 賣出 4 個，Transaction B 賣出 1 個

商品交易過程會像這樣

最後總共賣出 5 個商品，庫存應該剩 5 個，但庫存卻因為 Transaction B 的更新變成庫存 9 個，這就是 Lost Update 現象。

Lost Update 現象通常都發生在像這種對資料庫做 read-modify-write 的操作。

有的資料庫會實作 Lost Update 的自動偵測機制來避免這種錯誤，像是 PostgreSQL 的 RR Isolation。

但是 MySQL 則沒有，所以 Lost Update 現象是有可能在 MySQL 的 RR Isolation 發生的。

避免 Lost Update

1. 使用 Atomic Operations

對原本的欄位數字做增減，不要直接設定程式計算的數字

UPDATE inventory SET quantity = quantity - 4
WHERE item = A;

2. 使用 SHARE LOCK / EXCLUSIVE LOCK

對要更新的資料做鎖定，但不建議使用，這會影響效能很大，如果沒有為欄位做好 Index ，就有可能會造成 Full-Table-Lock，應該盡量避免使用。

SELECT * FROM inventory FOR UPDATE;
UPDATE inventory SET quantity = 6 WHERE item = A;
COMMIT;

MySQL Range Locks on Indexed and Non-indexed Column

MySQL 的文件中介紹它的 Range Lock 採用的是 Next-Key Lock，Next-Key Lock 只 對有 Index 的欄位有作用

沒有做 Index 的欄位則沒有作用，且可能造成 Full Table Lock。

鎖定指定範圍的 索引（indexed） 欄位資料

有一學生資料表，裡面有學生的 姓名 (name)、身高 (height)、體重 (weight)，然後 身高 (height) 欄位有建立索引

限制鎖定 身高大於等於 170 的資料

SELECT * FROM student
WHERE height >= 170 FOR UPDATE;

上面語法會對所有 SELECT 出來的資料做 Exclusive Lock。MySQL 還會另外做 Range Lock

Lock 的範圍是 height 從 170 到無限大，不允許其他 Transaction 新增任何 height 的值介於這個範圍內的資料

Transaction B 在步驟 5 要新增身高超過 170 的資料會被 Block 住，要等 Transaction A 執行完 Commit 後才可以繼續執行

Range Lock 這個機制的好處是可以只 Lock 所有跟 Transaction A 有關的『資料 Range』，而不是 Lock 整張 Table，提高存取效能

MySQL RR Isolation，如果沒有額外下 Shared Lock 或是 Exclusive Lock 指令，Range Lock 就不會生效。

鎖定指定範圍的 未索引（none indexed） 欄位資料

限制鎖定 體重大於等於 58 的資料，但因為 體重 (weight) 欄位沒有做過索引，所以會對整張資料表做 Full Table Lock。

SELECT * FROM student
WHERE weight >= 58 FOR UPDATE;

所以即便新增的欄位資料 體重小於 58，也沒辦法順利新增資料

設定資料庫的 Transaction Isolation

MySQL

SET SESSION transaction_isolation='SERIALIZABLE';

參考資料

• 複習資料庫的 Isolation Level 與圖解五個常見的 Race Conditions | by Chester Chu | Medium
• 事務隔離 - 維基百科，自由的百科全書
• MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.1 Transaction Isolation Levels
• 對於 MySQL Repeatable Read Isolation 常見的三個誤解 | by Chester Chu | Medium
• 資料庫交易的 Isolation. 最近在讀 High Performance MySQL 時讀到了… | by Yuren Ju | getamis

• KeJyun學習日誌: MySQL鎖表(Lock Table)Table-level與Row-level比較