查看mysql锁的详细信息

最新推荐文章于 2024-07-31 09:08:05 发布

weixin_34195364

最新推荐文章于 2024-07-31 09:08:05 发布

阅读量453

点赞数

文章标签：数据库

原文链接：http://blog.51cto.com/aklaus/1657058

版权

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