MySQL 交易隔離級別與鎖定機制深入解析

– 9 分鐘閱讀

MySQL 的交易隔離級別是確保資料一致性的關鍵機制。REPEATABLE READ 透過 MVCC 實作一致性讀取，避免髒讀和不可重複讀，但仍可能出現幻讀。SERIALIZABLE 則透過更嚴格的鎖定機制，完全序列化交易執行，杜絕幻讀。READ COMMITTED 允許讀取已提交的資料，不支援一致性讀取，但擁有更高的並發效能。理解這些隔離級別的特性，才能根據應用場景選擇最合適的策略。鎖定機制是實作隔離級別的基礎，瞭解不同級別下鎖的型別和範圍，有助於診斷和解決鎖定衝突問題。此外，Flush Locks 是 MySQL 中一種棘手的鎖定問題，會導致資料函式庫停滯，需要透過分析查詢日誌和系統狀態資訊來找出原因並解決。

MySQL 交易隔離級別深入解析：REPEATABLE READ 與 SERIALIZABLE

在資料函式倉管理系統中，交易（Transaction）隔離級別是確保資料一致性和完整性的關鍵機制。MySQL 支援多種交易隔離級別，包括 REPEATABLE READ 和 SERIALIZABLE。本文將探討這兩種隔離級別的差異、鎖定機制以及實際應用中的注意事項。

REPEATABLE READ 與 SERIALIZABLE 的差異

REPEATABLE READ 和 SERIALIZABLE 是 MySQL 中兩種較高的交易隔離級別。它們都旨在解決髒讀（Dirty Read）和不可重複讀（Non-Repeatable Read）問題，但在鎖定機制和一致性讀取（Consistent Read）方面存在差異。

  • REPEATABLE READ：在該級別下，MySQL 透過多版本並發控制（MVCC）機制實作一致性讀取，確保在同一交易中，多次讀取相同的資料會得到相同的結果，除非交易本身對資料進行了修改。然而，當交易進行寫入操作（如 UPDATE 或 DELETE）時，MySQL 仍會對相關資料加鎖，以防止其他交易同時修改這些資料。

  • SERIALIZABLE：這是最高的隔離級別，它透過對讀取操作也加鎖來完全序列化交易的執行，從而避免了幻讀（Phantom Read）問題。在 SERIALIZABLE 級別下，所有讀取操作都會對相關資料加分享鎖（Shared Lock），這使得其他交易無法對這些資料進行寫入操作，直到前一交易完成。

鎖定機制分析

當交易在 REPEATABLE READ 或 SERIALIZABLE 級別下執行寫入操作（如 UPDATE）時，MySQL 會對相關資料加獨佔鎖（Exclusive Lock）。以下是一個具體的例子，展示了在 REPEATABLE READ 級別下，UPDATE 操作所持有的鎖：

Connection 1> SET transaction_isolation = 'REPEATABLE-READ';
Connection 1> START TRANSACTION;
Connection 1> UPDATE world.city
SET Population = Population * 1.10
WHERE CountryCode = 'SVK'
AND District = 'Bratislava';

透過查詢 performance_schema.data_locks 表，可以觀察到 Connection 1 所持有的鎖：

Connection 2> SELECT index_name, lock_type, lock_mode, lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND lock_type = 'RECORD'
AND thread_id = 1574
ORDER BY index_name, lock_data DESC;

內容解密：

  1. index_name、lock_type、lock_mode 和 lock_data 的意義：

    • index_name：表示被鎖定的索引名稱。
    • lock_type：鎖定型別，此處為 RECORD，表示行級鎖。
    • lock_mode：鎖定模式，例如 X 表示獨佔鎖，X,GAP 表示間隙鎖。
    • lock_data：鎖定資料的具體值。

  2. 結果分析：

    • 在 CountryCode 索引上，對 'SVK', 3211、'SVK', 3210 和 'SVK', 3209 等記錄加了獨佔鎖（X）。
    • 對 PRIMARY 索引上的相關記錄加了 X,REC_NOT_GAP 鎖，確保不會有其他交易插入或刪除這些記錄。

  3. 鎖定策略的影響：

    • 這種鎖定機制確保了在交易進行期間，其他交易無法修改或刪除這些記錄，從而維護了資料的一致性。

一致性讀取與寫入操作的互動影響

在 REPEATABLE READ 級別下，一致性讀取（Consistent Read）是預設行為。然而，當交易進行寫入操作時，這些操作不受一致性讀取的限制。以下是一個範例：

Connection 1> SET transaction_isolation = 'REPEATABLE-READ';
Connection 1> START TRANSACTION;
Connection 1> SELECT ID, Name, Population
FROM world.city
WHERE CountryCode = 'BHS';

第一次查詢結果：

| ID | Name | Population | |

–|

-|

-| | 148 | Nassau | 172000 |

Connection 2> START TRANSACTION;
Connection 2> INSERT INTO world.city
VALUES (4080, 'Freeport', 'BHS', 'Grand Bahama', 50000);
Connection 2> COMMIT;

Connection 1 重複查詢：

Connection 1> SELECT ID, Name, Population
FROM world.city
WHERE CountryCode = 'BHS';

結果仍為： | ID | Name | Population | |

–|

-|

-| | 148 | Nassau | 172000 |

但當 Connection 1 更新資料時：

Connection 1> UPDATE world.city
SET Population = Population * 1.10
WHERE CountryCode = 'BHS';

更新後查詢：

Connection 1> SELECT ID, Name, Population
FROM world.city
WHERE CountryCode = 'BHS';

結果變為： | ID | Name | Population | |

|

|

-| | 148 | Nassau | 189200 | | 4080 | Freeport | 55000 |

內容解密：

  1. 一致性讀取的限制：

    • 在 REPEATABLE READ 級別下，一致性讀取確保在同一交易中，多次讀取相同的資料得到相同的結果，但這僅適用於讀取操作。

  2. 寫入操作的影響：

    • 當 Connection 1 更新資料時，它能夠「看見」並修改由 Connection 2 提交的新資料（Freeport），即使這些資料在 Connection 1 的初始讀取中並不存在。

  3. 避免此行為的方法：

    • 使用 FOR SHARE 子句顯式請求分享鎖。
    • 切換到 SERIALIZABLE 交易隔離級別。

交易隔離級別詳解：READ COMMITTED 的特性與應用

在資料函式倉管理系統中，交易隔離級別（Transaction Isolation Level）是確保資料一致性和並發控制的重要機制。MySQL 的 InnoDB 儲存引擎支援多種交易隔離級別，其中 READ COMMITTED 是一個常見且重要的級別。本文將探討 READ COMMITTED 的特性、優勢及其在實際應用中的表現。

READ COMMITTED 的基本特性

READ COMMITTED 交易隔離級別允許一個交易讀取其他已提交交易的資料變更。這意味著在 READ COMMITTED 級別下，髒讀（Dirty Read）是不被允許的，因為只有已提交的資料才能被讀取。然而，與 REPEATABLE READ 和 SERIALIZABLE 級別相比，READ COMMITTED 的鎖定機制較為寬鬆，從而提高了並發效能。

與 REPEATABLE READ 的比較

相較於 REPEATABLE READ，READ COMMITTED 有以下主要差異：

  1. 不支援一致性讀取（Consistent Read）：READ COMMITTED 不保證在同一個交易中多次讀取的結果是一致的，除非使用鎖定讀取（如 SELECT ... FOR SHARE）。
  2. 鎖定機制的差異：在 READ COMMITTED 下，DML 陳述式對未修改的記錄所持有的鎖會在 WHERE 子句評估完成後立即釋放。
  3. 間隙鎖（Gap Lock）的處理：READ COMMITTED 只在檢查外部索引鍵和唯一鍵約束以及頁面分裂（Page Split）時使用間隙鎖。
  4. 半一致性讀取（Semi-Consistent Read）：對於使用非索引列的 WHERE 子句，READ COMMITTED 允許使用最後提交的值進行比較，即使該列被其他交易鎖定。

實際應用與效能考量

在實際應用中，READ COMMITTED 提供了較好的並發效能和資料一致性保證。它適合於大多數需要強一致性和高並發性的應用場景。然而，需要注意的是，由於間隙鎖的減少，READ COMMITTED 可能會出現幻讀（Phantom Read）的情況。

範例分析

考慮以下範例，展示了在 READ COMMITTED 級別下鎖定的情況：

-- 連線 1
SET transaction_isolation = 'READ-COMMITTED';
START TRANSACTION;
UPDATE world.city
SET Population = Population * 1.10
WHERE CountryCode = 'SVK'
AND District = 'Bratislava';

-- 連線 2
SELECT index_name, lock_type, lock_mode, lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND lock_type = 'RECORD'
AND thread_id = 1582
ORDER BY index_name, lock_data DESC;

輸出結果顯示，在 READ COMMITTED 級別下，只有兩個記錄鎖被持有，分別對應 CountryCode 索引和主鍵索引，相較於 REPEATABLE READ 和 SERIALIZABLE 級別，大大減少了鎖定的數量。

半一致性讀取的優勢

半一致性讀取是 READ COMMITTED 的一個重要特性，它允許陳述式使用列的最後提交值進行比較，即使該行被鎖定。這減少了鎖定衝突，提高了並發效能。

-- 連線 1
SET transaction_isolation = 'READ-COMMITTED';
START TRANSACTION;
UPDATE world.city
SET Population = Population * 1.10
WHERE Name = 'San Jose'
AND District = 'Southern Tagalog';

-- 連線 2
SET transaction_isolation = 'READ-COMMITTED';
START TRANSACTION;
UPDATE world.city
SET Population = Population * 1.10
WHERE Name = 'San Jose'
AND District = 'Southern Tagalog';

在這個範例中，連線 2 的更新陳述式不會因為連線 1 的鎖定而被阻塞，因為它使用了半一致性讀取。

第13章：Flush Locks案例研究

鎖定問題是效能問題的常見原因之一，其影響可能非常嚴重。在最壞的情況下，查詢可能會失敗，且連線會堆積，導致無法建立新的連線。因此，瞭解如何調查鎖定問題並解決問題非常重要。

本章和後續章節將討論六類別鎖定問題：

  • Flush鎖
  • 中繼資料和結構鎖
  • 紀錄層級鎖（包括間隙鎖）
  • 死鎖
  • 外部索引鍵
  • 訊號量

除了外部索引鍵案例研究之外，每類別鎖定問題都使用不同的技術來確定鎖定爭用的原因。在閱讀範例時，您應該記住，類別似的技術可以用來調查不符合範例的鎖定問題。在前四個案例研究（第13-16章）中，討論被分為六個部分：

  • 症狀：這些使您能夠識別遇到的鎖定問題型別。
  • 原因：遇到這種鎖定問題的根本原因。這與本文前面對鎖的一般討論有關，特別是第6和7章。

Flush Locks的症狀與原因

在探討Flush Locks的詳細資訊之前，瞭解其基本概念非常重要。Flush Locks通常與資料的重新整理操作相關，例如當InnoDB需要將資料從記憶體重新整理到磁碟時。這些操作可能會導致鎖定爭用，進而影響系統效能。

調查Flush Locks問題

要調查Flush Locks問題，可以使用多種技術，包括：

  1. 檢查InnoDB的狀態：使用SHOW ENGINE INNODB STATUS命令可以提供有關InnoDB目前狀態的資訊，包括鎖定爭用的詳細資訊。
  2. 監控效能指標：監控與鎖定相關的效能指標，例如鎖等待次數和鎖等待時間，可以幫助識別鎖定問題。
  3. 分析查詢日誌：分析查詢日誌可以幫助識別導致鎖定爭用的查詢。

內容解密：

上述步驟可以用來系統地調查Flush Locks問題。首先，透過檢查InnoDB的狀態，可以獲得有關目前鎖定狀況的詳細資訊。其次，監控效能指標可以提供鎖定爭用的整體情況。最後，分析查詢日誌可以幫助識別具體導致問題的查詢。

SHOW ENGINE INNODB STATUS;

內容解密：

這條命令提供了InnoDB目前狀態的詳細資訊，包括事務、鎖和等待事件等。透過分析這些資訊，可以識別Flush Locks問題的原因。

內容解密：

此圖示展示了調查Flush Locks問題的步驟。首先從檢查InnoDB狀態開始，接著監控相關的效能指標，然後分析查詢日誌以識別導致問題的查詢，最後根據調查結果最佳化查詢或組態以解決問題。

Flush Locks：最棘手的鎖定問題之一

Flush Locks 是 MySQL 中一種難以偵測的鎖定問題。當發生 Flush Locks 時，資料函式庫可能會完全停滯，新的查詢請求將被阻塞，等待 Flush Lock 的釋放。本文將探討 Flush Locks 的症狀、成因、調查方法以及解決和預防措施。

症狀

當 Flush Locks 發生時，主要的症狀包括：

  • 新的查詢請求進入「Waiting for table flush」狀態，可能影響所有查詢或特定表格的查詢。
  • 連線數不斷增加，最終因達到最大連線數限制而導致新的連線請求失敗，錯誤訊息為 ER_CON_COUNT_ERROR: ERROR 1040 (HY000): Too many connections。
  • 至少有一個查詢的執行時間晚於最早的 Flush Lock 請求。
  • 可能在 Process List 中看到 FLUSH TABLES 陳述式，但並非總是如此。
  • 當 FLUSH TABLES 陳述式等待 lock_wait_timeout 時間後，會發生 ER_LOCK_WAIT_TIMEOUT 錯誤。
  • 使用 mysql 命令列客戶端連線資料函式庫時，若有預設的 schema，可能會出現連線阻塞的情況。

成因

當一個連線請求重新整理表格時，需要關閉對該表格的所有參照，這意味著沒有活躍的查詢可以使用該表格。因此，當 Flush 請求到達時，它必須等待所有使用待重新整理表格的查詢完成。預設情況下，除非明確指定要重新整理的表格，否則只需等待查詢完成，而非整個交易完成。

當等待 Flush Lock 成為問題時，意味著有一個或多個查詢阻止了 FLUSH TABLES 陳述式獲得 Flush Lock。由於 FLUSH TABLES 陳述式需要獨佔鎖，因此它反過來阻止後續查詢獲得所需的分享鎖。

這種情況常見於備份過程中，備份程式需要重新整理所有表格並獲得讀鎖，以建立一致的備份。

調查方法

調查 Flush Locks 需要檢視例項上正在執行的查詢列表。與其他鎖定爭用不同，沒有 Performance Schema 表格或 InnoDB 監視器報告可以直接查詢阻塞查詢。

SELECT thd_id, conn_id, state, current_statement, statement_latency
FROM sys.session
WHERE command = 'Query'\G

此查詢可以列出目前正在執行的查詢及其相關資訊，如執行緒 ID、連線 ID、狀態、目前陳述式和陳述式延遲。

解決方案

要解決 Flush Locks 問題，可以終止導致 Flush Lock 爭用的查詢。例如：

KILL 668;

這將終止 ID 為 668 的查詢，從而釋放 Flush Lock。

預防措施

為了減少遇到 Flush Locks 問題的機會，可以採取以下措施：

  • 最佳化查詢效能，減少長查詢時間。
  • 調整備份策略，避免在繁忙時段進行備份。
  • 使用 MysQl shell 取代 mysql 命令列客戶端，以避免因自動完成資訊收集而導致的阻塞。

總之，Flush Locks 是一種複雜且難以偵測的鎖定問題，需要仔細調查和分析才能解決。透過瞭解其症狀、成因和解決方案，可以有效地預防和處理這類別問題。

玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。