mysql 锁 会话_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.

原文：http://labreeze.iteye.com/blog/2233691