InnoDB 儲存引擎的鎖定機制是 MySQL 資料函式庫效能的關鍵因素。理解鎖定機制運作原理，才能有效診斷和解決效能瓶頸。透過 Performance Schema，可以監控鎖定事件，例如互斥鎖和訊號量的等待時間，進而找出效能問題的根源。鎖定爭用是常見的效能問題，尤其在高並發環境下。透過分析等待事件，可以識別出哪些鎖定資源成為瓶頸。此外，鎖定逾時和死鎖也是需要關注的議題。設定適當的鎖定等待逾時時間，可以避免交易長時間阻塞，而死鎖檢測機制則能有效預防和解決死鎖問題。
InnoDB 鎖定機制與效能分析
InnoDB 是 MySQL 中一個重要的儲存引擎，其鎖定機制對於資料函式庫的效能和資料一致性至關重要。本章節將探討 InnoDB 的鎖定機制，包括互斥鎖（mutex）和訊號量（semaphore），以及如何使用 Performance Schema 進行效能分析。
啟用 Performance Schema
要分析 InnoDB 的鎖定機制，首先需要啟用 Performance Schema。以下 SQL 陳述式用於啟用相關的 consumers 和 instruments：
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME IN ('events_waits_current', 'events_waits_history_long');
內容解密：
UPDATE performance_schema.setup_consumers：更新 Performance Schema 中的 consumers 設定。SET ENABLED = 'YES'：啟用指定的 consumers。WHERE NAME IN ('events_waits_current', 'events_waits_history_long')：指定要啟用的 consumers，包括目前等待事件和歷史等待事件。
執行測試案例
接下來，執行一個簡單的測試案例來觀察 InnoDB 的鎖定機制。首先，在另一個連線中執行一個 UPDATE 陳述式：
UPDATE world.city
SET Population = Population + 1
WHERE CountryCode = 'USA';
內容解密：
UPDATE world.city：更新world.city表中的資料。SET Population = Population + 1：將Population欄位的值增加 1。WHERE CountryCode = 'USA'：指定更新條件，只更新CountryCode為 ‘USA’ 的記錄。
分析等待事件
執行完測試案例後，可以使用以下 SQL 陳述式來分析等待事件：
SELECT REPLACE(event_name, 'wait/synch/', '') AS event, COUNT(*)
FROM performance_schema.events_waits_history_long
WHERE thread_id = 1058
AND event_name LIKE 'wait/synch/%'
GROUP BY event_name
WITH ROLLUP
ORDER BY COUNT(*);
內容解密：
SELECT REPLACE(event_name, 'wait/synch/', '') AS event, COUNT(*)：選擇等待事件名稱並計算其出現次數。FROM performance_schema.events_waits_history_long：從events_waits_history_long表中查詢資料。WHERE thread_id = 1058 AND event_name LIKE 'wait/synch/%'：指定執行緒 ID 和等待事件名稱的篩選條件。GROUP BY event_name WITH ROLLUP：按等待事件名稱分組並計算總計。ORDER BY COUNT(*)：按計數排序結果。
查詢結果顯示了各種同步物件的等待次數，例如互斥鎖（mutex）和讀寫鎖（rwlock）。這些資訊可以幫助我們瞭解 InnoDB 的鎖定機制和潛在的效能瓶頸。
停用 Performance Schema
完成分析後，可以停用 Performance Schema 以避免對效能造成影響：
UPDATE performance_schema.setup_instruments
SET ENABLED = 'NO', TIMED = 'NO'
WHERE NAME LIKE 'wait/synch/%';
UPDATE performance_schema.setup_consumers
SET ENABLED = 'NO'
WHERE NAME IN ('events_waits_current', 'events_waits_history_long');
內容解密：
UPDATE performance_schema.setup_instruments：更新 Performance Schema 中的 instruments 設定。SET ENABLED = 'NO', TIMED = 'NO'：停用指定的 instruments 並關閉計時。WHERE NAME LIKE 'wait/synch/%'：指定要停用的 instruments 範圍。
Plantuml 圖表說明
@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle
title InnoDB 鎖定機制效能分析與最佳實務
package "資料庫架構" {
package "應用層" {
component [連線池] as pool
component [ORM 框架] as orm
}
package "資料庫引擎" {
component [查詢解析器] as parser
component [優化器] as optimizer
component [執行引擎] as executor
}
package "儲存層" {
database [主資料庫] as master
database [讀取副本] as replica
database [快取層] as cache
}
}
pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中
master --> replica : 資料同步
note right of cache
Redis/Memcached
減少資料庫負載
end note
@enduml
此圖示展示了分析 InnoDB 鎖定機制的流程，從啟用 Performance Schema 到得出結論的步驟。
圖表解說：
- 啟用 Performance Schema：首先需要啟用相關的 consumers 和 instruments。
- 執行測試案例：執行一個簡單的測試案例來觀察 InnoDB 的鎖定機制。
- 分析等待事件：使用 Performance Schema 分析等待事件，瞭解鎖定機制和效能瓶頸。
- 停用 Performance Schema：完成分析後，停用 Performance Schema 以避免對效能造成影響。
- 結論：總結分析結果，瞭解 InnoDB 的鎖定機制和最佳化方法。
第8章：處理鎖定衝突
在資料函式倉管理系統中，鎖定（Locks）是用來限制對物件或記錄的存取，以避免平行操作衝突，確保資料的一致性和完整性。然而，在某些情況下，鎖定無法被授予。這種情況的發生取決於所請求的鎖定型別和當時的環境。後設資料（包括明確請求的表格鎖定）和InnoDB鎖定都設有逾時機制，而且對於某些鎖定情況，存在明確的死鎖檢測。
瞭解無法獲得鎖定的失敗是資料函式庫操作中的常態是非常重要的。理論上，可以使用非常粗粒度的鎖定來避免鎖定失敗，除了逾時之外——這是MyISAM儲存引擎的做法，但這樣做的結果是寫入平行性很差。然而，在實踐中，為了允許寫入工作負載的高平行性，細粒度鎖定是首選，這也引入了死鎖的可能性。
結論是，您應該始終使您的應用程式準備好重試取得鎖定或優雅地失敗。無論是顯式還是隱式鎖定，都適用此原則。
提示：始終準備好處理無法獲得鎖定的情況。無法獲得鎖定並不是災難性的錯誤，通常不應被視為錯誤。然而，如第9章“減少鎖定問題”中所討論的，在開發應用程式時，值得考慮減少鎖定爭用的技術。
本章的其餘部分將討論當有多個請求要求相同的資料鎖定時，InnoDB如何選擇哪個交易應該首先被授予鎖定請求、InnoDB資料鎖定的相容性，以及表格級別逾時、記錄級別逾時、InnoDB死鎖和InnoDB互斥鎖及訊號量等待的具體情況。
爭用感知交易排程（CATS）
當有多個請求要求相同的鎖定時，一個重要的決定是決定應該以什麼順序授予鎖定。最簡單的解決方案，也是資料函式庫中最常用的，是維護一個佇列，並按照先進先出（FIFO）的原則服務請求。這也是MySQL 5.7及更早版本中授予鎖定的方式；然而，在MySQL 8中，實作了一種新的排程演算法。
新的實作是根據密歇根大學Barzan Mozafari教授團隊開發的爭用感知交易排程（CATS）演算法，並由Sunny Bains與Mozafari教授團隊（尤其是Jiamin Huang）合作在MySQL中實作。
提示：如果您想了解更多關於CATS演算法的資訊，那麼https://mysqlserverteam.com/contention-aware-transaction-scheduling-arriving-in-innodb-to-boost-performance/ 是一個好的起點，並且在評論中有兩篇研究論文的連結——主要論文是http://web.eecs.umich.edu/~mozafari/php/data/uploads/pvldb_2018_sched.pdf。另一個來源是https://dev.mysql.com/doc/refman/en/innodb-transaction-scheduling.html 在參考手冊中。
CATS演算法的工作原理是，已經持有大量鎖定的交易是最重要的，要盡快推動它們完成，以便盡快釋放它們的鎖定，從而造福所有交易。這種方法的一個潛在缺點是，如果不斷有持有大量現有鎖定的交易等待給定的鎖定，那麼它們可能會使持有少量鎖定的交易永遠無法獲得鎖定。為了防止這種情況，該演算法具有防止飢餓的保障措施。
保障措施的工作原理是在當前鎖定請求佇列的末尾新增一個屏障，並且在考慮稍後到達的請求之前處理屏障之前的所有請求。
CATS演算法的主要好處是在高平行性工作負載下，而且直到MySQL 8.0.20，它只在InnoDB檢測到嚴重的鎖定爭用時才被使用。該演算法在8.0.20中得到了改進，以提高可擴充套件性，並且在information_schema.INNODB_TRX中增加了trx_schedule_weight列，因此可以觀察到交易的排程權重。
InnoDB鎖定的相容性
InnoDB支援多種鎖定模式，包括分享（S）鎖定和獨佔（X）鎖定。分享鎖定允許多個交易同時讀取同一資源，而獨佔鎖定則確保只有一個交易可以寫入資源。
內容解密：
- 分享鎖定（S）：多個交易可以同時持有S鎖定，用於讀取操作。
- 獨佔鎖定（X）：只有一個交易可以持有X鎖定，用於寫入操作。
- 意向分享鎖定（IS）和意向獨佔鎖定（IX）：用於表明交易對資源的意向，IS表示未來可能需要S鎖定，IX表示未來可能需要X鎖定。
-- 檢視InnoDB交易資訊
SELECT * FROM information_schema.INNODB_TRX\G
內容解密：
這條SQL陳述式用於檢視當前InnoDB的交易資訊，包括交易的狀態、開始時間、是否被鎖定等。透過這個查詢，可以瞭解當前資料函式庫中的交易活動，從而幫助診斷和解決鎖定相關的問題。
表格級別逾時和記錄級別逾時
InnoDB支援表格級別和記錄級別的鎖定逾時機制。當交易嘗試取得一個鎖定，但該鎖定被其他交易持有時，交易將進入等待狀態。如果等待時間超過了設定的逾時時間，交易將被回復。
-- 設定InnoDB鎖定等待逾時時間
SET GLOBAL innodb_lock_wait_timeout = 100;
內容解密：
這條SQL陳述式用於設定InnoDB的鎖定等待逾時時間（以秒為單位）。當交易等待鎖定的時間超過這個值時，交易將被回復，避免因為死鎖導致的交易掛起。
InnoDB死鎖檢測
InnoDB具有死鎖檢測機制，可以自動檢測和解決死鎖。當發生死鎖時，InnoDB將回復其中一個交易，以解除死鎖狀態。
-- 檢視InnoDB死鎖日誌
SHOW ENGINE INNODB STATUS;
內容解密：
這條SQL陳述式用於檢視InnoDB的狀態資訊，包括最近一次死鎖的詳細資訊。透過分析這些資訊，可以瞭解死鎖發生的原因和涉及的交易，從而最佳化應用程式邏輯，避免死鎖的發生。
InnoDB 鎖定等待逾時與相容性探討
在資料函式倉管理系統中，鎖定機制是確保資料一致性和完整性的重要手段。MySQL 的 InnoDB 儲存引擎採用複雜的鎖定策略來處理平行事務。本文將探討 InnoDB 的鎖定等待逾時機制及其相容性問題。
鎖定相容性與非對稱關係
InnoDB 的鎖定相容性規則並非簡單的對稱關係。不同的鎖定型別之間存在著複雜的相容性問題。例如，插入意圖鎖（Insert Intention Lock）必須等待間隙鎖（Gap Lock），但間隙鎖不需要等待插入意圖鎖。這種非對稱關係使得鎖定相容性的判斷變得困難。
-- 連線 1
START TRANSACTION;
UPDATE world.city SET Population = Population + 1 WHERE ID = 130;
-- 連線 2
START TRANSACTION;
UPDATE world.city SET Population = Population + 1 WHERE ID = 130; -- 將會被鎖定
內容解密：
- 連線 1 啟動了一個事務並對
world.city表中的特定列進行了更新，此時會對該列加上獨佔鎖。 - 連線 2 也嘗試更新同一列，但由於連線 1 的鎖定，連線 2 將進入等待狀態。
- 如果連線 1 長時間持有鎖定，連線 2 將可能遭遇逾時錯誤。
鎖定等待逾時
當事務請求鎖定但無法立即獲得時，將進入等待狀態。InnoDB 設定了鎖定等待逾時的機制，以避免事務無限期等待。
中繼資料與備份鎖定等待逾時
對於中繼資料和備份鎖定，預設的逾時時間為 365 天（31536000 秒）。可以透過 lock_wait_timeout 選項動態調整此值。
-- 設定 session 級別的 lock_wait_timeout 為 5 秒
SET SESSION lock_wait_timeout = 5;
LOCK TABLES world.city WRITE; -- 若無法立即獲得鎖定，將在 5 秒後逾時
內容解密：
- 將
lock_wait_timeout設定為 5 秒，以縮短鎖定等待時間。 - 當嘗試對
world.city表加上寫入鎖定時，若無法立即獲得，將等待 5 秒後傳回逾時錯誤。
InnoDB 鎖定等待逾時
對於 InnoDB 的記錄級鎖定，預設逾時時間為 50 秒。可透過 innodb_lock_wait_timeout 選項進行調整。
-- 設定 session 級別的 innodb_lock_wait_timeout 為 3 秒
SET SESSION innodb_lock_wait_timeout = 3;
UPDATE world.city SET Population = Population + 1 WHERE ID = 130; -- 若無法立即獲得鎖定，將在 3 秒後逾時
內容解密：
- 將
innodb_lock_wait_timeout設定為 3 秒，以縮短 InnoDB 鎖定等待時間。 - 當嘗試更新
world.city表中的特定列時，若無法立即獲得鎖定，將等待 3 秒後傳回逾時錯誤。
InnoDB 鎖定與交易管理：鎖等待逾時與死鎖處理
在高並發的資料函式庫環境中，鎖定機制對於維護資料一致性至關重要。InnoDB 儲存引擎提供了完善的鎖定管理，但同時也可能導致鎖等待逾時和死鎖問題。本文將探討 InnoDB 中的鎖等待逾時和死鎖機制，並提供最佳實踐建議。
鎖等待逾時（Lock Wait Timeout）
當一個交易嘗試取得已被其他交易持有的鎖時，便會發生鎖等待。若等待時間超過 innodb_lock_wait_timeout 設定的值，交易將被中斷並回復預設的單一陳述式。
設定 innodb_lock_wait_timeout
innodb_lock_wait_timeout 的預設值為 50 秒。根據實際需求調整此引數可以有效避免長時間的鎖等待。例如，將其設定為 3 秒或更低，以減少對其他交易的影響。
-- 檢視目前的 innodb_lock_wait_timeout 設定
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
-- 設定 innodb_lock_wait_timeout（需重啟 MySQL 服務）
SET GLOBAL innodb_lock_wait_timeout = 3;
innodb_rollback_on_timeout 的作用
當發生鎖等待逾時時，innodb_rollback_on_timeout 引數決定是否回復整個交易。預設情況下，此引數為關閉狀態，只回復觸發逾時的單一陳述式。啟用此選項可確保整個交易被回復，避免部分完成的事務佔用鎖資源。
-- 檢視目前的 innodb_rollback_on_timeout 設定
SHOW VARIABLES LIKE 'innodb_rollback_on_timeout';
-- 啟用 innodb_rollback_on_timeout（需重啟 MySQL 服務）
SET GLOBAL innodb_rollback_on_timeout = ON;
死鎖（Deadlocks）
死鎖發生於兩個或多個交易相互持有對方所需的鎖資源，形成迴圈等待的局面。InnoDB 能夠自動檢測死鎖並選擇一個交易作為犧牲者進行回復。
死鎖檢測與處理
InnoDB 預設啟用死鎖檢測。當檢測到死鎖時，受影響的交易將被回復，並傳回 ER_LOCK_DEADLOCK 錯誤（錯誤碼 1213）。
-- Connection 1
START TRANSACTION;
UPDATE world.city SET Population = Population + 1 WHERE ID = 130;
-- Connection 2
START TRANSACTION;
UPDATE world.city SET Population = Population + 1 WHERE ID = 3805;
UPDATE world.city SET Population = Population + 1 WHERE ID = 130; -- 將在此處發生死鎖
#### 內容解密：
- 啟動兩個獨立的交易：兩個交易各自更新不同的資料列。
- 形成死鎖條件：當第一個交易嘗試更新第二個交易已鎖定的列，而第二個交易又嘗試更新第一個交易鎖定的列時，死鎖便產生了。
- InnoDB 的死鎖檢測機制：自動檢測到死鎖後，會選擇其中一個交易進行回復，以釋放鎖資源。
最佳實踐建議
- 調整
innodb_lock_wait_timeout：根據實際應用需求，將鎖等待逾時時間設定為較低的值，如 1 至 3 秒，以減少對其他交易的影響。 - 啟用
innodb_rollback_on_timeout：在停用死鎖檢測的情況下，啟用此選項以確保在鎖等待逾時時回復整個交易。 - 最佳化交易邏輯：避免大事務和複雜的交易邏輯，減少持有鎖的時間，以降低死鎖發生的機率。
- 監控與分析：定期檢查
information_schema.INNODB_TRX表格，瞭解當前交易的執行情況和權重，有助於排查潛在的死鎖問題。