<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=)