MySQL基礎鎖小結
- 2020 年 1 月 22 日
- 筆記
全局鎖
對整個資料庫實例加鎖。典型使用場景是，做全庫邏輯備份。
FTWRL
Flush tables with read lock 命令是MySQL 提供的一個加全局讀鎖的方法，簡稱FTWRL。
當需要讓整個庫處於只讀狀態的時候，可以使用這個命令，之後其他執行緒的以下語句會被阻塞： – 數據更新語句（數據的增刪改） – 數據定義語句（包括建表、修改表結構等） – 更新類事務的提交語句。
以前有一種做法，是通過 FTWRL 確保不會有其他執行緒對資料庫做更新，然後對整個庫做備份。注意，在備份過程中整個庫完全處於只讀狀態。但這樣其實很危險：
- 如果在主庫上備份，那麼在備份期間都不能執行更新，業務基本上就得停擺；
- 如果在從庫上備份，那麼備份期間從庫不能執行主庫同步過來的 binlog，會導致主從延遲。
single-transaction 方法
在可重複讀隔離級別下開啟一個事務，從而獲得一致性讀視圖。使用該方法的前提是引擎要支援這個隔離級別。
single-transaction 方法只適用於所有的表使用事務引擎的庫。
官方自帶的邏輯備份工具是 mysqldump。當 mysqldump 使用參數–single-transaction 的時候，導數據之前就會啟動一個事務，來確保拿到一致性視圖。而由於 MVCC 的支援，這個過程中數據是可以正常更新的。
不使用readonly的原因
通過命令set global readonly=true 的 readonly 方式也可以讓全庫進入只讀狀態，但還是會建議用 FTWRL 方式，主要有兩個原因：
- 一、在有些系統中，readonly 的值常會被用來做其他邏輯，比如用來判斷一個庫是主庫還是備庫。因此，修改 global 變數的方式影響面更大，不建議使用。
- 二、在異常處理機制上有差異。如果執行
FTWRL命令之後由於客戶端發生異常斷開，那麼 MySQL 會自動釋放這個全局鎖，整個庫回到可以正常更新的狀態。而將整個庫設置為readonly之後，如果客戶端發生異常，則資料庫就會一直保持readonly狀態，這樣會導致整個庫長時間處於不可寫狀態，風險較高。
業務的更新不只是增刪改數據（DML)，還有可能是加欄位等修改表結構的操作（DDL）。不論是哪種方法，一個庫被全局鎖上以後，要對裡面任何一個表做加欄位操作，都是會被鎖住的。
表級鎖
MySQL 裡面表級別的鎖有兩種：一種是表鎖，一種是元數據鎖（meta data lock，MDL)。
表鎖
表鎖的語法是 lock tables … read/write。與 FTWRL 類似，可以用 unlock tables 主動釋放鎖，也可以在客戶端斷開的時候自動釋放。需要注意，lock tables 語法除了會限制別的執行緒的讀寫外，也限定了本執行緒接下來的操作對象。
在還沒有出現更細粒度的鎖的時候，表鎖是最常用的處理並發的方式。而對於 InnoDB 這種支援行鎖的引擎，一般不使用 lock tables 命令來控制並發，畢竟鎖住整個表的影響面還是太大。
MDL
MDL（metadata lock)不需要顯式使用，在訪問一個表的時候會被自動加上。MDL 的作用是保證讀寫的正確性。
在 MySQL 5.5 版本中引入 MDL：
- 當對一個表做增刪改查操作（DML）的時候，加 MDL 讀鎖；
- 當要對錶做結構變更操作（DDL）的時候，加 MDL 寫鎖
MDL讀寫鎖相關規則
- 讀鎖之間不互斥，因此你可以有多個執行緒同時對一張表增刪改查。
- 讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性。因此，如果有兩個執行緒要同時給一個表加欄位，其中一個要等另一個執行完才能開始執行。
- 給一個表加欄位，或者修改欄位，或者加索引，需要掃描全表的數據。
事務中的 MDL 鎖，在語句執行開始時申請，但是語句結束後並不會馬上釋放，而會等到整個事務提交後再釋放。
如果是DML與DDL之間的交互，就更容易出現不可讀寫情況,若客戶端存在重試機制，很容易執行緒爆滿、session爆滿,session是佔用記憶體的,也會導致記憶體升高。
如何安全地給小表加欄位？
要解決長事務，事務不提交，就會一直占著 MDL 鎖。具體方案如下：
1.暫停或kill
在 MySQL 的 information_schema 庫的 innodb_trx 表中，你可以查到當前執行中的事務。如果你要做 DDL 變更的表剛好有長事務在執行，要考慮先暫停 DDL，或者 kill 掉這個長事務。
2.設置等待時間
比較理想的機制是，在 alter table 語句裡面設定等待時間，如果在這個指定的等待時間裡面能夠拿到 MDL 寫鎖最好，拿不到也不要阻塞後面的業務語句，先放棄。之後開發人員或者 DBA 再通過重試命令重複這個過程。
MariaDB 已經合併了 AliSQL 的這個功能，所以這兩個開源分支目前都支援 DDL NOWAIT/WAIT n 這個語法。
# 如果無法獲取鎖會立即返回失敗，NOWAIT等價於WAIT 0 ALTER TABLE tbl_name NOWAIT add column ... # 鎖等待超時時長可以通過WAIT n(單位為秒)來精確設置 ALTER TABLE tbl_name WAIT N add column ...
表鎖一般是在資料庫引擎不支援行鎖的時候才會被用到的。如果發現應用程式里有 lock tables 這樣的語句，需要追查一下，比較可能的情況是：
- 系統現在還在用 MyISAM 這類不支援事務的引擎，那要安排升級換引擎；
- 引擎升級了，但是程式碼還沒升級。此時需要把
lock tables和unlock tables改成begin和commit。
行鎖
行鎖就是針對數據表中行記錄的鎖。
MySQL 的行鎖是在引擎層由各個引擎自己實現的。但並不是所有的引擎都支援行鎖，比如 MyISAM 引擎就不支援行鎖。
兩階段鎖協議
在 InnoDB 事務中，行鎖是在需要的時候才加上的，但並不是不需要了就立刻釋放，而是要等到事務結束時才釋放。這個就是兩階段鎖協議。
如果你的事務中需要鎖多個行，要把最可能造成鎖衝突、最可能影響並發度的鎖盡量往後放。
死鎖
當並發系統中不同執行緒出現循環資源依賴，涉及的執行緒都在等待別的執行緒釋放資源時，就會導致這幾個執行緒都進入無限等待的狀態，稱為死鎖。
事務 A 和事務 B 在互相等待對方的資源釋放，就是進入了死鎖狀態。
死鎖解決策略
當出現死鎖以後，有兩種策略：
- 1.直接進入等待，直到超時。這個超時時間可以通過參數
innodb_lock_wait_timeout來設置。 - 2.發起死鎖檢測。發現死鎖後，主動回滾死鎖鏈條中的某一個事務，讓其他事務得以繼續執行。將參數
innodb_deadlock_detect設置為 on，表示開啟這個邏輯。
在 InnoDB 中，innodb_lock_wait_timeout 的默認值是 50s，意味著如果採用第一個策略，當出現死鎖以後，第一個被鎖住的執行緒要過 50s 才會超時退出，然後其他執行緒才有可能繼續執行。對於在線服務來說，這個等待時間太長往往是無法接受的。超時時間設置太短的話，會出現很多誤傷。
所以，正常情況下還是要採用第二種策略，即主動死鎖檢測，而且 innodb_deadlock_detect 的默認值本身就是 on。主動死鎖檢測在發生死鎖的時候，是能夠快速發現並進行處理的，但是它也是有額外負擔的。
熱點行更新導致的性能問題
問題
你可以想像一下這個過程：每當一個事務被鎖的時候，就要看看它所依賴的執行緒有沒有被別人鎖住，如此循環，最後判斷是否出現了循環等待，也就是死鎖。雖然最終檢測的結果是沒有死鎖，但是這期間要消耗大量的CPU資源。因此，你就會看到CPU利用率很高，但是每秒卻執行不了幾個事務。
方案
一種頭痛醫頭的方法，就是如果你能確保這個業務一定不會出現死鎖，可以臨時把死鎖檢測關掉。但是這種操作本身帶有一定的風險，因為業務設計的時候一般不會把死鎖當做一個嚴重錯誤，畢竟出現死鎖了，就回滾，然後通過業務重試一般就沒問題了，這是業務無損的。而關掉死鎖檢測意味著可能會出現大量的超時，這是業務有損的。
另一個思路是控制並發度。這個並發控制要做在資料庫服務端。如果你有中間件，可以考慮在中間件實現；如果你的團隊有能修改 MySQL 源碼的人，也可以做在 MySQL 裡面。基本思路就是，對於相同行的更新，在進入引擎之前排隊。這樣在 InnoDB 內部就不會有大量的死鎖檢測工作了。
從設計上優化，可以考慮通過將一行改成邏輯上的多行來減少鎖衝突。以影院賬戶為例，可以考慮放在多條記錄上，比如 10 個記錄，影院的賬戶總額等於這 10 個記錄的值的總和。這樣每次要給影院賬戶加金額的時候，隨機選其中一條記錄來加。這樣每次衝突概率變成原來的 1/10，可以減少鎖等待個數，也就減少了死鎖檢測的 CPU 消耗。
這個方案看上去是無損的，但其實這類方案需要根據業務邏輯做詳細設計。如果賬戶餘額可能會減少，比如退票邏輯，那麼這時候就需要考慮當一部分行記錄變成 0 的時候，程式碼要有特殊處理。
間隙鎖
間隙鎖，鎖的就是兩個值之間的空隙。在可重複讀隔離級別下才會生效。
跟行鎖有衝突關係的是「另外一個行鎖」。
但是間隙鎖不一樣，跟間隙鎖存在衝突關係的，是「往這個間隙中插入一個記錄」這個操作。間隙鎖之間都不存在衝突關係。
next-key lock
間隙鎖和行鎖合稱 next-key lock，每個 next-key lock 是前開後閉區間。也就是說，表 t 初始化以後，如果用 select * from t for update 要把整個表所有記錄鎖起來，就形成了 7 個 next-key lock，分別是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。
備註：這篇文章中，如果沒有特別說明，把間隙鎖記為開區間，把 next-key lock 記為前開後閉區間。
supremum的存在是因為 +∞是開區間。實現上，InnoDB 給每個索引加了一個不存在的最大值 supremum，這樣才符合前面說的「都是前開後閉區間」。
間隙鎖和next-key lock的引入，解決了幻讀的問題，但可能會導致同樣的語句鎖住更大的範圍，這其實是影響了並發度的。
讀提交隔離級別加binlog_format=row的組合。
如果把隔離級別設置為讀提交的話，就沒有間隙鎖了，從而操作數據的鎖範圍更小。但同時要解決可能出現的數據和日誌不一致問題，需要把 binlog 格式設置為 row。這也是現在不少公司使用的配置組合。
