<b>#mysql 鎖之間的依賴關系資訊sql如下【打開注釋部分的sql是查找最源頭鎖的thread_id線程id及關系】</b>
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;
<b>關聯對象的介紹：</b>
-- 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;
+-------------------+-------------+------+-----+---------+-------+
| 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
-- innodb_trx ## 目前運作的所有事務
> desc innodb_trx ;
+----------------------------+---------------------+------+-----+---------------------+-------+
| 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 | |#
擴充：
【源于本人筆記】 若有書寫錯誤，表達錯誤，請指正...
![SQL優化SQL語句優化的目的[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)