鎖定 Lock

鎖定 Lock
Categories:
Tags:

鎖定方式

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 A Transaction B
1 START TRANSACTION
2 UPDATE inventory SET quantity = quantity - 4 WHERE item = 'A'
3 START TRANSACTION
4 SELECT * FROM inventory WHERE item = 'A'
5 ROLLBACK
  • 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 A Transaction B
1 START TRANSACTION
2 START TRANSACTION
3 UPDATE inventory SET quantity = quantity - 4 WHERE item = 'A'
4 SELECT * FROM inventory WHERE item = 'A'
5 COMMIT
6 SELECT * FROM inventory WHERE item = 'A'
  • 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 B
1 START TRANSACTION
2 START TRANSACTION
3 SELECT * FROM inventory WHERE item = 'A'
4 SELECT * FROM inventory WHERE item = 'A'
5 UPDATE inventory SET quantity = 6 WHERE item = 'A'
6 UPDATE inventory SET quantity = 9 WHERE item = 'A'
7 COMMIT
8 COMMIT
  • 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
1 START TRANSACTION
2 SELECT * FROM inventory WHERE item = 'A'
3 START TRANSACTION
4 UPDATE inventory SET quantity = 999 WHERE item = 'A'
5 COMMIT
6 SELECT * FROM inventory WHERE item = 'A'
7 COMMIT
  • 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 A Transaction B
1 START TRANSACTION
2 START TRANSACTION
3 SELECT * FROM inventory WHERE item = 'A'
4 SELECT * FROM inventory WHERE item = 'A'
5 庫存數量是否 > 2
6 庫存數量是否 > 2
7 UPDATE inventory SET quantity = quantity - 2 WHERE item = 'A'
8 COMMIT
9 UPDATE inventory SET quantity = quantity - 2 WHERE item = 'A'
10 COMMIT
  • 兩個 Transaction 都滿足大於 2 這個『前提』，所以繼續進行更新庫存的操作
  • 最後卻導致庫存數量等於 -1，違反庫存不能小於 0 的前提，這就是 Write Skew。

所以 Write Skew 現象只有在 Serializable Isolation

Isolation Levels 資料隔離等級

MySQL

等級 Dirty Reads Non-repeatable Reads Lost Updates Phantoms Write Skews
Read Uncommitted 可能發生 可能發生 可能發生 可能發生 可能發生
Read Committed X 可能發生 可能發生 可能發生 可能發生
Repeatable Read X X 可能發生 可能發生 可能發生
Serializable X X X X X

PostgreSQL

等級 Dirty Reads Non-repeatable Reads Lost Updates Phantoms Write Skews
Read Uncommitted 可能發生 可能發生 可能發生 可能發生 可能發生
Read Committed X 可能發生 可能發生 可能發生 可能發生
Repeatable Read X X X X 可能發生
Serializable X X X X X

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 分

目前玩家分數狀況

Name Score Credit
Alice 980 0
Bob 740 0
Carol 880 0
Dave 540 0
Eve 610 0

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

步驟 Transaction A Transaction B
1 START TRANSACTION
2 SELECT * FROM gamer
3 START TRANSACTION
4 INSERT INTO gamer VALUE (Frank, 999, 0)
5 COMMIT
6 SELECT * FROM gamer

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

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

Name Score Credit
Alice 980 0
Bob 740 0
Carol 880 0

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

步驟 Transaction A Transaction B
1 START TRANSACTION
2 SELECT * FROM gamer
3 START TRANSACTION
4 INSERT INTO gamer VALUE (Frank, 999, 0)
5 COMMIT
6 SELECT * FROM gamer (Snapshot)
7 UPDATE gamer SET credit = credit + 1 WHERE score >= 740
8 SELECT * FROM gamer (Snapshot)
9 COMMIT

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

Name Score Credit
Alice 980 1
Bob 740 1
Carol 880 1
Dave 540 0
Eve 610 0
Frank 999 1

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 個

item quantity
A 10

商品交易過程會像這樣

步驟 Transaction A Transaction B
1 START TRANSACTION
2 START TRANSACTION
3 SELECT * FROM inventory WHERE item = A
4 SELECT * FROM inventory WHERE item = A
5 賣出 4 個 item
6 賣出 1 個 item
7 UPDATE inventory SET quantity = 6 WHERE item = A
8 COMMIT
9 UPDATE inventory SET quantity = 9 WHERE item = A
10 COMMIT

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

item quantity
A 9

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) 欄位有建立索引

name height (indexed) weight
Alice 173 58
Bob 181 72

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

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

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

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

步驟 Transaction A Transaction B
1 START TRANSACTION
2 SELECT * FROM student WHERE height >= 170 FOR UPDATE
3 START TRANSACTION
4 INSERT INTO student VALUE (Carol, 160, 50) -> success
5 INSERT INTO student VALUE (Dave, 180, 75) -> block
6 COMMIT
7 INSERT INTO student VALUE (Dave, 180, 75) -> success
8 COMMIT

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） 欄位資料

name height (indexed) weight
Alice 173 58
Bob 181 72

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

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

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

步驟 Transaction A Transaction B
1 START TRANSACTION
2 SELECT * FROM student WHERE weight >= 58 FOR UPDATE
3 START TRANSACTION
4 INSERT INTO student VALUE (Carol, 160, 50) -> block
5 COMMIT
6 INSERT INTO student VALUE (Carol, 160, 50) -> success
7 COMMIT

設定資料庫的 Transaction Isolation

MySQL

SET SESSION transaction_isolation='SERIALIZABLE';

參考資料