詳細瞭解INNODB_TRX、INNODB_LOCKs、INNODB_LOCK_waits、PROCESSLIST表

# 當前執行的所有事務

SELECT * FROM information_schema.INNODB_TRX;

# 當前出現的鎖

SELECT * FROM information_schema.INNODB_LOCKs; 

# 鎖等待的對應關係

SELECT * FROM information_schema.INNODB_LOCK_waits;

# PROCESSLIST表記錄了每個MySql執行緒的使用者，地址以及操作的db等其他資訊。

①

SELECT * FROM information_schema.PROCESSLIST;

或者

②

SHOW PROCESSLIST

一、information_schema.INNODB_TRX表詳情

innodb_trx 表的每個欄位解釋如下：

trx_id：事務ID。
trx_state：事務狀態，有以下幾種狀態：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。
trx_started：事務開始時間。
trx_requested_lock_id：事務當前正在等待鎖的標識，可以和 INNODB_LOCKS 表 JOIN 以得到更多詳細資訊。
trx_wait_started：事務開始等待的時間。
trx_weight：事務的權重。
trx_mysql_thread_id：事務執行緒 ID，可以和 PROCESSLIST 表 JOIN。
trx_query：事務正在執行的 SQL 語句。
trx_operation_state：事務當前操作狀態。
trx_tables_in_use：當前事務執行的 SQL 中使用的表的個數。
trx_tables_locked：當前執行 SQL 的行鎖數量。
trx_lock_structs：事務保留的鎖數量。
trx_lock_memory_bytes：事務鎖住的記憶體大小，單位為 BYTES。
trx_rows_locked：事務鎖住的記錄數。包含標記為 DELETED，並且已經儲存到磁碟但對事務不可見的行。
trx_rows_modified：事務更改的行數。
trx_concurrency_tickets：事務併發票數。
trx_isolation_level：當前事務的隔離級別。
trx_unique_checks：是否開啟唯一性檢查的標識。
trx_foreign_key_checks：是否開啟外來鍵檢查的標識。
trx_last_foreign_key_error：最後一次的外來鍵錯誤資訊。
trx_adaptive_hash_latched：自適應雜湊索引是否被當前事務鎖住的標識。
trx_adaptive_hash_timeout：是否立刻放棄為自適應雜湊索引搜尋 LATCH 的標識。

二、information_schema.INNODB_LOCKs表詳情

innodb_locks 表的每個欄位解釋如下

lock_id：鎖 ID。
lock_trx_id：擁有鎖的事務 ID。可以和 INNODB_TRX 表 JOIN 得到事務的詳細資訊。
lock_mode：鎖的模式。有如下鎖型別：行級鎖包括：S、X、IS、IX，分別代表：共享鎖、排它鎖、意向共享鎖、意向排它鎖。表級鎖包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分別代表共享間隙鎖、排它間隙鎖、意向共享間隙鎖、意向排它間隙鎖和自動遞增鎖。
lock_type：鎖的型別。RECORD 代表行級鎖，TABLE 代表表級鎖。
lock_table：被鎖定的或者包含鎖定記錄的表的名稱。
lock_index：當 LOCK_TYPE=’RECORD’ 時，表示索引的名稱；否則為 NULL。
lock_space：當 LOCK_TYPE=’RECORD’ 時，表示鎖定行的表空間 ID；否則為 NULL。
lock_page：當 LOCK_TYPE=’RECORD’ 時，表示鎖定行的頁號；否則為 NULL。
lock_rec：當 LOCK_TYPE=’RECORD’ 時，表示一堆頁面中鎖定行的數量，亦即被鎖定的記錄號；否則為 NULL。
lock_data：當 LOCK_TYPE=’RECORD’ 時，表示鎖定行的主鍵；否則為NULL。

三、information_schema.INNODB_LOCK_waits表詳情

innodb_lock_waits 表的每個欄位解釋如下：

requesting_trx_id：請求事務的 ID。
requested_lock_id：事務所等待的鎖定的 ID。可以和 INNODB_LOCKS 表 JOIN。
blocking_trx_id：阻塞事務的 ID。
blocking_lock_id：某一事務的鎖的 ID，該事務阻塞了另一事務的執行。可以和 INNODB_LOCKS 表 JOIN。

四、INFORMATION_SCHEMA.PROCESSLIST表詳情

PROCESSLIST 表的每個欄位解釋如下：

id:標識ID。這與在SHOW PROCESSLIST語句的Id列、Performance Schema threads表的PROCESSLIST_ID列中顯示的值型別相同，並由CONNECTION_ID()函式返回
user:發出該語句的mysql使用者。
host：發出該語句的客戶機的主機名(系統使用者除外，沒有主機)。
db：預設資料庫。
command：執行緒正在執行的命令的型別。
time：執行緒處於當前狀態的時間(以秒為單位)。
state：指示執行緒正在執行的操作、事件或狀態。
info：執行緒正在執行的語句，如果沒有執行任何語句，則為NULL。    

五、事務一直處於RUNNING狀態解決辦法

1、需要區分的是 Lock wait timeout exceeded 與  Dead Lock 是不一樣。

• Lock wait timeout exceeded：後提交的事務等待前面處理的事務釋放鎖，但是在等待的時候超過了mysql的鎖等待時間，就會引發這個異常。
• Dead Lock：兩個事務互相等待對方釋放相同資源的鎖，從而造成的死迴圈，就會引發這個異常。

2、還有一個要注意的是 innodb_lock_wait_timeout 與 lock_wait_timeout 也是不一樣的。

• innodb_lock_wait_timeout：innodb的dml操作的行級鎖的等待時間。
• lock_wait_timeout：資料結構ddl操作的鎖的等待時間。

定位步驟如下：

（1）使用如下語句檢視事務，找到狀態為RUNNING的記錄

SELECT * FROM information_schema.INNODB_TRX;

（2）通過trx_mysql_thread_id去查詢information_schema.processlist表，找到執行事務的客戶端請求的SQL執行緒

select * from information_schema.PROCESSLIST WHERE ID = 'xxx';

（3）檢視到埠和host以後，再到對應的伺服器檢視相關的應用和日誌

netstat -nlatp | grep 23452
ps -eaf | grep 12059

（4）如果無法定位，此時我們需要從performance_schema表尋找特定執行緒的資訊

# 檢視事件
select * from performance_schema.events_statements_current

（5）根據我們拿到的執行緒id去查，可以獲取到具體的執行sql

select * from performance_schema.events_statements_current
where THREAD_ID in (select THREAD_ID from performance_schema.threads where PROCESSLIST_ID=15844)