【MySQL】鎖——檢視當前資料庫鎖請求的三種方法

MySQL版本：5.6.21

MySQL提供了檢視當前資料庫鎖請求的三種方法：

1. show  full  processlist命令
  觀察state和info列

2. show engine  innodb status\G 命令
  檢視 TRANSACTIONS 部分和 LATEST DETECTED DEADLOCK 兩個部分

3. information_shcema下的三張表（透過這三張表可以更新監控當前事物並且分析存在的鎖問題）
 —— innodb_trx （

列印innodb核心中的當前活躍（ACTIVE）事務）

 —— innodb_locks 

（ 列印當前狀態產生的innodb鎖 僅在有鎖等待時列印）

 —— innodb_lock_waits

（列印當前狀態產生的innodb鎖等待 僅在有鎖等待時列印）

 1) innodb_trx表結構說明 （摘取最能說明問題的8個欄位）

欄位名	說明
trx_id	innodb儲存引擎內部唯一的事物ID
trx_state	當前事物狀態（running和lock wait兩種狀態）
trx_started	事物的開始時間
trx_requested_lock_id	等待事物的鎖ID，如trx_state的狀態為Lock wait，那麼該值帶表當前事物等待之前事物佔用資源的ID，若trx_state不是Lock wait 則該值為NULL
trx_wait_started	事物等待的開始時間
trx_weight	事物的權重，在innodb儲存引擎中，當發生死鎖需要回滾的時，innodb儲存引擎會選擇該值最小的進行回滾
trx_mysql_thread_id	mysql中的執行緒id, 即show  processlist顯示的結果
trx_query	事物執行的SQL語句

 2）innodb_locks表結構說明

欄位名	說明
lock_id	鎖的ID
lock_trx_id	事物的ID
lock_mode	鎖的模式（S鎖與X鎖兩種模式）
lock_type	鎖的型別 表鎖還是行鎖（RECORD）
lock_table	要加鎖的表
lock_index	鎖住的索引
lock_space	鎖住物件的space id
lock_page	事物鎖定頁的數量，若是表鎖則該值為NULL
lock_rec	事物鎖定行的數量，若是表鎖則該值為NULL
lock_data	事物鎖定記錄主鍵值，若是表鎖則該值為NULL（此選項不可信）

 3）innodb_lock_waits表結構說明

欄位名	說明
requesting_trx_id	申請鎖資源的事物ID
requested_lock_id	申請的鎖的ID
blocking_trx_id	阻塞其他事物的事物ID
blocking_lock_id	阻塞其他鎖的鎖ID

可以根據這三張表進行聯合查詢，得到更直觀更清晰的結果，可以參考如下SQL（可根據自己的分析習慣適進行調整）
 select
r.trx_isolation_level,
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id  waiting_trx_thread,
r.trx_state  waiting_trx_state,
lr.lock_mode waiting_trx_lock_mode,
lr.lock_type  waiting_trx_lock_type,
lr.lock_table  waiting_trx_lock_table,
lr.lock_index  waiting_trx_lock_index,
r.trx_query  waiting_trx_query,
b.trx_id  blocking_trx_id,
b.trx_mysql_thread_id  blocking_trx_thread,
b.trx_state  blocking_trx_state,
lb.lock_mode blocking_trx_lock_mode,
lb.lock_type  blocking_trx_lock_type,
lb.lock_table  blocking_trx_lock_table,
lb.lock_index  blocking_trx_lock_index,
b.trx_query  blocking_query
from  information_schema.innodb_lock_waits  w
inner  join  information_schema.innodb_trx b
on  b.trx_id=w.blocking_trx_id
inner  join  information_schema.innodb_trx  r
on  r.trx_id=w.requesting_trx_id
inner  join   information_schema.innodb_locks  lb
on  lb.lock_trx_id=w.blocking_trx_id
inner  join   information_schema.innodb_locks  lr
on  lr.lock_trx_id=w.requesting_trx_id\G;
*************************** 1. row ***************************
    trx_isolation_level: REPEATABLE READ
         waiting_trx_id: 2900247
     waiting_trx_thread: 1070
      waiting_trx_state: LOCK WAIT
  waiting_trx_lock_mode: S
  waiting_trx_lock_type: RECORD
 waiting_trx_lock_table: `jiang_test`.`test`
 waiting_trx_lock_index: PRIMARY
      waiting_trx_query: select * from test where id=3 lock in share mode
        blocking_trx_id: 2900241
    blocking_trx_thread: 1137
     blocking_trx_state: RUNNING
 blocking_trx_lock_mode: X
 blocking_trx_lock_type: RECORD
blocking_trx_lock_table: `jiang_test`.`test`
blocking_trx_lock_index: PRIMARY
         blocking_query: NULL
1 row in set (0.01 sec)