会话依赖关系主要需要理解锁等待相关四张表的关系。
主要逻辑是:先查询当前会话持有哪些事务的锁和依赖哪些事务的锁,通过这两种锁就可以得到一种图关系。
一:查询当前会话持有的锁:
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.
INFORMATION_SCHEMA Name
SHOW Name
Remarks
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
Column name
Description
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
3.The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table
Table 21.6 INNODB_LOCK_WAITS Columns
Column name
Description
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
Column name
Description
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.