?
会话依赖关系主要需要理解锁等待相关四张表的关系。
主要逻辑是：先查询当前会话持有哪些事务的锁和依赖哪些事务的锁，通过这两种锁就可以得到一种图关系。
?
一：查询当前会话持有的锁：
?
? 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表就可以得到被当前会话等待的锁被哪些会话占用了。
?
?
下面列出相关表部分表结构：
?
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 |
??
?
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_WEIGHT |
The weight of a transaction, reflecting (but not necessarily the exact count of) the number of rows altered and the number of rows locked by the transaction. To resolve a deadlock,?InnoDB?selects the transaction with the smallest weight as the?“victim”?to rollback. Transactions that have changed non-transactional tables are considered heavier than others, regardless of the number of altered and locked rows. |
TRX_STATE |
Transaction execution state. One of?RUNNING,?LOCK WAIT,?ROLLING BACK?or?COMMITTING. |
TRX_STARTED |
Transaction start time. |
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. |
TRX_WAIT_STARTED |
Time when the transaction started waiting on the lock (if?TRX_STATE?isLOCK WAIT, otherwise?NULL). |
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”. |
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. |
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. |
LOCK_MODE |
Mode of the lock. One of?S,?X,?IS,?IX,?S_GAP,?X_GAP,?IS_GAP,?IX_GAP, or?AUTO_INC?for shared, exclusive, intention shared, intention exclusive row locks, shared and exclusive gap locks, intention shared and intention exclusive gap locks, and auto-increment table level lock, respectively. Refer to the sections?Section?14.2.2.1, “InnoDB Lock Modes”?andSection?14.2.2, “The InnoDB Transaction Model and Locking”?for information on?InnoDBlocking. |
LOCK_TYPE |
Type of the lock. One of?RECORD?or?TABLE?for record (row) level or table level locks, respectively. |
原文：http://labreeze.iteye.com/blog/2233691