MySQL会话锁等待关系原理

 

会话依赖关系主要需要理解锁等待相关四张表的关系。

主要逻辑是：先查询当前会话持有哪些事务的锁和依赖哪些事务的锁，通过这两种锁就可以得到一种图关系。

 

一：查询当前会话持有的锁：

 

  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	MySQL thread ID. Can be used for joining with PROCESSLIST on ID. See Section 14.12.2.3.1, “Potential Inconsistency with PROCESSLIST Data”.

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.