資料庫:mysql5.6 innoDB鎖等待

在information_schema庫中新增了三個關於鎖的表,分別是innodb_trx,innodb_locks和innodb_lock_waits

select * from information_schema.INNODB_TRX;

:記錄當前執行的所有事務

select * from information_schema.INNODB_LOCKS;

:記錄當前出現的鎖

select * from information_schema.INNODB_LOCK_WAITS;

:記錄鎖等待的對應關係

innodb_trx表結構

trx_id:事務ID
trx_state:事務狀態,有以下幾種狀態:running,lock wait,rolling back和committing
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_lock_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 的標識。

檢視innodb_locks表結構
lock_id:鎖ID
lock_trx_id:擁有鎖的事務 ID。可以和 INNODB_TRX 表 JOIN 得到事務的詳細資訊。
lock_mode:鎖的模式。如下的鎖型別:行鎖包括:S、X、LS、LX分別代表:共享鎖、排它鎖、意向共享鎖、意向排它鎖。表級鎖包括: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。

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。

測試
再mysql命令列的模式下進行。

1、建立一個user表,插入資料。
這裡寫圖片描述

2、模擬鎖等待
這裡寫圖片描述

3、執行三條查詢語句,檢視是否有鎖現象

select * from information_schema.innodb_trx \G;
select * from information_schema.innodb_locks \G
SELECT * FROM information_schema.innodb_lock_waits \G

這裡寫圖片描述

沒有發生鎖等待, innodb_locks表 和 innodb_lock_waits 表都沒有資料

4、另起一個連線,執行update操作
這裡寫圖片描述

5、再次執行查詢鎖的語句,發現有鎖

這裡寫圖片描述

6、解決鎖等待的方法

使用show processlist;找到等待的id號,執行 kill Id 命令來解決鎖等待。
或者執行rollback或者commit命令
這裡寫圖片描述

參考地址:https://dbarobin.com/2015/01/27/innodb-lock-wait-under-mysql-5.5/