MySQL效能與鎖定機制深入解析

– 11 分鐘閱讀

MySQL 的效能結構提供多種工具監控資料函式庫交易，例如 events_transactions_current 表格記錄目前活躍交易，events_statements_history 和 events_statements_current 表格則能分析交易歷史。此外，sys.session 檢視提供另一種查詢活躍交易的方式，而五個交易彙總表格則能分析不同維度的交易行為。理解這些工具能幫助開發者深入瞭解資料函式庫的執行狀況。MySQL 鎖定機制是確保資料一致性的關鍵，透過不同層級的鎖定型別，例如共用鎖、獨佔鎖和意向鎖，可以有效控制資料的存取許可權。文章將會探討這些鎖定型別的工作原理以及應用場景，並提供相關 SQL 查詢範例，讓讀者能實際操作和理解。此外，文章也會探討高層級鎖定，例如使用者層級鎖定，並提供程式碼範例說明如何使用相關函式來管理鎖定。

MySQL效能結構中的交易分析

MySQL的效能結構（Performance Schema）提供了豐富的工具來監控和分析資料函式庫的交易行為。透過效能結構中的表格，我們可以深入瞭解目前活躍的交易、交易的歷史記錄以及交易的彙總資料。

查詢活躍交易

效能結構中的events_transactions_current表格記錄了目前所有活躍的交易。若要查詢特定執行緒的交易，可以結合threads表格進行查詢。以下是一個查詢範例：

SELECT * FROM performance_schema.events_transactions_current
WHERE thread_id = <thread_id>;

內容解密：

  • events_transactions_current：此表格儲存了目前正在執行的交易資訊。
  • thread_id：用於識別執行緒的唯一ID。

分析交易歷史

若要進一步分析某個交易的所有陳述式（statements），可以查詢events_statements_current和events_statements_history表格。以下是一個查詢範例：

SELECT * FROM (
  SELECT event_id, sql_text, timer_start, timer_end
  FROM performance_schema.events_statements_history
  WHERE thread_id = <thread_id>
  UNION ALL
  SELECT event_id, sql_text, timer_start, timer_end
  FROM performance_schema.events_statements_current
  WHERE thread_id = <thread_id>
) AS statement_history
WHERE event_id >= 7
ORDER BY event_id DESC;

內容解密：

  • events_statements_history：儲存了歷史陳述式的資訊。
  • events_statements_current：儲存了目前正在執行的陳述式資訊。
  • UNION ALL：合併兩個查詢結果，並保留重複的行。
  • event_id >= 7：篩選出與特定交易相關的陳述式。

使用sys.session檢視活躍交易

sys.session檢視提供了另一種查詢活躍交易的方式，並排除了目前執行查詢的連線。以下是一個查詢範例：

SELECT * FROM sys.session
WHERE trx_state = 'ACTIVE'
AND conn_id <> CONNECTION_ID()\G

內容解密：

  • sys.session：一個系統檢視，提供了關於目前工作階段的資訊。
  • trx_state = 'ACTIVE'：篩選出活躍的交易。
  • conn_id <> CONNECTION_ID()：排除目前執行查詢的連線。

交易彙總表格

效能結構中還提供了五個交易彙總表格，用於分析不同維度的交易行為：

  • events_transactions_summary_global_by_event_name
  • events_transactions_summary_by_account_by_event_name
  • events_transactions_summary_by_host_by_event_name
  • events_transactions_summary_by_thread_by_event_name

這些表格可以幫助我們瞭解哪些連線或帳戶正在使用交易，以及他們的使用模式。

MySQL 鎖存取層級詳解

MySQL 資料函式庫鎖機制是確保資料一致性與平行存取控制的關鍵技術。在第一章中，我們初步介紹了鎖的概念，但並未探討鎖的工作原理。如果僅允許一個查詢存取資料函式庫，無論其執行的工作型別，都將導致效率極度低下。

鎖存取層級的基本概念

如同交通號誌管理交叉路口的交通流，資料函式庫鎖機制根據執行的操作型別授予存取權。交通號誌允許多輛車在相同方向上透過交叉路口，而資料函式庫則區分共用（讀取）與獨佔（寫入）存取權。

  • 共用鎖（Shared Locks）：允許多個連線同時獲得共用鎖，是最寬鬆的鎖存取層級，也稱為讀鎖。
  • 獨佔鎖（Exclusive Locks）：僅允許一個連線獲得鎖，也稱為寫鎖。
  • 意向鎖（Intention Locks）：表示事務對資源的存取意圖，可分為共用意向鎖與獨佔意向鎖。

本章將探討共用鎖、獨佔鎖以及意向鎖的工作原理與應用場景。

共用鎖的應用與特點

當執行緒需要保護某個資源但不打算修改它時，可以使用共用鎖來防止其他執行緒修改該資源，同時允許它們存取相同資源。共用鎖是最常用的鎖存取層級。

-- 連線 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)

Connection 1> SELECT * FROM world.city WHERE ID = 130 FOR SHARE;
+
---
--+
---
-
---
-+
---
-
---
-
---
--+
---
-
---
-
---
-
---
--+
---
-
---
-
---
-+
| ID | Name   | CountryCode | District        | Population |
+
---
--+
---
-
---
-+
---
-
---
-
---
--+
---
-
---
-
---
-
---
--+
---
-
---
-
---
-+
| 130 | Sydney | AUS         | New South Wales | 3276207    |
+
---
--+
---
-
---
-+
---
-
---
-
---
--+
---
-
---
-
---
-
---
--+
---
-
---
-
---
-+
1 row in set (0.0047 sec)

內容解密：

  1. START TRANSACTION; 陳述式用於啟動一個新的事務。
  2. SELECT * FROM world.city WHERE ID = 130 FOR SHARE; 使用 FOR SHARE 子句對查詢結果施加共用鎖，防止其他事務修改該資料列，同時允許其他事務讀取該資料列。
  3. 共用鎖適用於讀取密集型的操作，能夠提高平行讀取的效率。

查詢 Performance Schema 中的 metadata_locks 表，可以觀察到當前事務持有的鎖資訊：

Connection 1> SELECT object_type, object_schema, object_name, 
                      lock_type, lock_duration, lock_status
               FROM performance_schema.metadata_locks
               WHERE OWNER_THREAD_ID = PS_CURRENT_THREAD_ID()
               AND OBJECT_SCHEMA <> 'performance_schema'\G
*************************** 1. row ***************************
object_type: TABLE
object_schema: world
object_name: city
lock_type: SHARED_READ
lock_duration: TRANSACTION

內容解密：

  1. object_type 與 object_name 欄位表示被鎖定的物件型別與名稱，本例中為 world 資料函式庫中的 city 表。
  2. lock_type 為 SHARED_READ，表示這是一個共用讀取鎖。
  3. lock_duration 為 TRANSACTION，表示該鎖將持續到事務結束。

獨佔鎖與意向鎖簡介

  • 獨佔鎖 確保只有持有鎖的事務能夠修改資源，其他事務無法讀取或修改該資源，直到鎖被釋放。
  • 意向鎖 用於表示事務計劃執行的操作型別（讀取或寫入），有助於避免不同事務之間的鎖定衝突。

本章節對 MySQL 中的鎖存取層級進行了詳細解析，共用鎖、獨佔鎖以及意向鎖在不同的操作場景下發揮著至關重要的作用。正確理解和使用這些鎖機制，能夠有效提升資料函式庫的平行處理能力與資料一致性。

資料函式庫鎖定機制詳解

在資料函式倉管理系統中，鎖定機制（Locking Mechanism）是確保資料一致性和完整性的重要手段。MySQL 的 InnoDB 儲存引擎支援多種鎖定型別，包括分享鎖（Shared Locks）、獨佔鎖（Exclusive Locks）以及意圖鎖（Intention Locks）。本篇文章將探討這些鎖定型別的特性、應用場景以及它們之間的相容性。

分享鎖（Shared Locks）

分享鎖允許多個事務同時讀取同一份資料，但不允許任何事務對該資料進行修改。當一個事務對資料加上分享鎖後，其他事務也可以對同一資料加上分享鎖，但任何事務都無法對該資料加上獨佔鎖。分享鎖主要用於讀取操作，例如 SELECT 陳述式。

範例：取得分享鎖

START TRANSACTION;
SELECT * FROM world.city WHERE ID = 130 LOCK IN SHARE MODE;

查詢 metadata_locks 表格，可以看到 world.city 表格被加上了 SHARED_READ 鎖。

SELECT object_type, object_schema, object_name, lock_type, lock_duration, lock_status
FROM performance_schema.metadata_locks
WHERE OWNER_THREAD_ID = PS_CURRENT_THREAD_ID() AND OBJECT_SCHEMA <> 'performance_schema';

查詢 data_locks 表格，可以看到 world.city 表格被加上了 IS（意圖分享）鎖，並且對記錄加上了 S,REC_NOT_GAP 鎖。

SELECT engine, object_schema, object_name, lock_type, lock_mode, lock_status
FROM performance_schema.data_locks
WHERE THREAD_ID = PS_CURRENT_THREAD_ID();

內容解密：

  1. LOCK IN SHARE MODE：在 SELECT 陳述式中使用此選項，可以對查詢的資料加上分享鎖，確保資料在事務結束前不會被其他事務修改。
  2. SHARED_READ 鎖：表示對表格的後設資料（metadata）加上了分享鎖，允許多個事務同時讀取表格的後設資料。
  3. IS 鎖：意圖分享鎖，表示事務有意對表格中的某些記錄加上分享鎖。
  4. S,REC_NOT_GAP 鎖：對記錄加上分享鎖，並且不是間隙鎖（Gap Lock），表示只鎖定特定的記錄，而非範圍。

獨佔鎖（Exclusive Locks）

獨佔鎖確保只有持有該鎖的事務能夠存取或修改資料，其他事務無法對該資料加上任何鎖。獨佔鎖主要用於寫入操作，例如 UPDATE、DELETE 陳述式。

範例：取得獨佔鎖

START TRANSACTION;
UPDATE world.city SET Population = Population + 1 WHERE ID = 130;

查詢 metadata_locks 和 data_locks 表格，可以看到 world.city 表格被加上了 SHARED_WRITE 鎖和 IX（意圖獨佔）鎖，並且對記錄加上了 X,REC_NOT_GAP 鎖。

SELECT object_type, object_schema, object_name, lock_type, lock_duration, lock_status
FROM performance_schema.metadata_locks
WHERE OWNER_THREAD_ID = PS_CURRENT_THREAD_ID() AND OBJECT_SCHEMA <> 'performance_schema';

SELECT engine, object_schema, object_name, lock_type, lock_mode, lock_status
FROM performance_schema.data_locks
WHERE THREAD_ID = PS_CURRENT_THREAD_ID();

內容解密：

  1. SHARED_WRITE 鎖：表示對表格的後設資料加上了分享寫入鎖，允許多個事務同時存取表格的後設資料，但不允許修改。
  2. IX 鎖：意圖獨佔鎖，表示事務有意對表格中的某些記錄加上獨佔鎖。
  3. X,REC_NOT_GAP 鎖：對記錄加上獨佔鎖，表示只有持有該鎖的事務能夠修改該記錄。

意圖鎖（Intention Locks）

意圖鎖是一種訊號鎖，用於表示事務將要對表格或記錄加上分享或獨佔鎖。意圖鎖分為意圖分享鎖（IS）和意圖獨佔鎖（IX）。意圖鎖的主要目的是提高鎖定機制的效率，避免不相容的鎖請求導致的阻塞。

意圖鎖的作用

  • 提高效率：意圖鎖允許 InnoDB 在不阻塞相容操作的前提下，提前知道事務的意圖，從而更有效地管理鎖請求。
  • 避免阻塞：透過意圖鎖，InnoDB 可以在事務真正需要對記錄或表格加上鎖之前，就知道是否有其他事務持有不相容的鎖，從而避免不必要的等待。

鎖相容性矩陣

鎖相容性矩陣定義了不同型別的鎖請求之間是否相容。引入意圖鎖後，鎖相容性變得更加複雜，不再是簡單的分享鎖相容、獨佔鎖不相容的規則。

鎖相容性分析

現有鎖請求的鎖相容性
分享 (S)分享 (S)相容
分享 (S)獨佔 (X)不相容
獨佔 (X)分享 (S)不相容
獨佔 (X)獨佔 (X)不相容
意圖分享 (IS)分享 (S)相容
意圖分享 (IS)獨佔 (X)不相容
意圖獨佔 (IX)分享 (S)不相容
意圖獨佔 (IX)獨佔 (X)不相容

內容解密：

  1. 相容性規則：不同的鎖請求之間根據其型別決定是否相容。例如，兩個分享鎖請求是相容的，但分享鎖與獨佔鎖請求之間是不相容的。
  2. 意圖鎖的作用：意圖鎖的存在使得 InnoDB 能夠更靈活地處理多個事務之間的鎖請求，提高平行處理能力。

高層級鎖定型別

在前一章中，您瞭解了分享和獨佔存取層級。原則上，您可以建立一個只包含一種鎖定型別的鎖定系統，該鎖定型別可以是分享或獨佔的。然而，這意味著它必須在例項層級上運作，因此在允許對資料進行平行讀寫存取方面表現很差。在本章和下一章中，您將瞭解到有多種鎖定型別，具體取決於它們所保護的資源。雖然這使得鎖定變得更加複雜，但它也允許更細粒度的鎖定，從而支援更高的平行性。

本章討論了MySQL中的高層級鎖定，從使用者層級鎖定開始，並逐步介紹了在MySQL層級（即儲存引擎之上）處理的各種鎖定型別。其中包括重新整理鎖定、中繼資料鎖定、明確和隱含的表格鎖定（這是一個例外，因為它們由InnoDB處理）、備份鎖定和日誌鎖定。

使用者層級鎖定

使用者層級鎖定是一種明確的鎖定型別，應用程式可以使用它來保護，例如，一個工作流程。它們不常被使用，但對於某些需要序列化存取的複雜任務非常有用。所有使用者鎖定都是獨佔鎖定，並使用最多64個字元的名稱來取得。

您可以使用一組函式來操作使用者層級鎖定：

  • GET_LOCK(name, timeout)：透過指定鎖定的名稱來取得鎖定。第二個引數是以秒為單位的逾時時間；如果在該時間內無法取得鎖定，則函式傳回0。如果取得鎖定，則傳回值為1。如果逾時時間為負數，則函式將無限期等待鎖定可用。
  • IS_FREE_LOCK(name)：檢查指定的鎖定是否可用。如果鎖定可用，則函式傳回1；如果不可用，則傳回0。
  • IS_USED_LOCK(name)：這與IS_FREE_LOCK()函式相反。如果鎖定正在使用中（不可用），則函式傳回持有該鎖定的連線ID；如果未使用（可用），則傳回NULL。
  • RELEASE_ALL_LOCKS()：釋放由連線持有的所有使用者層級鎖定。傳回值是釋放的鎖定數量。
  • RELEASE_LOCK(name)：釋放具有指定名稱的鎖定。如果釋放鎖定，則傳回值為1；如果鎖定存在但不屬於該連線，則傳回0；如果鎖定不存在，則傳回NULL。

內容解密：

上述五個函式用於管理使用者層級的鎖定，分別用於取得、檢查和釋放鎖定。GET_LOCK 函式用於取得一個名為 name 的鎖定，timeout 引數控制等待時間。若成功取得鎖定，傳回 1；若逾時則傳回 0；若發生錯誤則傳回 NULL。IS_FREE_LOCK 用於檢查一個名為 name 的鎖定是否可用，若可用傳回 1，否則傳回 0。IS_USED_LOCK 檢查一個名為 name 的鎖定是否正在被使用，若正在被使用則傳回持有該鎖定的連線 ID，否則傳回 NULL。RELEASE_ALL_LOCKS 釋放當前連線所持有的所有使用者層級鎖定，並傳回釋放的鎖定數量。RELEASE_LOCK 釋放一個名為 name 的鎖定，若成功釋放傳回 1，若該鎖定存在但不屬於當前連線則傳回 0，若該鎖定不存在則傳回 NULL。

SELECT GET_LOCK('my_lock', 10);  -- 嘗試取得名為 my_lock 的鎖定，逾時時間為10秒
SELECT IS_FREE_LOCK('my_lock');  -- 檢查 my_lock 鎖定是否可用
SELECT IS_USED_LOCK('my_lock');  -- 檢查 my_lock 鎖定是否正在被使用
SELECT RELEASE_LOCK('my_lock');  -- 釋放 my_lock 鎖定
SELECT RELEASE_ALL_LOCKS();      -- 釋放所有使用者層級鎖定

內容解密：

此程式碼範例展示瞭如何使用上述五個函式來管理一個名為 my_lock 的使用者層級鎖定。首先，嘗試使用 GET_LOCK 函式取得該鎖定，並設定10秒的逾時時間。接著，使用 IS_FREE_LOCK 和 IS_USED_LOCK 檢查該鎖定的狀態。最後，使用 RELEASE_LOCK 和 RELEASE_ALL_LOCKS 釋放該鎖定和所有使用者層級的鎖定。

可以透過多次呼叫 GET_LOCK() 來取得多個鎖定。如果這樣做，請小心確保所有使用者都以相同的順序取得鎖定，否則可能會發生死結。如果發生死結，則會傳回 ER_USER_LOCK_DEADLOCK 錯誤（錯誤程式碼3058）。清單6-1中顯示了一個例子。

清單6-1 使用者層級鎖定的死結範例

-- 連線 Processlist ID 執行緒 ID 事件 ID
-- 
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
-- 1 322 617 6
-- 2 323 618 6
-- 連線 1
Connection 1> SELECT GET_LOCK('my_lock_1', -1);
+
---
-
---
-
---
-
---
-
---
-
---
-
---
+
| GET_LOCK('my_lock_1', -1) |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
+
|                         1 |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
+
1 row in set (0.0003 sec)
-- 連線 2
Connection 2> SELECT GET_LOCK('my_lock_2', -1);

內容解密：

此清單展示了兩個連線之間可能發生的死結情況。在連線1中，首先使用 GET_LOCK 函式取得名為 my_lock_1 的鎖定，且逾時時間設為無限期。在連線2中，同樣使用 GET_LOCK 函式取得名為 my_lock_2 的鎖定。若接下來連線1嘗試取得 my_lock_2 而連線2嘗試取得 my_lock_1 時，便會發生死結，因為兩個連線都在等待對方釋放其持有的鎖定。

本章介紹了MySQL中的高層級鎖定型別，包括使用者層級的鎖定及其相關操作。這些內容有助於理解MySQL如何管理平行存取以及如何使用這些機制來避免資料競爭和死結問題。下一章將進一步討論其他型別的高層級鎖定及其應用場景。

玄貓 BlackCat

玄貓 BlackCat

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