MySQL鎖定爭用案例分析與解決方案

– 9 分鐘閱讀

MySQL 鎖定機制對於資料函式庫的資料一致性和完整性至關重要，但同時也可能成為效能瓶頸。在高併發環境下，鎖定爭用時常發生，影響系統的吞吐量和回應速度。理解鎖定機制的運作原理，並掌握診斷和解決鎖定爭用的技巧，對於資料函式倉管理員和開發人員至關重要。本文將透過實際案例，分析幾種常見的鎖定爭用場景，例如中繼資料鎖定、外部索引鍵鎖定和旗號量爭用，並提供相應的解決方案和最佳實務。這些案例涵蓋了 DDL 操作、DML 操作以及 InnoDB 內部機制等多個層面，旨在幫助讀者更全面地理解 MySQL 鎖定機制，並提升資料函式庫效能調校能力。

MySQL 鎖定爭用分析與中繼資料鎖定

在MySQL的效能調校與問題診斷過程中，鎖定爭用（Lock Contention）是一個常見且重要的議題。當多個交易或查詢嘗試存取相同資源時，便可能發生鎖定爭用，進而影響系統的整體效能。本文將探討MySQL中的鎖定機制，並透過實際案例分析中繼資料鎖定（Metadata Lock Contention）的問題。

鎖定型別與狀態

MySQL支援多種鎖定型別，包括分享鎖（Shared Lock）、獨佔鎖（Exclusive Lock）等。透過觀察performance_schema.metadata_locks表格，可以瞭解當前系統中的鎖定狀態。

案例分析：中繼資料鎖定爭用

在案例中，我們觀察到系統出現了中繼資料鎖定爭用的情況。透過查詢performance_schema.metadata_locks表格，可以看到各個執行緒所持有的鎖定狀態。

SELECT object_name, lock_type, lock_status, owner_thread_id, owner_event_id
FROM performance_schema.metadata_locks
WHERE object_type = 'TABLE'
AND object_schema = 'sakila'
ORDER BY owner_thread_id, object_name, lock_type\G

結果分析

查詢結果顯示了多個執行緒對sakila資料函式庫中各個表格的鎖定狀態。例如，執行緒ID 792 對category、film和film_category表格持有分享讀取鎖定，而執行緒ID 796 則對film、inventory和rental表格持有分享可升級鎖定（SHARED_UPGRADABLE）。

鎖定狀態解讀

  • lock_type: 表示鎖定的型別，如SHARED_READ、SHARED_WRITE、EXCLUSIVE等。
  • lock_status: 表示鎖定的狀態，GRANTED表示已取得鎖定，PENDING表示等待取得鎖定。

中繼資料鎖定爭用的影響

中繼資料鎖定爭用可能導致交易或查詢被阻塞，進而影響系統的整體效能。在案例中，我們觀察到某些執行緒因等待鎖定而被阻塞，例如執行緒ID 795 對inventory表格的分享讀取鎖定請求被擱置，因為執行緒ID 796 對同一表格持有獨佔鎖定請求。

處理中繼資料鎖定爭用

  1. 最佳化交易邏輯: 檢視並最佳化交易邏輯，盡量減少鎖定的持有時間。
  2. 使用合適的隔離級別: 根據應用需求選擇合適的隔離級別，以平衡一致性和效能。
  3. 監控與分析: 定期監控系統中的鎖定狀態，並分析鎖定爭用的原因。
程式碼範例：查詢 metadata_locks 表格
SELECT 
    object_name, 
    lock_type, 
    lock_status, 
    owner_thread_id, 
    owner_event_id
FROM 
    performance_schema.metadata_locks
WHERE 
    object_type = 'TABLE'
    AND object_schema = 'sakila'
ORDER BY 
    owner_thread_id, 
    object_name, 
    lock_type;

內容解密：

  1. SELECT: 從performance_schema.metadata_locks表格中選擇所需的欄位。
  2. object_name, lock_type, lock_status, owner_thread_id, owner_event_id: 分別表示表格名稱、鎖定型別、鎖定狀態、擁有者執行緒ID和事件ID。
  3. WHERE: 篩選條件，限定object_type為’TABLE’且object_schema為’sakila’。
  4. ORDER BY: 排序結果，先按owner_thread_id排序，再按object_name和lock_type排序。

這個查詢陳述式幫助我們瞭解sakila資料函式庫中各個表格的鎖定狀態，從而診斷中繼資料鎖定爭用的問題。

MySQL 外部索引鍵相關鎖爭用案例分析

在資料函式倉管理中，外部索引鍵（Foreign Key）約束的引入為資料完整性提供了保障，但同時也可能導致鎖爭用問題。本章節透過一個具體案例，探討了在 MySQL 中因外部索引鍵而引起的鎖等待超時問題，並分析了其背後的原理和解決方案。

案例背景

案例發生在一個名為 sakila 的資料函式庫中，該資料函式庫大量使用了外部索引鍵約束。案例測試涉及對 inventory 表進行 ALTER TABLE 操作，以及對其他相關表（如 customer 和 category 表）進行 UPDATE 操作。這些操作導致了鎖等待超時問題。

鎖等待超時問題分析

透過查詢 performance_schema.metadata_locks 表，可以觀察到有大量執行緒因後設資料鎖（Metadata Locks）而被阻塞。後設資料鎖是用於保護資料函式庫物件（如表結構）的鎖，當對某個表進行 DDL 操作（如 ALTER TABLE）時，會請求獨佔的後設資料鎖。

mysql> SELECT object_name, COUNT(*)
FROM performance_schema.metadata_locks
WHERE object_type = 'TABLE'
AND object_schema = 'sakila'
GROUP BY object_name
ORDER BY object_name;
+
---
-
---
-
---
-
---
+
---
-
---
---
+
| object_name   | COUNT(*) |
+
---
-
---
-
---
-
---
+
---
-
---
---
+
| #sql-35e8_1d2 | 1        |
| address       | 2        |
| category      | 2        |
| customer      | 2        |
| film          | 3        |
| film_category | 2        |
| inventory     | 4        |
| payment       | 2        |
| rental        | 3        |
| staff         | 2        |
| store         | 3        |
+
---
-
---
-
---
-
---
+
---
-
---
---
+

sakila 資料函式庫表關係圖

@startuml
note
  無法自動轉換的 Plantuml 圖表
  請手動檢查和調整
@enduml

InnoDB 鎖等待分析

進一步分析發現，除了後設資料鎖等待外，還存在 InnoDB 鎖等待問題。透過 sys.innodb_lock_waits 檢視，可以查詢到具體的鎖等待資訊。

mysql> SELECT * FROM sys.innodb_lock_waits\G
*************************** 1. row ***************************
wait_started: 2020-08-02 14:17:13
wait_age: 00:00:02
wait_age_secs: 2
locked_table: `sakila`.`category`
locked_table_schema: sakila
locked_table_name: category
locked_table_partition: None
locked_table_subpartition: None
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 535860
waiting_trx_started: 2020-08-02 14:17:13
waiting_trx_age: 00:00:02
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 0
waiting_pid: 463
waiting_query: UPDATE sakila.category SET name = IF(name = 'Travel', 'Exploring', 'Travel') WHERE category_id = 16
waiting_lock_id: 2711671600928:1795:4:282:2711634698920
waiting_lock_mode: X,REC_NOT_GAP
blocking_trx_id: 535859
blocking_pid: 462
blocking_query: None
blocking_lock_id: 2711671600096:1795:4:282:2711634694976
blocking_lock_mode: S,REC_NOT_GAP

程式碼解析：

SELECT * FROM sys.innodb_lock_waits\G

此查詢陳述式用於檢視 InnoDB 鎖等待資訊。輸出結果顯示了鎖等待的詳細資訊，包括等待中的事務 ID、鎖定的表、索引、鎖模式等。

鎖等待解說：

  1. wait_started 和 wait_age: 表示鎖等待的開始時間和持續時間。
  2. locked_table 和 locked_index: 表示被鎖定的表和索引。
  3. waiting_trx_id 和 waiting_query: 表示正在等待的事務 ID 和正在執行的查詢。
  4. blocking_trx_id 和 blocking_query: 表示阻塞其他事務的事務 ID 和查詢。

解決方案與預防措施

  1. 避免長事務: 長事務會佔用鎖資源，導致其他事務等待。應盡量縮短事務的持續時間。
  2. 調整 lock_wait_timeout: 設定合理的鎖等待超時時間，避免長時間等待。
  3. 殺死阻塞的 DDL 陳述式: 當發現 DDL 陳述式阻塞其他操作時，可以考慮殺死該 DDL 陳述式，以釋放資源。

案例研究：訊號量（Semaphores）爭用分析與解決方案

在MySQL的效能調優過程中，Mutex（互斥鎖）和Semaphore（訊號量）爭用是一種極具挑戰性的問題。這類別爭用通常不會直接表現為明顯的錯誤，而是以整體延遲增加和吞吐量下降的形式出現，難以直接定位。本章節將透過一個案例研究，探討InnoDB中的適應性雜湊索引（Adaptive Hash Index）rw-semaphore爭用的調查與解決方法。

問題症狀

當InnoDB Mutex或Semaphore發生爭用時，主要透過兩種方式發現：InnoDB Monitor輸出和innodb_rwlock_% InnoDB指標。

  1. InnoDB Monitor輸出：在輸出結果的SEMAPHORES部分，可以觀察到目前的等待情況，例如：

SEMAPHORES

OS WAIT ARRAY INFO: reservation count 77606 –Thread 19304 has waited at btr0sea.ic line 122 for 0 seconds the semaphore: S-lock on RW-latch at 00000215E6DC12F8 created in file btr0sea.cc line 202 a writer (thread id 11100) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.ic line 122 Last time write locked in file G:\ade\build\sb_0-39697839-1592332179.68\mysql-8.0.21\storage\innobase\btr\btr0sea.cc line 1197

等待次數越多，等待時間越長，問題越嚴重。

2. **innodb_rwlock_% InnoDB指標**：監控中可觀察到rw-lock等待次數高且可能在高負載時出現峰值。

### 爭用的成因與影響

當對分享資源（如適應性雜湊索引）的請求速度超過處理速度時，便會發生爭用。這些資源在原始碼中受Mutex和rw-locks保護。爭用意味著要麼達到MySQL版本的並發限制，要麼需要將資源分割成更多部分。

#### 爭用原因分析

1. **並發限制**：MySQL版本對於特定工作負載的並發處理能力達到上限。
2. **資源分割**：需要最佳化或重新設計資源存取結構，以減少爭用。

### 緩解與解決方案

1. **監控與分析**：利用InnoDB Monitor和`innodb_rwlock_%`指標進行深入分析。
2. **最佳化查詢與事務**：減少事務大小和持續時間，使用索引減少記錄存取次數。
3. **調整隔離級別**：考慮使用`READ COMMITTED`事務隔離級別（若適用於應用）。
4. **升級MySQL版本**：新版本可能包含效能改進和並發處理最佳化。
5. **資源分割或最佳化**：針對特定資源爭用進行最佳化，如適應性雜湊索引的調優。

## 旗號量爭用案例研究

在資料函式庫系統中，旗號量（semaphore）是一種同步機制，用於控制對分享資源的存取。當多個執行緒或行程嘗試存取同一資源時，旗號量可以用來避免衝突。在本章中，我們將探討一個與InnoDB儲存引擎相關的旗號量爭用案例。

### 重現旗號量爭用

要重現旗號量爭用情況可能相當困難，尤其是當系統擁有多個CPU核心時。為了產生足夠的負載並觸發旗號量等待，我們需要執行特定的工作負載。在本案例研究中，我們使用了一個包含多個連線的測試指令碼，該指令碼會提示使用者輸入讀寫連線數、唯讀連線數和執行時間等引數。

#### 測試環境設定

- 使用一台具有8個CPU核心的筆記型電腦。
- InnoDB緩衝池大小設為預設的128 MiB。

#### 執行測試

執行測試指令碼時，使用者需要根據提示輸入相關引數，例如讀寫連線數和唯讀連線數。測試指令碼會根據輸入的引數建立相應的連線並執行特定的查詢。

```sql
-- 測試指令碼範例
Specify the number of read-write connections (0-31) [1]:
Specify the number of read-only connections (1-31) [7]:
Specify the number of seconds to run for (1-3600) [10]:
Restart MySQL before executing the test? (Y|Yes|N|No) [No]:
Delete the test specific indexes after executing the test? (Y|Yes|N|No) [Yes]:

減少旗號量爭用的方法

如果您的系統出現旗號量爭用，可以嘗試以下方法來減少爭用：

  1. 增加InnoDB緩衝池大小：較大的緩衝池可以減少磁碟I/O操作，從而降低旗號量爭用的可能性。
  2. 最佳化查詢：最佳化查詢陳述式，減少不必要的索引查詢和掃描，可以降低對分享資源的競爭。
  3. 調整InnoDB組態引數：適當調整innodb_flush_log_at_trx_commit和sync_binlog等引數，可以減少因flush操作導致的旗號量爭用。
SET GLOBAL innodb_flush_log_at_trx_commit = 0,
GLOBAL sync_binlog = 0;

注意事項

  • 在生產環境中，調整上述引數可能會導致資料遺失或不一致，因此請謹慎操作。
  • 在測試環境中，可以嘗試不同的組態和引數，以找出最優的解決方案。

工作負載分析

在本案例研究中，唯讀工作負載涉及三個表格之間的聯接操作，包括大量的次要索引查詢。讀寫工作負載則選擇一個隨機的姓氏，並為具有該姓氏的員工加薪。

唯讀查詢範例

SELECT dept_name, MIN(salary) min_salary,
AVG(salary) AS avg_salary, MAX(salary) AS max_salary
FROM employees.departments
INNER JOIN employees.dept_emp USING (dept_no)
INNER JOIN employees.salaries USING (emp_no)
WHERE dept_emp.to_date = '9999-01-01'
AND salaries.to_date = '9999-01-01'
GROUP BY dept_no
ORDER BY dept_name;

讀寫操作範例

SELECT last_name
FROM employees.employees
WHERE emp_no = ?;

SELECT emp_no, salary, from_date + INTERVAL 1 DAY
FROM employees.employees
INNER JOIN employees.salaries USING (emp_no)
WHERE employees.last_name = ?
AND to_date = '9999-01-01';

INSERT INTO employees.salaries
VALUES (?, ?, ?, '9999-01-01');

UPDATE employees.salaries
SET to_date = ?
WHERE emp_no = ? AND to_date = '9999-01-01';

索引建立

為了確保必要的次要索引存在以引起爭用，我們在相關表格上建立了額外的索引。

ALTER TABLE employees.dept_emp
ADD INDEX idx_concurrency_book_0 (dept_no, to_date);

ALTER TABLE employees.employees
ADD INDEX idx_concurrency_book_1 (last_name, first_name);

ALTER TABLE employees.salaries
ADD INDEX idx_concurrency_book_2 (emp_no, to_date, salary);

#### 內容解密：

  1. 唯讀查詢分析：此查詢涉及多表聯接和聚合操作，可能會導致大量的次要索引查詢，進而引起旗號量爭用。
  2. 讀寫操作分析：讀寫操作涉及選擇、插入和更新等操作，這些操作可能會對相關表格和索引造成競爭，進而導致旗號量爭用。
  3. 索引建立的目的：建立額外的索引是為了確保必要的次要索引存在，從而增加旗號量爭用的可能性，以便進行案例研究。
玄貓 BlackCat

玄貓 BlackCat

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