mysql 观察锁信息_查看mysql锁的详细信息

作为dba有时候需要查看show engine innodb status \G 但是对于锁输出的信息比较少

比如：

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

....

....

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

下面就告诉一个办法，可以看到详细的情况 ， 在空闲的database上执行

create table innodb_lock_monitor(a int) engine=innodb;

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;;....

这个时候就会看到好多的锁信息 同样这个也会输出到log-error日志

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

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

补充：需要即时关闭上述开关，即用完就drop那个表。因为我发现mysql目录下的log-error文件变得很大，其中有很多类似show innodb status的记录。看来mysql定期把monitor的内容输出到错误日志文件

另；show open tables 同样也可以看到打开的锁的表

(本文转自：http://blog.chinaunix.net/u/25477/showart_203494.html)