2025年06月08日玄貓（BlackCat）

MySQL鎖定機制深入解析

本文探討 MySQL 的鎖定機制，包含使用者層級鎖定、Flush 鎖定、元資料鎖定、表格鎖定和例項層級鎖定，並解析死鎖問題的調查與處理方式。文章涵蓋顯式和隱式鎖定，例如 `LOCK TABLES`、`FLUSH TABLES WITH READ LOCK` 以及 InnoDB 的意圖鎖定，並提供程式碼範例和查詢

資料函式庫系統設計

MySQL 鎖定效能死鎖 FLUSH Metadata Lock

MySQL 的鎖定機制是確保資料函式庫在高併發環境下資料一致性和完整性的關鍵。本文將探討不同層級的鎖定型別，包含使用者層級鎖定、Flush 鎖定、元資料鎖定、表格鎖定和例項層級鎖定，例如備份鎖定和日誌鎖定。文章將分析這些鎖定的工作原理、應用場景以及如何診斷和解決鎖定相關問題，例如死鎖和「Waiting for table flush」等常見錯誤。此外，我們也會探討顯式鎖定和隱式鎖定，例如使用 LOCK TABLES、FLUSH TABLES WITH READ LOCK 以及 InnoDB 的意圖鎖定，並提供程式碼範例和查詢 performance_schema 等系統表格的技巧，幫助讀者更好地理解和應用 MySQL 的鎖定機制。

使用者層級鎖定與死鎖問題分析

使用者層級鎖定（User-Level Lock）是一種由應用程式或使用者直接控制的鎖定機制，用於協調多個資料函式庫連線之間的存取。在 MySQL 中，可以使用 GET_LOCK() 函式來取得鎖定，並使用 RELEASE_LOCK() 或 RELEASE_ALL_LOCKS() 來釋放鎖定。

死鎖問題例項分析

當兩個或多個連線互相等待對方的鎖定資源時，便會發生死鎖（Deadlock）。以下是一個典型的死鎖場景：

連線 1 取得 my_lock_1 鎖定。
連線 2 取得 my_lock_2 鎖定。
連線 2 嘗試取得 my_lock_1 鎖定，但因已被 連線 1 持有而進入等待狀態。
連線 1 嘗試取得 my_lock_2 鎖定，此時發生死鎖，MySQL 回報錯誤。

錯誤訊息示例

ERROR: 3058: Deadlock found when trying to get user-level lock; try rolling back transaction/releasing locks and restarting lock acquisition.

死鎖調查與處理

當發生死鎖時，可以查詢 performance_schema.metadata_locks 表來瞭解目前的鎖定狀態。

鎖定狀態查詢示例

SELECT *
FROM performance_schema.metadata_locks
WHERE object_type = 'USER LEVEL LOCK'
AND owner_thread_id IN (617, 618)\G

查詢結果顯示了鎖定的詳細資訊，包括鎖定型別、狀態、擁有者執行緒 ID 等。根據這些資訊，可以判斷哪些連線持有或等待鎖定，從而定位死鎖的原因。

解決死鎖問題

釋放鎖定：使用 RELEASE_LOCK() 或 RELEASE_ALL_LOCKS() 釋放不再需要的鎖定。
重新設計鎖定順序：確保所有連線以相同的順序取得鎖定，以避免迴圈等待。
使用超時機制：設定合理的 lock_wait_timeout，避免連線無限期等待鎖定。

Flush 鎖定與其相關問題

Flush 鎖定是在執行 FLUSH TABLES 陳述式時取得的鎖定，主要用於確保資料的一致性。當執行 FLUSH TABLES WITH READ LOCK 時，會持有一個共用的讀取鎖定，直到鎖定被明確釋放。

Flush 鎖定問題例項

當有長執行時間的查詢正在使用某個表時，執行 FLUSH TABLES 陳述式會被阻塞，直到該查詢完成。這可能導致後續需要存取該表的陳述式也被阻塞，出現「Waiting for table flush」狀態。

狀態查詢示例

SELECT thd_id, conn_id, state, current_statement
FROM sys.session
WHERE current_statement IS NOT NULL
AND thd_id IN (691, 692, 693)
ORDER BY thd_id\G

解決 Flush 鎖定問題

最佳化長執行查詢：改善查詢效能，減少執行時間。
殺死阻塞的查詢：在必要時，使用 KILL 命令終止長執行查詢，以釋放相關資源。
調整 lock_wait_timeout：設定合理的超時值，避免 FLUSH TABLES 陳述式無限期等待。

程式碼範例與解析

-- 連線 1
Connection 1> SELECT city.*, SLEEP(3) FROM world.city WHERE ID = 130;
-- 連線 2
Connection 2> FLUSH TABLES world.city;
-- 連線 3
Connection 3> SELECT * FROM world.city WHERE ID = 201;

內容解密：

在 連線 1 中，執行了一個包含 SLEEP(3) 的查詢，模擬一個長執行的查詢。
在 連線 2 中，執行 FLUSH TABLES world.city;，嘗試重新整理 world.city 表，但因 連線 1 的查詢仍在執行而被阻塞。
在 連線 3 中，執行了一個簡單的 SELECT 陳述式，同樣需要存取 world.city 表，但因 連線 2 的 FLUSH TABLES 陳述式被阻塞，進而導致 連線 3 的查詢也被阻塞。

綜上所述，正確地管理和排程鎖定是確保資料函式庫系統穩定性和效能的關鍵。透過瞭解和使用 MySQL 提供的使用者層級鎖定和 Flush 鎖定，可以有效地控制並發存取，避免潛在的效能瓶頸和死鎖問題。

MySQL中的鎖機制：表鎖與後設資料鎖

MySQL的鎖機制是資料函式倉管理系統中至關重要的組成部分，用於控制對資料的並發存取，確保資料的一致性和完整性。在MySQL中，鎖機制主要分為兩大類別：表鎖（Table Locks）和後設資料鎖（Metadata Locks）。本文將探討這兩種鎖的工作原理、應用場景以及如何排查與它們相關的問題。

表鎖（Table Locks）

表鎖是MySQL中最基本的鎖機制之一，主要用於控制對表的並發存取。當一個執行緒對表進行讀或寫操作時，它會取得相應的鎖，以防止其他執行緒同時修改或存取該表，從而導致資料不一致。

表重新整理鎖（Table Flush Lock）

表重新整理鎖是一種特殊的表鎖，當執行FLUSH TABLES陳述式時會用到。這個操作會關閉所有開啟的表，並重新開啟它們，主要用於清空查詢快取、重新整理日誌等維護操作。

-- 連線1
Connection 1> SELECT SLEEP(3) FROM world.city;
-- 連線2
Connection 2> FLUSH TABLES world.city;
-- 連線3
Connection 3> SELECT * FROM world.city WHERE ID = 201;

在上述例子中，連線1執行了一個慢查詢，使用了world.city表。與此同時，連線2執行了FLUSH TABLES world.city陳述式，由於連線1仍然持有world.city表的開啟狀態，因此FLUSH TABLES陳述式需要等待表重新整理鎖。最後，連線3嘗試查詢該表，也必須等待FLUSH TABLES陳述式完成。

#### 內容解密：

連線1的慢查詢：執行SELECT SLEEP(3) FROM world.city;，這使得連線1持有world.city表的資源。
FLUSH TABLES陳述式：連線2執行FLUSH TABLES world.city;，試圖重新整理表，但由於連線1仍在使用該表，因此該操作被阻塞。
連線3的查詢：連線3嘗試查詢world.city表，由於連線2的FLUSH TABLES操作仍在等待，因此連線3也被阻塞。

後設資料鎖（Metadata Locks）

後設資料鎖是MySQL 5.5版本引入的一種鎖機制，主要用於保護表的結構定義，防止在查詢或事務處理過程中表的結構被修改。

後設資料鎖的工作原理

SELECT陳述式和DML（資料操縱語言）查詢會取得分享後設資料鎖。
DDL（資料定義語言）陳述式會取得獨佔後設資料鎖。
當一個連線取得了後設資料鎖後，其他連線不能修改表的結構定義，但仍可以執行SELECT和DML操作。

-- 連線1
Connection 1> START TRANSACTION;
Connection 1> SELECT * FROM world.city WHERE ID = 130;

-- 連線2
Connection 2> OPTIMIZE TABLE world.city;

在這個例子中，連線1開啟了一個事務，並查詢了world.city表。然後，連線2試圖最佳化該表，但由於連線1的事務仍在進行中，持有分享後設資料鎖，因此連線2的OPTIMIZE TABLE陳述式被阻塞，等待後設資料鎖。

#### 內容解密：

連線1開啟事務並查詢：連線1開啟事務並查詢world.city表，取得了分享後設資料鎖。
連線2執行DDL操作：連線2試圖執行OPTIMIZE TABLE world.city;，需要取得獨佔後設資料鎖，但由於連線1持有分享後設資料鎖，因此被阻塞。
排查與解決：透過查詢sys.session或performance_schema.metadata_locks表，可以找出持有和等待後設資料鎖的執行緒資訊。最終，需要提交或回復連線1的事務，以釋放後設資料鎖，讓連線2的操作繼續。

如何排查鎖問題

當遇到鎖相關的問題時，可以透過以下方法進行排查：

使用sys.session檢視：查詢當前活動的會話資訊，包括執行緒ID、連線ID、狀態、當前陳述式等。

SELECT thd_id, conn_id, state, current_statement, trx_state FROM sys.session WHERE conn_id IN (相關連線ID);

    #### #### 內容解密：
    *   `thd_id`：執行緒ID。
    *   `conn_id`：連線ID。
    *   `state`：執行緒的當前狀態。
    *   `current_statement`：當前正在執行的陳述式。
    *   `trx_state`：事務的狀態。

2.  **使用`performance_schema.metadata_locks`表**：查詢後設資料鎖的資訊，包括物件型別、物件名、鎖型別、鎖狀態等。
    ```sql
SELECT object_type, object_schema, object_name, lock_type, lock_status, owner_thread_id 
FROM performance_schema.metadata_locks 
WHERE owner_thread_id IN (相關執行緒ID) AND object_type = 'TABLE';

#### #### 內容解密：
*   `object_type`：物件型別，如TABLE。
*   `object_schema`和`object_name`：物件所在的schema和名稱。
*   `lock_type`：鎖的型別，如SHARED_READ或EXCLUSIVE。
*   `lock_status`：鎖的狀態，如GRANTED或PENDING。
*   `owner_thread_id`：持有鎖的執行緒ID。

MySQL鎖機制詳解：顯式與隱式鎖定

MySQL的鎖機制是確保資料一致性和完整性的重要手段，特別是在多使用者並發存取資料函式庫的環境中。本篇文章將探討MySQL中的顯式表鎖定、隱式表鎖定以及備份鎖定等高階鎖定型別，並分析其工作原理、應用場景和最佳實踐。

顯式表鎖定

顯式表鎖定是透過LOCK TABLES和FLUSH TABLES WITH READ LOCK陳述式來實作的。這些鎖定會在指定的表上取得分享或獨佔鎖，直到使用UNLOCK TABLES陳述式釋放鎖定為止。

使用顯式表鎖定的場景

當需要鎖定整個表以進行備份或維護操作時，可以使用FLUSH TABLES WITH READ LOCK取得全域性讀鎖。
當需要對特定表進行獨佔存取時，可以使用LOCK TABLES取得獨佔鎖。

顯式表鎖定的特點

顯式表鎖定是元資料鎖定的一種，因此會在performance_schema.metadata_locks表中反映出來。
當取得顯式鎖定後，只能存取被鎖定的表，否則會出現錯誤。

範例：使用顯式表鎖定

-- 連線1
LOCK TABLES world.country READ, world.countrylanguage READ, world.city WRITE;
Query OK, 0 rows affected (0.0029 sec)

-- 嘗試更新被讀鎖定為READ的country表
UPDATE world.country SET Population = Population + 1 WHERE Code = 'AUS';
ERROR: 1099: Table 'country' was locked with a READ lock and can't be updated

-- 嘗試存取未被鎖定的sakila.film表
SELECT * FROM sakila.film WHERE film_id = 1;
ERROR: 1100: Table 'film' was not locked with LOCK TABLES

-- 釋放鎖定
UNLOCK TABLES;
Query OK, 0 rows affected (0.0006 sec)

#### 內容解密：

LOCK TABLES陳述式用於對指定的表取得鎖定，本例中對world.country和world.countrylanguage表取得讀鎖定（READ），對world.city表取得寫鎖定（WRITE）。
當嘗試更新被讀鎖定為READ的country表時，MySQL傳回錯誤，因為讀鎖定不允許寫入操作。
當嘗試存取未被鎖定的sakiila.film表時，MySQL傳回錯誤，因為該表未被包含在LOCK TABLES陳述式中。
使用UNLOCK TABLES陳述式釋放所有被當前連線持有的鎖定。

隱式表鎖定

MySQL會在查詢表時自動取得隱式表鎖定。對於InnoDB表來說，隱式表鎖定主要涉及意向鎖（Intention Locks）。

意向鎖的工作原理

當一個事務需要對表中的某些記錄進行鎖定時，首先會在表層級取得意向鎖。
意向鎖分為意向分享鎖（IS）和意向獨佔鎖（IX），取決於事務需要的鎖定型別。

意向鎖的例子

SELECT ... FOR SHARE陳述式會在相關表上取得意向分享鎖（IS）。
SELECT ... FOR UPDATE陳述式會在相關表上取得意向獨佔鎖（IX）。
DML陳述式（不包括SELECT）會在被修改的表上取得意向獨佔鎖（IX）。

檢視意向鎖

意向鎖的資訊可以透過查詢performance_schema.data_locks表來取得，篩選條件為LOCK_TYPE = 'TABLE'。

範例：檢視InnoDB意向分享鎖

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

SELECT * FROM world.city WHERE ID = 130 FOR SHARE\G
*************************** 1. row ***************************
ID: 130
Name: Sydney
CountryCode: AUS
District: New South Wales
Population: 3276207
1 row in set (0.0010 sec)

-- 連線2
SELECT engine, thread_id, object_schema, object_name, lock_type, lock_mode, lock_status, lock_data 
FROM performance_schema.data_locks 
WHERE lock_type = 'TABLE' AND thread_id = 796\G
*************************** 1. row ***************************
engine: INNODB
thread_id: 796
object_schema: world
object_name: city
lock_type: TABLE
lock_mode: IS
lock_status: GRANTED
lock_data: NULL
1 row in set (0.0011 sec)

#### 內容解密：

在連線1中啟動了一個事務，並執行了一個帶有FOR SHARE子句的SELECT查詢，這會在world.city表上取得意向分享鎖（IS）。
在連線2中查詢了performance_schema.data_locks表，以取得與連線1相關的鎖定資訊。
結果顯示，在world.city表上成功取得了意向分享鎖（IS），且狀態為已授權（GRANTED）。

備份鎖定

備份鎖定是MySQL 8引入的一種例項級別的鎖定，用於確保備份的一致性同時允許其他陳述式並發執行。備份鎖主要由MySQL Enterprise Backup使用，以避免在InnoDB表上執行FLUSH TABLES WITH READ LOCK。

被備份鎖阻塞的陳述式

建立、重新命名或刪除檔案的陳述式，如CREATE TABLE、CREATE TABLESPACE、RENAME TABLE和DROP TABLE等。

MySQL 高層級鎖定型別解析

MySQL 提供了多種高層級鎖定型別，以確保資料的一致性和完整性。這些鎖定型別大多與儲存引擎無關，包括使用者層級鎖定、例項層級鎖定、表格鎖定和元資料鎖定等。

使用者層級鎖定

使用者層級鎖定可用於保護應用程式中的工作流程，是最通用的鎖定型別。透過 GET_LOCK() 和 RELEASE_LOCK() 函式，可以實作使用者自定義的鎖定機制。

使用者層級鎖定的特點

可用於保護應用程式中的工作流程
最通用的鎖定型別
可透過 GET_LOCK() 和 RELEASE_LOCK() 函式實作

Flush 鎖定

Flush 鎖定發生在表格被重新整理時，可能會導致難以診斷的問題。Flush 鎖定與表格定義快取（TDC）版本鎖定有關。

Flush 鎖定的影響

可能導致難以診斷的問題
與表格定義快取（TDC）版本鎖定有關

元資料鎖定

元資料鎖定保護了結構描述物件的元資料，例如表格的欄位定義。

元資料鎖定的作用

保護結構描述物件的元資料
確保資料的一致性和完整性

表格鎖定

MySQL 支援顯式表格鎖定和隱式表格鎖定，其中隱式表格鎖定在使用 InnoDB 表格時最為常見。隱式表格鎖定也包括意圖鎖定。

表格鎖定的型別

顯式表格鎖定
隱式表格鎖定（最常見於 InnoDB 表格）
意圖鎖定（屬於隱式表格鎖定）

例項層級鎖定

例項層級鎖定包括備份鎖定和日誌鎖定，這兩種鎖定都是為了支援備份操作而設計的。

備份鎖定

備份鎖定保護備份操作免受可能導致備份不一致的變更，例如使用者和許可權的變更以及某些結構描述變更。

取得和釋放備份鎖定的範例

mysql> LOCK INSTANCE FOR BACKUP;
Query OK, 0 rows affected (0.0002 sec)

mysql> UNLOCK INSTANCE;
Query OK, 0 rows affected (0.0003 sec)

日誌鎖定

日誌鎖定是一種隱式鎖定，當查詢 performance_schema.log_status 表格時會自動取得，以確保日誌相關狀態值能夠以一致的方式取得，且最小化鎖定的影響。

查詢 `log_status` 表格的範例

Connection 1 > SELECT *
FROM performance_schema.log_status\G
*************************** 1. row ***************************
SERVER_UUID: fcbb7afc-bdde-11ea-b95f-ace2d35785be
LOCAL: {"gtid_executed": "d2549c41-86ca-11ea-9dc7-ace2d35785be:1-351", "binary_log_file": "binlog.000002", "binary_log_position": 39348}
REPLICATION: {"channels": [{"channel_name": "", "relay_log_file": "relay-bin.000002", "relay_log_position": 39588}]}
STORAGE_ENGINES: {"InnoDB": {"LSN": 2073604726, "LSN_checkpoint": 2073604726}}
1 row in set (0.0012 sec)

使用者層級鎖定與死鎖問題分析

死鎖問題例項分析

錯誤訊息示例

死鎖調查與處理

鎖定狀態查詢示例

解決死鎖問題

Flush 鎖定與其相關問題

Flush 鎖定問題例項

狀態查詢示例

解決 Flush 鎖定問題

程式碼範例與解析

內容解密：

MySQL中的鎖機制：表鎖與後設資料鎖

表鎖（Table Locks）

表重新整理鎖（Table Flush Lock）

#### 內容解密：

後設資料鎖（Metadata Locks）

後設資料鎖的工作原理

#### 內容解密：

如何排查鎖問題

MySQL鎖機制詳解：顯式與隱式鎖定

顯式表鎖定

使用顯式表鎖定的場景

顯式表鎖定的特點

範例：使用顯式表鎖定

#### 內容解密：

隱式表鎖定

意向鎖的工作原理

意向鎖的例子

檢視意向鎖

範例：檢視InnoDB意向分享鎖

#### 內容解密：

備份鎖定

被備份鎖阻塞的陳述式

MySQL 高層級鎖定型別解析

使用者層級鎖定

使用者層級鎖定的特點

Flush 鎖定

Flush 鎖定的影響

元資料鎖定

元資料鎖定的作用

表格鎖定

表格鎖定的型別

例項層級鎖定

備份鎖定

取得和釋放備份鎖定的範例

日誌鎖定

查詢 log_status 表格的範例

查詢 `log_status` 表格的範例