會話依賴關系主要需要理解鎖等待相關四張表的關系：

先查詢當前會話持有哪些事務的鎖和依賴哪些事務的鎖，通過這兩種鎖就可以得到一種圖關系。

一：查詢當前會話持有的鎖：

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_MYSQL_THREAD_ID

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

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.

總結

以上是生活随笔為你收集整理的mysql 锁 会话_MySQL会话锁等待关系原理的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。