【MySQL】第7節｜Mysql鎖機制與優化實踐以及MVCC底層原理剖析

鎖等待分析

我們通過檢查InnoDB_row_lock相關的狀態變量來分析系統上的行鎖的爭奪情況

示例場景

假設有兩個用戶同時操作賬戶表?accounts（主鍵為?id）：

1. 用戶A：執行轉賬，鎖定賬戶?id=1?并等待3秒：

BEGIN;
SELECT * FROM accounts WHERE id=1 FOR UPDATE;
-- 模擬業務邏輯耗時3秒
COMMIT;

2. 用戶B：幾乎同時嘗試鎖定同一賬戶：?

BEGIN;
SELECT * FROM accounts WHERE id=1 FOR UPDATE;  -- 被阻塞，等待用戶A釋放鎖
COMMIT;

參數變化說明

Innodb_row_lock_current_waits
- 含義：當前正在等待鎖的事務數量。
- 示例：當用戶B被阻塞時，該值為?1；用戶A提交后，該值變為?0。
Innodb_row_lock_waits
- 含義：系統啟動后鎖等待的總次數。
- 示例：用戶B等待一次后，該值增加?1。
Innodb_row_lock_time
- 含義：系統啟動后所有鎖等待的總時長（毫秒）。
- 示例：用戶B等待3秒（3000毫秒）后，該值增加?3000。
Innodb_row_lock_time_avg
- 含義：每次鎖等待的平均時長（毫秒）。
- 示例：若此前無等待，此次等待后該值為?3000；若已有1次等待（假設為2000毫秒），則平均值為?(2000 + 3000) / 2 = 2500。
Innodb_row_lock_time_max
- 含義：系統啟動后最長的單次鎖等待時長。
- 示例：若此前最大值為2500毫秒，此次等待3000毫秒后，該值更新為?3000。

關鍵指標解讀

高?Innodb_row_lock_waits：

表示頻繁出現鎖爭用，可能是事務持有鎖時間過長或并發度過高。

→?優化：縮短事務執行時間，避免長事務。

高?Innodb_row_lock_time_avg：

表示鎖等待時間過長，可能是鎖粒度太大或索引缺失導致鎖范圍擴大。

→?優化：優化查詢語句，確保使用索引減少鎖范圍。

高?Innodb_row_lock_time：

累計等待時間過長，影響整體吞吐量。

→?優化：調整業務邏輯，減少鎖競爭（如批量操作拆分為多次小事務）。

查看方法

SHOW STATUS LIKE 'Innodb_row_lock%';-- 輸出示例：
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 3000  |
| Innodb_row_lock_time_avg      | 3000  |
| Innodb_row_lock_time_max      | 3000  |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+

這些指標是?動態累積值，重啟MySQL后會重置為0。通過監控它們的變化趨勢，可以定位數據庫性能瓶頸。

INFORMATION_SCHEMA系統庫鎖相關數據表

以下是關于 InnoDB 事務和鎖相關系統表的示例說明，結合常見場景演示如何查詢和分析數據：

一、查看當前事務（`INNODB_TRX`?/?`information_schema.INNODB_TRX`）

場景：查詢當前正在運行的事務

SELECTtrx_id,               -- 事務IDtrx_state,            -- 事務狀態（RUNNING、LOCK WAIT等）trx_started,          -- 事務開始時間trx_query,            -- 正在執行的SQL語句（可能為NULL）trx_mysql_thread_id   -- 事務對應的線程ID
FROM INFORMATION_SCHEMA.INNODB_TRX;

示例輸出：

trx_id	trx_state	trx_started	trx_query	trx_mysql_thread_id
12345	LOCK WAIT	2025-05-24 15:30:00	SELECT * FROM accounts WHERE id=1 FOR UPDATE	101
12346	RUNNING	2025-05-24 15:29:55	UPDATE orders SET status='paid'	102

說明：

trx_state=LOCK WAIT：表示事務正在等待鎖（如示例中線程101在等待鎖定id=1的記錄）。
trx_query：若事務未執行具體SQL（如處于空閑狀態），則顯示為NULL。

二、查看鎖信息（`INNODB_LOCKS`?→ 8.0+ 改為?`performance_schema.data_locks`）

場景：查詢當前數據庫中的行鎖

-- MySQL 5.7及之前版本
SELECTlock_id,          -- 鎖IDlock_trx_id,      -- 持有鎖的事務IDlock_mode,        -- 鎖模式（如X鎖、S鎖、IS鎖、IX鎖）lock_table,       -- 鎖定的表名lock_index,       -- 鎖定的索引（若為行鎖，通常為索引名）lock_space,       -- 表空間IDlock_page,        -- 數據頁號lock_rec,         -- 數據行號lock_data          -- 鎖定的行數據（如主鍵值）
FROM INFORMATION_SCHEMA.INNODB_LOCKS;-- MySQL 8.0+ 版本（改用 performance_schema.data_locks）
SELECTENGINE_LOCK_ID,    -- 鎖IDTHREAD_ID,         -- 持有鎖的線程IDLOCK_MODE,         -- 鎖模式LOCK_TYPE,         -- 鎖類型（ROW、TABLE等）TABLE_SCHEMA,      -- 表所屬數據庫TABLE_NAME,        -- 表名INDEX_NAME,        -- 索引名LOCK_DATA           -- 鎖定的行數據（如主鍵值）
FROM performance_schema.data_locks;

示例輸出（MySQL 8.0+）：

ENGINE_LOCK_ID	THREAD_ID	LOCK_MODE	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME	INDEX_NAME	LOCK_DATA
12345:10:4:3	102	X	ROW	test	accounts	PRIMARY	1
12346:10:4:5	103	S	ROW	test	orders	idx_status	'paid'

說明：

LOCK_MODE=X：表示排他鎖（示例中線程102對accounts表id=1的記錄加了X鎖）。
LOCK_DATA：行鎖通常顯示主鍵值（如1），表鎖或意向鎖可能顯示為NULL。

三、查看鎖等待（`INNODB_LOCK_WAITS`?→ 8.0+ 改為?`performance_schema.data_lock_waits`）

場景：查詢當前鎖等待的阻塞關系

-- MySQL 5.7及之前版本
SELECTrequest_trx_id,     -- 請求鎖的事務ID（等待者）request_lock_id,    -- 請求的鎖IDblock_trx_id,       -- 持有鎖的事務ID（阻塞者）block_lock_id       -- 被持有的鎖ID
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;-- MySQL 8.0+ 版本（改用 performance_schema.data_lock_waits）
SELECTREQUESTING_THREAD_ID,  -- 請求鎖的線程ID（等待者）BLOCKING_THREAD_ID,    -- 持有鎖的線程ID（阻塞者）REQUESTED_LOCK_ID,     -- 請求的鎖IDBLOCKING_LOCK_ID       -- 被持有的鎖ID
FROM performance_schema.data_lock_waits;

示例輸出：

request_trx_id	request_lock_id	block_trx_id	block_lock_id
12345	12345:10:4:3	12346	12346:10:4:3

說明：

事務12345（等待者）?請求鎖定id=1的記錄，但事務12346（阻塞者）?已持有該記錄的X鎖，導致阻塞。
通過此表可快速定位死鎖或鎖競爭的源頭。

四、釋放鎖（`KILL THREAD`）

場景：強制終止阻塞事務（需謹慎！）

通過?INNODB_TRX?找到阻塞事務的線程ID：

SELECT trx_mysql_thread_id FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_id=12346;  -- 假設阻塞者事務ID為12346
-- 輸出：102

終止線程釋放鎖：

KILL 102;  -- 殺死線程ID為102的事務（需SUPER權限）

注意：

直接終止事務可能導致數據不一致或未提交的業務邏輯中斷，僅建議在緊急情況下使用（如死鎖無法自動解決時）。

五、查看鎖等待詳細信息（`SHOW ENGINE INNODB STATUS`）

場景：獲取更詳細的鎖等待日志（包含死鎖檢測信息）

SHOW ENGINE INNODB STATUS\G

關鍵輸出片段：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-05-24 15:35:00 0x7f8a12345678
*** (1) TRANSACTION:
TRANSACTION 12347, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 103, OS thread handle 12345, query id 123456 test 192.168.1.1 root
INSERT INTO accounts (id, balance) VALUES (2, 1000)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 4 n bits 72 index PRIMARY of table `test`.`accounts` trx id 12347 lock_mode X insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 00: len 4; hex 80000002; asc     ;;1: len 6; hex 000000003031; asc    01;;2: len 7; hex b60000019d0110; asc        ;;*** (2) TRANSACTION:
TRANSACTION 12348, ACTIVE 6 sec inserting
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 104, OS thread handle 12346, query id 123457 test 192.168.1.2 root
INSERT INTO accounts (id, balance) VALUES (1, 2000)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 4 n bits 72 index PRIMARY of table `test`.`accounts` trx id 12348 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 00: len 4; hex 80000001; asc     ;;1: len 6; hex 000000003030; asc    00;;2: len 7; hex b60000019d010a; asc        ;;*** (1) AND (2) WAITING FOR EACH OTHER'S LOCKS!

說明：

輸出顯示兩個事務（12347和12348）在插入數據時發生死鎖，互相等待對方持有的鎖。
SHOW ENGINE INNODB STATUS?會打印最近一次死鎖的詳細信息，包括鎖定的表、索引、行數據及事務操作，用于分析死鎖原因。

總結：如何通過系統表診斷鎖問題

第一步：通過?INNODB_TRX?查看是否有事務處于?LOCK WAIT?狀態，定位等待者和阻塞者的線程ID。
第二步：通過?data_locks?或?INNODB_LOCKS?查看具體鎖的類型、鎖定的表和行數據。
第三步：通過?data_lock_waits?或?INNODB_LOCK_WAITS?確認鎖等待的阻塞關系。
第四步：結合?SHOW ENGINE INNODB STATUS?的詳細日志分析死鎖或長時間等待的原因。

通過這些系統表的組合查詢，可以快速定位數據庫中的鎖競爭、死鎖等性能問題，并針對性優化事務邏輯或索引設計。

死鎖問題分析

set transcation_isolation='repeatable-read';
Session_1執行：select * from account where id=1 for update;
Session_2執行：select * from account where id=2 for update;
Session_1執行：select * from account where id=2 for update;
Session_2執行：select * from account where id=1 for update;
查看近期死鎖日志信息：show engine innodb status;

鎖優化實踐

盡可能讓所有數據檢索都通過索引來完成，避免無索引行鎖升級為表鎖
合理設計索引，盡量縮小鎖的范圍
盡可能減少檢索條件范圍，避免間隙鎖
盡量控制事務大小，減少鎖定資源量和時間長度，涉及事務加鎖的sql盡量放在事務最后執行
盡可能用低的事務隔離級別

MVCC多版本并發控制機制

MVCC（多版本并發控制）是MySQL中InnoDB存儲引擎實現讀一致性和并發控制的核心機制，主要用于讀已提交（RC）和可重復讀（RR）隔離級別（默認是RR）。它通過版本鏈和一致性讀視圖避免鎖競爭，提升并發性能。

核心要素

版本鏈
- 每行數據修改時會生成多個版本，通過隱藏字段（trx_id、roll_ptr等）串聯成版本鏈。
- trx_id：修改數據的事務ID（唯一遞增）。
- roll_ptr：指向舊版本數據（存于回滾日志undo log）。
一致性讀視圖（Read View）
- 事務執行普通查詢（非鎖定讀，如SELECT）時，會生成一個快照（讀視圖），記錄當前活躍事務ID列表。
- 通過讀視圖判斷數據版本是否可見，實現無鎖讀。

在MySQL中的應用場景

1.?不同隔離級別下的行為

可重復讀（RR）
- 事務啟動時生成讀視圖，整個事務期間只讀該視圖，保證多次讀取結果一致（避免不可重復讀和幻讀）。
- 例：

-- 事務A（RR隔離級別）
BEGIN;
SELECT * FROM t WHERE id=1; -- 生成讀視圖V1，讀取版本鏈中可見的數據
-- 其他事務修改id=1的數據，生成新版本（trx_id=102）
SELECT * FROM t WHERE id=1; -- 仍用V1，讀取舊版本數據（不可重復讀被禁止）
COMMIT;

讀已提交（RC）
- 每次查詢時重新生成讀視圖，保證每次讀取的是最新已提交數據（可能出現不可重復讀）。
- 例：

-- 事務A（RC隔離級別）
BEGIN;
SELECT * FROM t WHERE id=1; -- 讀視圖V1，讀取舊版本
-- 其他事務提交修改（trx_id=102）
SELECT * FROM t WHERE id=1; -- 重新生成V2，讀取新版本（允許不可重復讀）
COMMIT;

2.?與鎖的配合

快照讀（非阻塞讀）：普通SELECT使用MVCC，不加鎖，讀舊版本數據（一致性讀）。
當前讀（阻塞讀）：加鎖語句（如SELECT ... FOR UPDATE）直接讀最新數據，需等待鎖釋放。
- 例：

-- 事務A（當前讀）
SELECT * FROM t WHERE id=1 FOR UPDATE; -- 讀取最新數據，加行鎖，阻塞其他寫事務

3.?提升并發性能

寫操作（如INSERT/UPDATE/DELETE）生成新版本，讀操作通過MVCC訪問舊版本，避免讀寫阻塞。
場景：高并發讀場景（如電商商品詳情頁），讀不阻塞寫，寫不阻塞讀。

關鍵特性

無鎖讀：普通查詢不阻塞寫事務，反之亦然（除了SERIALIZABLE隔離級別）。
讀一致性：根據隔離級別控制數據可見性，避免臟讀、不可重復讀等問題。
回滾日志管理：舊版本數據存于undo log，由InnoDB自動清理（purge線程），避免日志膨脹。

總結

MVCC是InnoDB實現高并發的基石，通過版本鏈和讀視圖在一致性和并發性間找到平衡。理解其原理有助于優化事務設計（如合理選擇隔離級別）和排查鎖問題（如長時間鎖等待可能因MVCC版本鏈過長導致）。