会话依赖关系主要需要理解锁等待相关四张表的关系：
先查询当前会话持有哪些事务的锁和依赖哪些事务的锁，通过这两种锁就可以得到一种图关系。
一：查询当前会话持有的锁：
? 1.查询当前会话processlist.id?
? 2.processlist.id对应innodb_trx表中的TRX_MYSQL_THREAD_ID，这样就可以查询到当前会话的事务id
? 3.到lock_waits中查询blocking_trx_id 为当前会话的事务id,这样就获得了当前会话的持有lock_id
? 4.根据lock_id到innodb_lock可以查询出当前会话持有锁信息。
? ?其实如果只需要查询会话的依赖的关系不需要知道锁信息的话，到第三步就可以了，通过第三步的REQUESTING_TRX_ID来反查innodb_trx表获取trx_id，反查processlist表就可以得到被当前会话阻塞的会话列表。
?
二：查询当前会话正等待的锁
?1.查询当前会话processlist.id?
?2.processlist.id对应innodb_trx表中的TRX_MYSQL_THREAD_ID，这样就可以查询到当前会话的事务id
?3.到lock_waits中查询REQUESTING_TRX_ID为当前会话的事务id,这样就获得了当前会话的正等待lock_id
?4.根据lock_id到innodb_lock可以查询出当前会话正在等待的锁信息。
?同样，其实如果只需要查询会话的依赖的关系不需要知道锁信息的话，到第三步就可以了，通过第三步的blocking_trx_id来反查innodb_trx表获取trx_id，反查processlist表就可以得到被当前会话等待的锁被哪些会话占用了。
?
?
下面列出相关表部分表结构：
1.INFORMATION_SCHEMA.PROCESSLIST?
?
The?PROCESSLIST?table provides information about which threads are running.
ID | Id | MySQL extension |
USER | User | MySQL extension |
HOST | Host | MySQL extension |
DB | db | MySQL extension |
COMMAND | Command | MySQL extension |
TIME | Time | MySQL extension |
STATE | State | MySQL extension |
INFO | Info | MySQL extension |
?
?
2.The INFORMATION_SCHEMA INNODB_TRX Table
?
Table?21.4?INNODB_TRX Columns
TRX_ID | Unique transaction ID number, internal to?InnoDB. (Starting in MySQL 5.6, these IDs are not created for transactions that are read-only and non-locking. See?Section?8.5.3, “Optimizing InnoDB Read-Only Transactions”for details.) |
?
TRX_MYSQL_THREAD_ID | MySQL thread ID. Can be used for joining with?PROCESSLIST?on?ID. See?Section?14.12.2.3.1, “Potential Inconsistency with PROCESSLIST Data”. |
TRX_REQUESTED_LOCK_ID | ID of the lock the transaction is currently waiting for (if?TRX_STATE?isLOCK WAIT, otherwise?NULL). Details about the lock can be found by joining with?INNODB_LOCKS?on?LOCK_ID. |
?
3.The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table
Table?21.6?INNODB_LOCK_WAITS Columns
REQUESTING_TRX_ID | ID of the requesting transaction. |
REQUESTED_LOCK_ID | ID of the lock for which a transaction is waiting. Details about the lock can be found by joining with?INNODB_LOCKS?on?LOCK_ID. |
BLOCKING_TRX_ID | ID of the blocking transaction. |
BLOCKING_LOCK_ID | ID of a lock held by a transaction blocking another transaction from proceeding. Details about the lock can be found by joining with?INNODB_LOCKS?on?LOCK_ID. |
?
4.The INFORMATION_SCHEMA INNODB_LOCKS Table
Table?21.5?INNODB_LOCKS Columns
?
? ?LOCK_ID | Unique lock ID number, internal to?InnoDB. Treat it as an opaque string. Although?LOCK_IDcurrently contains?TRX_ID, the format of the data in?LOCK_ID?is not guaranteed to remain the same in future releases. Do not write programs that parse the?LOCK_ID?value. |
LOCK_TRX_ID | ID of the transaction holding this lock. Details about the transaction can be found by joining with?INNODB_TRX?on?TRX_ID. ? ? ? ? ? ? ? ? ? ? ? ? ? ?? |
?
?