通常情况下,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的内容输出到错误日志文件里
了。