MySQL查詢鎖之間依賴關係的資訊和最源頭鎖的thread_id號及關係

shy丶gril發表於2016-05-24
MySqlthread

標題： MySQL 查詢鎖之間依賴關係的資訊和最源頭鎖的thread_id號及關係

作者：ｌōττéｒｙ©版權所有[文章允許轉載,但必須以連結方式註明源地址,否則追究法律責任.]

#mysql 鎖之間的依賴關係資訊sql如下【開啟註釋部分的sql是查詢最源頭鎖的thread_id執行緒id及關係】

     SELECT distinct b.trx_id blocking_trx_id,
                     b.trx_mysql_thread_id 源頭鎖thread_id,
                     SUBSTRING(p. HOST, 1, INSTR(p. HOST, `:`) – 1) blocking_host,
                     SUBSTRING(p. HOST, INSTR(p. HOST, `:`) + 1) blocking_port,
                     IF(p.COMMAND = `Sleep`, p.TIME, 0) idel_in_trx,
                     b.trx_query blocking_query,
                     r.trx_id waiting_trx_id,
                     r.trx_mysql_thread_id waiting_thread,
                     TIMESTAMPDIFF(SECOND, r.trx_wait_started, CURRENT_TIMESTAMP) wait_time,
                     r.trx_query waiting_query,
                     l.lock_table waiting_table_lock
       FROM information_schema.INNODB_LOCKS l
       LEFT JOIN information_schema.INNODB_LOCK_WAITS w
         ON w.requested_lock_id = l.lock_id
       LEFT JOIN information_schema.INNODB_TRX b
         ON b.trx_id = w.blocking_trx_id
       LEFT JOIN information_schema.INNODB_TRX r
         ON r.trx_id = w.requesting_trx_id
       LEFT JOIN information_schema. PROCESSLIST p
         ON p.ID = b.trx_mysql_thread_id
     /*JOIN (SELECT blocking_trx_id — 查詢最源頭的trx_id
           FROM information_schema.INNODB_LOCK_WAITS ilw
          WHERE blocking_trx_id NOT IN
                (SELECT requesting_trx_id
                   FROM information_schema.INNODB_LOCK_WAITS)) c
     ON c.blocking_trx_id = b.trx_id */
     ORDER BY wait_time DESC;

關聯物件的介紹：
— innodb_locks ## 當前出現的鎖 
mysql > desc innodb_locks;
+————-+———————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————-+———————+——+—–+———+——-+
| lock_id | varchar(81) | NO | | | |#鎖ID
| lock_trx_id | varchar(18) | NO | | | |#擁有鎖的事務ID
| lock_mode | varchar(32) | NO | | | |#鎖模式
| lock_type | varchar(32) | NO | | | |#鎖型別
| lock_table | varchar(1024) | NO | | | |#被鎖的表
| lock_index | varchar(1024) | YES | | NULL | |#被鎖的索引
| lock_space | bigint(21) unsigned | YES | | NULL | |#被鎖的表空間號
| lock_page | bigint(21) unsigned | YES | | NULL | |#被鎖的頁號
| lock_rec | bigint(21) unsigned | YES | | NULL | |#被鎖的記錄號
| lock_data | varchar(8192) | YES | | NULL | |#被鎖的資料
+————-+———————+——+—–+———+——-+
mysql>
— innodb_lock_waits ## 鎖等待的對應關 
mysql > desc innodb_lock_waits;
+——————-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——————-+————-+——+—–+———+——-+
| requesting_trx_id | varchar(18) | NO | | | |#請求鎖的事務ID
| requested_lock_id | varchar(81) | NO | | | |#請求鎖的鎖ID
| blocking_trx_id | varchar(18) | NO | | | |#當前擁有鎖的事務ID
| blocking_lock_id | varchar(81) | NO | | | |#當前擁有鎖的鎖ID
+——————-+————-+——+—–+———+——-+
mysql>
— innodb_trx ## 當前執行的所有事務  
> desc innodb_trx ;
+—————————-+———————+——+—–+———————+——-+
| Field | Type | Null | Key | Default | Extra |
+—————————-+———————+——+—–+———————+——-+
| trx_id | varchar(18) | NO | | | |#事務ID
| trx_state | varchar(13) | NO | | | |#事務狀態：
| trx_started | datetime | NO | | 0000-00-00 00:00:00 | |#事務開始時間；
| trx_requested_lock_id | varchar(81) | YES | | NULL | |#innodINNODB_TRXb_locks.lock_id
| trx_wait_started | datetime | YES | | NULL | |#事務開始等待的時間
| trx_weight | bigint(21) unsigned | NO | | 0 | |#
| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |#事務執行緒ID
| trx_query | varchar(1024) | YES | | NULL | |#具體SQL語句
| trx_operation_state | varchar(64) | YES | | NULL | |#事務當前操作狀態
| trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |#事務中有多少個表被使用
| trx_tables_locked | bigint(21) unsigned | NO | | 0 | |#事務擁有多少個鎖
| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#
| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |#事務鎖住的記憶體大小（B）
| trx_rows_locked | bigint(21) unsigned | NO | | 0 | |#事務鎖住的行數
| trx_rows_modified | bigint(21) unsigned | NO | | 0 | |#事務更改的行數
| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |#事務併發票數
| trx_isolation_level | varchar(16) | NO | | | |#事務隔離級別
| trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性檢查
| trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外來鍵檢查
| trx_last_foreign_key_error | varchar(256) | YES | | NULL | |#最後的外來鍵錯誤
| trx_adaptive_hash_latched | int(1) | NO | | 0 | |#
| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |#
+—————————-+———————+——+—–+———————+——-+
mysql>
     擴充套件：
  【源於本人筆記】 若有書寫錯誤，表達錯誤，請指正… 


相關文章