mysql 观察锁信息_MYSQL关于锁信息的“秘密”开关

通常情况下，show innodb statusG所看到的锁的信息比较少，特别是无法看出哪个会话占用了什么锁。

比如：

mysql> select * from test;

+---+------+

| a | b |

+---+------+

| 1 | cba |

| 2 | abc |

+---+------+

2 rows in set (0.00 sec)

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> update test set b='ccccc' where

a=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

另一会话中：

mysql> show innodb statusG

*************************** 1. row

***************************

....

....

------------

TRANSACTIONS

------------

Trx id counter 0 72004903

Purge done for trx's n:o < 0 72004900 undo n:o

< 0 0

History list length 26

Total number of lock structs in row lock hash table 1

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0 0, not started, process no 30615, OS thread id

1874864

MySQL thread id 14, query id 191 localhost root

show innodb status

---TRANSACTION 0 72004902, ACTIVE 8 sec, process no 30615, OS

thread id 2075568

2 lock struct(s), heap size 320, undo log entries 1

MySQL thread id 17, query id 190 localhost gulei

....

....

看到其中一个会话占用了锁，但没有再具体的内容了。

这时有一个办法，能看到更多的当前的锁的信息：

mysql> create table innodb_lock_monitor(a int)

engine=innodb;Query OK, 0 rows affected (0.02

sec)

mysql> update test set b='ccccc' where

a=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

另一会话中：

mysql> show innodb statusG

*************************** 1. row

***************************

....

....

------------

TRANSACTIONS

------------

Trx id counter 0 72004906

Purge done for trx's n:o < 0 72004904 undo n:o

< 0 0

History list length 27

Total number of lock structs in row lock hash table 1

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0 0, not started, process no 30615, OS thread id

1874864

MySQL thread id 14, query id 195 localhost root

show innodb status

---TRANSACTION 0 72004905, ACTIVE 6 sec, process no 30615, OS

thread id 2075568

2 lock struct(s), heap size 320, undo log entries 1

MySQL thread id 17, query id 194 localhost gulei

TABLE LOCK table `test/test`

trx id 0 72004905 lock mode IX

RECORD LOCKS space id 0 page no 61 n bits 72 index `PRIMARY` of

table `test/test` trx id 0 72004905 lock_mode X locks rec but not

gap

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format;

info bits 0

0: len 4; hex 80000001; asc ;; 1: len 6; hex 0000044ab529; asc J

);; 2: len 7; hex 000001802d21b0; asc -! ;; 3: len 5; hex

6363636363; asc ccccc;;....

....

更多的锁的相关内容出现了，有些神奇吧。这样，当有些数据库操作由于获取不到锁而等待的时候，就可以用这个办法来查看锁的情况。

在MYSQL文档中有相关描述，但是说得并不明朗：

文中似乎只是提到会影响mysqld的标准输出，并没有说会影响show innodb status

补充：需要即时关闭上述开关，即用完就drop那个表。因为我发现mysql目录下的.err文件变得很大，其中有很多类似show

innodb status的记录。看来mysql定期把monitor的内容输出到错误日志文件里

了。