TIPS

本文基于MySQL 8.0.22

分析鎖的三種方式

方式1：

show status like '%innodb_row_lock%';

可查看行鎖相關的統計信息

方式2：

SHOW ENGINE INNODB STATUS;

關注結果中 TRANSACTIONS 段落

方式3：利用鎖、事務相關的表

MySQL 5.7

MySQL 8.xINFORMATION_SCHEMA.INNODB_TRX 官方文檔

INFORMATION_SCHEMA.INNODB_TRX 官方文檔

INFORMATION_SCHEMA.INNODB_LOCKS 官方文檔

PERFORMANCE.DATA_LOCKS 官方文檔

INFORMATION_SCHEMA.INNODB_LOCK_WAITS 官方文檔

PERFORMANCE.DATA_LOCKS_WAITS 官方文檔

鎖相關的表

MySQL主要有三張和鎖有關的表，不同的版本，表還不太一樣，這里我列出來了：

MySQL 5.7

MySQL 8.x

作用INFORMATION_SCHEMA.INNODB_TRX 官方文檔

INFORMATION_SCHEMA.INNODB_TRX 官方文檔

當前事務的執行情況

INFORMATION_SCHEMA.INNODB_LOCKS 官方文檔

PERFORMANCE.DATA_LOCKS 官方文檔

鎖相關信息

INFORMATION_SCHEMA.INNODB_LOCK_WAITS 官方文檔

PERFORMANCE.DATA_LOCKS_WAITS 官方文檔

鎖等待信息

INFORMATION_SCHEMA.INNODB_TRX表結構

TRX_ID：InnoDB存儲引擎內部唯一的事務ID

TRX_WEIGHT：事務的權重，反映了一個事務修改和鎖住的行數。在InnoDB存儲引擎中，當發生死鎖需要回滾時，InnoDB存儲引擎會選擇該值最小事務的進行回滾

TRX_STATE：當前事務的狀態

TRX_STARTED：事務的開始時間

TRX_REQUESTED_LOCK_ID：當trx_state的狀態為LOCK WAIT時，表示當前事務正在等待的鎖ID，否則記為NULL。如果想獲得更詳細的信息，可將該字段和PERFORMANCE.DATA_LOCKS聯系起來

TRX_WAIT_STARTED：當trx_state的狀態為LOCK WAIT時，表示當前事務開始等待的時間，否則記為NULL

TRX_MYSQL_THREAD_ID：MySQL中的線程ID，SHOW PROCESSLIST顯示的結果

TRX_QUERY：事務運行的SQL語句

TRX_OPERATION_STATE：事務當前的操作

TRX_TABLES_IN_USE：事務使用的表的個數

TRX_TABLES_LOCKED：當前SQL語句中擁有行鎖的表數目

TRX_LOCK_STRUCTS：事務保留的鎖數目

TRX_LOCK_MEMORY_BYTES：內存中此事務的鎖占用的內存總大小

TRX_ROWS_LOCKED：此事務大約鎖定的行數。這個值可能包含物理存在的、已標記為刪除但對事務不可見的記錄

TRX_ROWS_MODIFIED：此事務中修改和插入記錄的數目

TRX_CONCURRENCY_TICKETS：表示一個事務被swap out之前可以做多少工作，由innodb_concurrency_tickets參數決定

TRX_ISOLATION_LEVEL：當前事務的隔離級別

TRX_UNIQUE_CHECKS：當前事務是否開啟了唯一性檢查

TRX_FOREIGN_KEY_CHECKS：當前事務是否開啟了外鍵檢查

TRX_LAST_FOREIGN_KEY_ERROR：展示最后一個外鍵錯誤的詳細錯誤信息

TRX_ADAPTIVE_HASH_LATCHED：自適應hash索引是否被當前事務鎖定

TRX_ADAPTIVE_HASH_TIMEOUT：是否要立即放棄搜索自適應hash的鎖，還是繼續保留。如果沒有自適應hash索引爭用時，此值是0，爭用期間則倒計時到0，并在語句在每次行查找之后立即釋放鎖

TRX_IS_READ_ONLY：值1表示事務是只讀的

TRX_AUTOCOMMIT_NON_LOCKING：值為1表示事務是一個沒有使用FOR UPDATE或LOCK IN SHARE MODE子句的SELECT語句，當此值和TRX_IS_READ_ONLY都為1時，innodb會優化事務從而減少更改表事務相關的開銷

TRX_SCHEDULE_WEIGHT：由爭用感知事務調度(CATS)算法分配給等待鎖的事務的事務調度權重，值越大權重越大，此值僅為TRX_STATE字段是LOCK WAIT的事務計算，否則為NULL

PERFORMANCE.DATA_LOCKS表結構

ENGINE：持有或請求鎖定的存儲引擎

ENGINE_LOCK_ID：存儲引擎持有或請求的鎖的ID，鎖ID格式是內部的，隨時可能更改。

ENGINE_TRANSACTION_ID：請求鎖定的事務存儲引擎內部ID，可以將其視為鎖的所有者

THREAD_ID：對應事務的線程ID，如果需要獲取更詳細的信息，需要關聯threads表的THREAD_ID

EVENT_ID：指明造成鎖的EVENT_ID，THREAD_ID+EVENT_ID對應parent EVENT，可以在以下幾張表內獲得信息

events_waits_xx表查看等待事件

events_stages_xxx查看到了哪個階段

events_statements_xx表查看對應的SQL語句

events_transactions_current對應查看事務信息

OBJECT_SCHEMA：對應鎖表的schema名稱

OBJECT_NAME：對應鎖的表名

PARTITION_NAME：對應鎖的分區名

SUBPARTITION_NAME：對應鎖的子分區名

INDEX_NAME：鎖對應的索引名稱，InnoDB表不會為NULL

OBJECT_INSTANCE_BEGIN：鎖對應的內存地址

LOCK_TYPE：對應的鎖類型，對InnoDB而言，可為表鎖或者行鎖

LOCK_MODE：鎖模式，對應值可能為S[,GAP], X[, GAP], IS[,GAP], IX[,GAP], AUTO_INC和UNKNOWN

LOCK_STATUS：鎖狀態，可能為GRANTED或者WAITING

LOCK_DATA：鎖對應的數據，例如如果鎖定的是主鍵，那么該列對應的就是加鎖的主鍵值

PERFORMANCE.DATA_LOCKS_WAITS表結構

ENGINE：請求的鎖的引擎

REQUESTING_ENGINE_LOCK_ID：請求的鎖在存儲引擎中的鎖ID

REQUESTING_ENGINE_TRANSACTION_ID：請求鎖的事務對應的事務ID

REQUESTING_THREAD_ID：請求鎖的線程ID

REQUESTING_EVENT_ID：請求鎖的EVENT ID

REQUESTING_OBJECT_INSTANCE_BEGIN：請求的鎖的內存地址

BLOCKING_ENGINE_LOCK_ID：阻塞的鎖的ID，對應data_locks表的ENGINE_LOCK_ID列

BLOCKING_ENGINE_TRANSACTION_ID：鎖阻塞的事務ID

BLOCKING_THREAD_ID：鎖阻塞的線程ID

BLOCKING_EVENT_ID：鎖阻塞的EVENT ID

BLOCKING_OBJECT_INSTANCE_BEGIN：阻塞的鎖內存地址

實操

準備

步驟

Session 1

Session 2

Session 31

set innodb_lock_wait_timeout = 10000;

set innodb_lock_wait_timeout = 10000;

set innodb_lock_wait_timeout = 10000;

2

BEGIN;

SELECT * FROM test_isolation where id = 1 FOR UPDATE;

-

-

3

-

SELECT * FROM test_isolation where id >= 1 FOR UPDATE;

-

4

-

-

SELECT * FROM test_isolation where id <=1 FOR UPDATE;

SHOW ENGINE INNODB STATUS;

...

------------

TRANSACTIONS

------------

Trx id counter 2548

Purge done for trx's n:o < 2528 undo n:o < 0 state: running but idle

History list length 0

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 562948624083704, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 562948624079424, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 562948624078568, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 2547, ACTIVE 13 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 4157, OS thread handle 281473501401136, query id 23214 localhost root executing

SELECT * FROM test_isolation where id <=1 FOR UPDATE

------- TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 19 page no 4 n bits 72 index PRIMARY of table `test`.`test_isolation` trx id 2547 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 80000001; asc ;;

1: len 6; hex 00000000097f; asc ;;

2: len 7; hex 810000011b0110; asc ;;

3: len 5; hex 7465737431; asc test1;;

...

show status like ‘innodb_row_lock%’;

mysql> show status like 'innodb_row_lock%';

+-------------------------------+----------+

| Variable_name | Value |

+-------------------------------+----------+

| Innodb_row_lock_current_waits | 2 |

| Innodb_row_lock_time | 24967703 |

| Innodb_row_lock_time_avg | 567447 |

| Innodb_row_lock_time_max | 6453606 |

| Innodb_row_lock_waits | 44 |

+-------------------------------+----------+

5 rows in set (0.08 sec)

Innodb_row_lock_current_waits：當前正在等待鎖的事務數量

Innodb_row_lock_time：從系統啟動到現在發生鎖定的總時間

Innodb_row_lock_time_avg：從系統啟動到現在發生鎖等待的平均時間

Innodb_row_lock_time_max：從系統啟動到現在發生鎖等待的最大時間

Innodb_row_lock_waits：從系統啟動到現在發生等待的次數

查看事務的情況

select *

from information_schema.INNODB_TRX;

鎖的情況

select *

from performance_schema.data_locks;

里面可以看到鎖的情況，例如鎖的類型、狀態等信息

鎖的等待情況

select *

from performance_schema.data_lock_waits;

事務與鎖情況分析

用如下命令，可分析事務等待狀況：

SELECT

r.trx_id waiting_trx_id,

r.trx_mysql_thread_id waiting_thread,

r.trx_query waiting_query,

b.trx_id blocking_trx_id,

b.trx_mysql_thread_id blocking_thread,

b.trx_query blocking_query

FROM performance_schema.data_lock_waits w

INNER JOIN information_schema.innodb_trx b

ON b.trx_id = w.blocking_engine_transaction_id

INNER JOIN information_schema.innodb_trx r

ON r.trx_id = w.requesting_engine_transaction_id;

或者簡單一點，用如下命令，也可達到相同效果：

SELECT

waiting_trx_id,

waiting_pid,

waiting_query,

blocking_trx_id,

blocking_pid,

blocking_query

FROM sys.innodb_lock_waits;

結果如下：

waiting_trx_id

waiting_thread

waiting_query

blocking_trx_id

blocking_thread

blocking_query2486

4017

SELECT * FROM test_isolation where id <=1 FOR UPDATE

2484

4019

NULL

2486

4017

SELECT * FROM test_isolation where id <=1 FOR UPDATE

2485

4018

SELECT * FROM test_isolation where id >= 1 FOR UPDATE

2485

4018

SELECT * FROM test_isolation where id >= 1 FOR UPDATE

2484

4019

NULL

分析其中的waiting_query 和 blocking_query字段，可知：

Session 2(trx id 2485, thread id 4018)、Session 3(trx id 2486, thread id 4017)都在等待Session 1(trx id 2484, thread id 4019)

Session 3同時在等待Session 1及Session 2

殺死鎖(強制釋放掉鎖)

kill {processlist id}

例如，processlist id可以：

從 show processlist 結果中獲取

information_schema.innodb_trx表的trx_mysql_thread_id字段

上面表格中的waiting_thread、blocking_thread

sys.innodb_lock_waits視圖的blocking_pid字段

解決blocking_query結果是NULL的問題

如果發出命令的Session空閑時，information_schema.innodb_trx表的trx_query字段會記錄NULL。

因此，上面的Session 1的blocking_query字段值是NULL，可用如下步驟找到其執行的SQL。

information_schema.innodb_trx表的trx_mysql_thread_id字段(或sys.innodb_locak_waits表的blocking_pid字段)是被阻塞事務的processlist ID

通過processlist ID到performance_schema.threads表查詢被阻塞事務的THREAD_ID

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = {上一步得到的PROCESSLIST_ID};

使用THREAD_ID，到performance_schema.events_statements_current表中查詢指定THREAD_ID的數據(該線程最新執行的命令)，例如：

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current

WHERE THREAD_ID = {上一步獲得的THREAD_ID}

如果通過上一條SQL未能查到數據，那可以到performance_schema.events_statements_history表中查詢指定THREAD_ID的數據(該線程最近執行的10條命令)

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history

WHERE THREAD_ID = 28 ORDER BY EVENT_ID;

參考文檔

本文首發

總結

以上是生活随笔為你收集整理的mysql平均锁_MySQL锁情况分析的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。