集結各種 MySQL 的鎖，超難懂的，必須一鼓作氣寫下來，不然每次看一次又忘一次…

目錄

樂觀鎖、悲觀鎖

MySQL 的 MyISAM V.S. InnoDB

需要注意的是 MySQL 有兩個儲存引擎，5.1 之前 default 是 MyISAM，5.5 之後 default 是 InnoDB，MyISAM 是表級鎖， InnoDB 是行級鎖，這點要搞清楚，不然接下來會非常容易搞混。 MyISAM 備份會有 inconsistent backup 風險，備份時不能寫入，InnoDB 用 transaction 機制備份，consistent backup。

鎖的總類

1. 全局鎖
2. 表級鎖
3. 行級鎖
4. 頁面鎖

全局鎖

使用時機：MySQL 是 MyISAM 儲存引擎時，做資料庫備份。 整個資料庫都上鎖，Flush tables with read lock，執行命令所有資料庫表都上了讀鎖，解鎖則下 UNLOCK TABLES。

表級鎖(Table Level Lock)

MyISAM 儲存引擎是表級鎖。 發生時機：儲存引擎不支持行級鎖(MyISAM儲存引擎)、SQL 沒匹配索引時也會啟動表鎖(InnoDB 也是哦)。 當對一個表寫鎖後，只有持有鎖的那個操作能對表更新，其餘操作必須等待此操作完成，鎖才會釋放。 MyISAM 儲存引擎在執行查詢 Select 語句，會自動加入讀鎖(Table Read Lock)，執行 Update 更新語句，會自動加寫鎖(Table Write Lock)，這也是 MyISAM 不會出現死鎖的原因，但在高併發架構下 MyISAM 就不太適用。

1. 發生於 DDL(CREATE/ALTER/DROP/TRUNCATE) 語言
2. 加鎖快
3. 寫鎖(Table Write Lock)該表，其他使用者不可讀寫該表，寫鎖優先層級高於讀鎖
4. 讀鎖(Table Read Lock)，可讀該表，但不可寫該表
5. 沒加到鎖的表，不可讀寫
6. 表級鎖不會有死鎖(deadlock)
7. 表級鎖容易衝突

行級鎖(Row Level Lock)

InnoDB 預設是行級鎖，但使用者沒使用到索引，會造成表級鎖。 InnoDB 強調 Transaction 與行級鎖，查詢 Select 語句，不會上鎖。

1. 加鎖慢
2. 會有死鎖(deadlock) 產生
3. 鎖衝突發生率低

共享鎖 Shared Lock(S)

LOCK IN SHARE MODE 使用時機： 不可用在同張表的同個 Row，應於於兩張表有關聯時，可以先判斷優先順序，對順序在前面的先上共享鎖，避免後續插入 Row 後，上一層資料被改動，造成其資料不一致，例如：當分類表分類 A 存在時，允許在其下面新增或修改子分類。 當 Transaction 對此 Row 上共享鎖，不允許其他 Transaction 上排他鎖(X)，允許讀此 Row，但不允許修改更新此 Row。 其他非此 Row，使用者皆可讀寫其他 Row。 如果對同一張表的同個 Row 做查詢上共享鎖，且同時修改，會造成死鎖(deadlock)，兩個 Transaction 都在等對方釋放鎖，陷入死胡同，造成 Transaction 回滾。

排他鎖 Exclusive Lock(X)

FOR UPDATE 使用時機： Update/Delete/Insert 會自動加上排他鎖 X 允許獲得排他鎖的 Row 更新資料，不允許其他共享鎖與排他鎖上鎖，不允許其他 Transaction 讀取與更新此 Row。

意向共享鎖 Intention Shared Lock(IS)

表級鎖層級，在 Transaction 給此行上共享鎖前，需要先得到此表的意向共享鎖。 意向鎖皆為 InnoDB 自己加的，使用者無需自行加入。

意向排他鎖 Intention Exclusive Lock(IX)

表級鎖層級，在 Transaction 給此行上排他鎖前，需要先得到此表的意向排他鎖。 意向鎖皆為 InnoDB 自己加的，使用者無需自行加入。 在 Update/Delete/Insert InnoDB會自行加上 IX。

其餘鎖的細節

記錄鎖(Record Locks)

在更新時加入會自動加紀錄鎖，用 transaction 包住，避免其他作動改變

1

update users set name = 'cat' where id = 1;

間隙鎖(Gap Locks)

InnoDB 防止同一個 transaction 兩次讀取結果不一致(幻讀)，間隙鎖是加在兩個索引之間的鎖，第一個索引之前或最後一個索引後的縫隙，稱為範圍鎖(Range Locks)。 試想幻讀正是因為插入造成資料不一致現象，只要防止下一筆資料更動與插入，就可以避免幻讀。

Next-Key Locks

可以看這篇解釋，基本上就是當使用者去上鎖某列後，其前面或後面間隙也一起上鎖。

1
2
3
4

# 第 id = 3,5筆被撈出來，避免這樣寫，會造成間隙也有鎖
select id,name from users where 'name' = 'John' for update;
select id,name from users where id >= 3 and id <= 9 for update; 
# 第 3 至 第 9 筆之間間隙上鎖，例如：不能插入第四筆

插入意向鎖(Insert intention Locks)

在插入時才會發生，常與間隙鎖衝突。

鎖的結論與情境

這張表是共享鎖與排他鎖的衝突相容表，但我覺得太複雜，看下一張表

筆者認為記住下面這張表足矣！真正要了解的是共享鎖會跟誰衝突、誰可以相容，排他鎖基本上跟其他鎖都衝突！

例子

1. 共享鎖可多重讀取，是 OK 的

1
2
3
4
5
6

# 第一句
start transaction;
Select id,name from users where id = 1 lock in share mode;
# 第二句再下一次
start transaction;
Select id,name from users where id = 1 lock in share mode;

2. 第一次下共享鎖讀，第二次下排他鎖讀，是 No Way 的

1
2
3
4
5
6

# 第一句
start transaction;
Select id,name from users where id = 1 lock in share mode;
# 第二句下排他鎖讀取，無法讀取 Lock wait timeout exceeded; 除非第一句 commit 結束
start transaction;
Select id,name from users where id = 1 for update;

3. 共享鎖第一句更新，但沒 commit，第二句共享鎖再更新一次，會形成死鎖(deadlock)

1
2
3
4
5
6
7

# 第一句
start transaction;
Select id,name from users where id = 1 lock in share mode;
Update users set name = 'apple' where id = 1;
# 第二句再次共享鎖，去更新，會死鎖 Deadlock found when trying to get lock; try restarting transaction
Select id,name from users where id = 1 lock in share mode;
Update users set name = 'boy' where id = 1;

4. 共享鎖第一句更新，但沒 commit，第二句排他鎖讀，去再更新一次(基本上 2 就擋住了，因此不會走到更新這步驟，結果也是 No Way)

5. 排他鎖第一句讀，第二句排他鎖再讀一次，是 No Way 的

1
2
3
4
5
6

# 第一句
start transaction;
Select id,name from users where id = 1 for update;
# 第二句再下一次，Lock wait timeout exceeded; try restarting transaction
start transaction;
Select id,name from users where id = 1 for update;

6. 排他鎖第一句讀，第二句共享鎖再讀一次，是 No Way 的

1
2
3
4
5
6

# 第一句
start transaction;
Select id,name from users where id = 1 for update;
# 第二句共享鎖讀一次，Lock wait timeout exceeded; try restarting transaction
start transaction;
Select id,name from users where id = 1 lock in share mode;

7. 排他鎖第一句讀完後修改，第二句直接再修改一次，是 No Way 的

1
2
3
4
5
6

# 第一句
start transaction;
Select id,name from users where id = 1 for update;
Update users set name = 'apple' where id = 1;
# 第二句直接再修改一次，Lock wait timeout exceeded;
Update users set name = 'boy' where id = 1;

因此共享鎖可以重複再次上同一筆 row 的共享鎖去讀，這是沒問題的！但對一筆資料上共享鎖，並且修改，必須等待第一筆 commit 後才能對同一筆上同一個 row 的資料做修改，否則會形成死鎖(deadlock)。 排他鎖就單純許多，只要對那一筆資料上排他鎖，只要對這筆資料做共享鎖或是排他鎖(上鎖不行)的讀取或更動(讀取更動都不行)都不行，除非第一個排他鎖 commit 完成，才能接續下面的動作。

併發 Transaction 帶來的問題

1. 更新失敗：當資料吞吐量大，多個 Transaction 併發，好死不死，當兩個更新互卡，發生死鎖(deadlock)，Transaction 只會選擇更新其中一筆，其中一筆 Transaction 則會 Rollback，就屬於更新失敗。
2. 髒讀 Dirty Read：發生於一個 Transaction A 先發生修改，未提交前，這個資料就不一致，此時另一個 Transaction B 不加以控制，前一筆好死不死沒上排他鎖，造成資料讀取到同一筆的資料，違反 Transaction 隔離性，讀到同一筆資料就稱為髒讀。(一句話說明：Transaction 未 commit 前，讀到同一筆資料就是髒讀)
3. 不可重複讀 Non-Repeatable Read： 一個 Transaction A 中在讀取資料時，第一次讀取的資料正常，但當第二次讀取，此筆資料被另一個 Transaction B 改變或被刪除，因此第二次讀取的資料與第一次不一致，稱作不可重複讀。(一句話說明： Transaction 兩次讀取資料不一致就是不可重複讀)
4. 幻讀 Phantom Read：跟 3 情境很像，發生於 一個 Transaction A 對一個或數個列作改動，此時另一個 Transaction B 做了修改或做了插入列，且 Transaction B commit，這時 Transaction A 去查詢是否還有條件沒更新到的列，顯示是沒有，但其實 Transaction A 的修改並無成功對其條件做全面更新，還有部分 Transaction B 所造成的列沒被 Transaction A 做到改動，就稱為幻讀。(一句話說明：同樣條件下，兩次讀取的筆數不一樣，就是幻讀)

Transaction 四種隔離層級

1. Read Uncommited: 最低級別，在此層級會 Transaction 可以看到其他 Transaction 未提交的資料，此層級會發生髒讀、不可重複讀、幻讀。
2. Read Commited: 多數資料庫默認層級，但 MySQL 不是這個層級，Transaction 只能看到提交完成的 Transaction 改變後的資料，此層級只能解決髒讀，仍會出現不可重複讀、幻讀。
3. Repeatable Read: MySQL 默認層級，當 Transaction A 開始時，使用者就算只做讀取該行，其他 Transaction B 也無法進行，除非，因此解決髒讀、不可重複讀，但仍會造成幻讀，但 MySQL MVCC 機制解決出現幻讀的現象。
4. Serializable: 最高層級，所有 Transaction 都排隊站好，以佇列形式，因此不會有髒讀、不可重複讀、幻讀這些現象，畢竟所有 Transaction 都排隊了，就不會衝突，但會造成資料吞吐量慢。

死鎖(deadlock) 怎辦 ?

InnoDB 發生死鎖可能性高，發生死鎖後，InnoDB 會監測讓其中一個 Transaction Rollback，其中一個 Transaction 獲得鎖，將 Transaction 繼續完成。 也不要當 InnoDB 是萬能的，設定等待超時參數，讓資源釋放，避免併發高時，MySQL 資源卡住，耗用大量資源，不只針對死鎖，也去把 slow query 耗費資源提早釋放掉。 加入鎖順序不一致，也會造成死鎖！ 例如：
第一個 transaction A 先 update users set name = 'a' where id = 30 再次 update users set name = 'b' where id = 20， 第二個 transaction B 把兩個順序相反過來做 update，兩個 transaction 好死不死一起發生，就會造成死鎖，雙方卡在那裡，等待釋放。
另一個例子：第一個 transaction A 更新 update users set name = 'a' where id = 25，此筆不存在，然後插入一筆 id = 25，
另一個 transaction A 更新 update users set name = 'b' where id = 26，此筆也不存在，然後插入一筆 id = 26，
因此兩次 update 都有用到索引，因此在 id = 20~30 建立間隙鎖，插入時會有意向鎖，兩者衝突造成死鎖。

• 當併發存取多張表時，講好同一套邏輯，講好相同訪問順序與更改順序，讓加鎖順序相同，降低死鎖。
• 將商務邏輯規劃好處理順序用 Transaction 包好，讓他們佇列依序處理減少死鎖。
• 更新使用排他鎖
• 出現死鎖，可以用SHOW INNODB STATUS命令來確定最後一個死鎖產生的原因和改進措施
• 除非必要，查詢不要加鎖

結論

理解樂觀鎖與悲觀鎖後，發現自己過去的做法是正確的，用 Transaction 包住每次交易的邏輯，當害怕資料被髒讀時，利用悲觀鎖 for update，禁止下一筆資料上鎖或是做任何更動，除非此筆鎖 commit，去釋放鎖，否則禁止其他 Transaction 做查詢或更動。 如果害怕做商業邏輯時，有資料被幻讀或是其他不該有的現象，可以再把這些 MySQL 細節想一遍，然後遇到死鎖或是鎖資源互搶時，可以再想想看是哪個環節出了問題！ 實際上，遇到鎖衝突或是死鎖還是勤加筆記，去瞭解到底卡在哪裡，哪些細節沒注意到，避免同樣事情再次發生！

參考連結

• Brian雜記
• 理解資料庫『悲觀鎖』和『樂觀鎖』的觀念
• MySQL 四種隔離級別
• MySQL 共享鎖與排他鎖
• MySQL share & for update 應用場景分析
• lock 的介紹與死鎖分析