蒙国造博客

如何找出MySQL中正在锁表的语句?

MySQL在每个版本发布时,都会加上一些用以监控内部活动的工具。

但是,监控内部的锁情况的工具一直支持的不好。

监控锁一个非常重要和常用的功能,在这篇文章,我讲阐述如何做到。

1 介绍

假设有这样的一种情况:你准备update一个表,但是每次执行update语句的时候,都一直在等待,直到返回信息告诉你等待锁超时。

你准备update的表已经被某人锁住了,但是你不确定到底是谁。

这就有点郁闷了,因为你不知道现用者要用多久,是不是无限期的使用

有时候我不得不隔天再update,这个表可能要被锁上一整天。

在MySQL中有一个方法可以探窥到锁情况,它会打印出所有innodb的信息:

mysql> SHOW ENGINE INNODB STATUS;

2 哪里有死锁?

查看锁的第一步就是找到死锁的位置。

上面这个命令会显示关于死锁事务的信息,谁在使用锁,谁在等待锁。

下面是截取了一段输出,可以重点查看“WAITING FOR THIS LOCK TO BE GRANTED”和“HOLDS THE LOCKS.”部分:

------------------------
LATEST DETECTED DEADLOCK
------------------------
060731 20:19:58
*** (1) TRANSACTION:
TRANSACTION 0 93698, ACTIVE 2 sec, process no 12767, OS thread id 1141946720 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1216
MySQL thread id 3, query id 19 localhost root Updating
update test.innodb_deadlock_maker set a = 0 where a <> 0
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 131120 n bits 72 index `GEN_CLUST_INDEX` of table `test/innodb_deadlock_maker` trx id 0 93698 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000019000; asc       ;; 1: len 6; hex 000000016e01; asc     n ;; 2: len 7; hex 80000000320110; asc     2  ;; 3: len 4; hex 80000000; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 0 93699, ACTIVE 2 sec, process no 12767, OS thread id 1142212960 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1216
MySQL thread id 4, query id 20 localhost root Updating
update test.innodb_deadlock_maker set a = 1 where a <> 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 131120 n bits 72 index `GEN_CLUST_INDEX` of table `test/innodb_deadlock_maker` trx id 0 93699 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000019000; asc       ;; 1: len 6; hex 000000016e01; asc     n ;; 2: len 7; hex 80000000320110; asc     2  ;; 3: len 4; hex 80000000; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000019001; asc       ;; 1: len 6; hex 000000016e01; asc     n ;; 2: len 7; hex 8000000032011f; asc     2  ;; 3: len 4; hex 80000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 131120 n bits 72 index `GEN_CLUST_INDEX` of table `test/innodb_deadlock_maker` trx id 0 93699 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000019000; asc       ;; 1: len 6; hex 000000016e01; asc     n ;; 2: len 7; hex 80000000320110; asc     2  ;; 3: len 4; hex 80000000; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

以“RECORD LOCKS space id 0”开头的一行比较重要,它指示哪个表的哪个索引被锁住了

这是问题的关键所在,我们要找的就是这里。

但是还有一个问题,当我们知道有死锁的时候,说明已经太晚了。

我们不想知道过去谁拥有锁,我们只关心现在谁拥有锁

但是,上面的死锁信息对于找到当前拥有锁的事务,依然是帮助不多。

3 当事务在等待锁

接下来,我们可以看看事务部分的锁信息,如下:

---TRANSACTION 0 93789802, ACTIVE 19 sec, process no 9544, OS thread id 389120018
MySQL thread id 23740, query id 194861248 worker1.office 192.168.0.12 robot
---TRANSACTION 0 93789797, ACTIVE 20 sec, process no 9537, OS thread id 389005359 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 23733, query id 194861215 elpaso 192.168.0.31 robot Updating
update test.test set col1 = 4
------- TRX HAS BEEN WAITING 20 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 299998 n bits 200 index `PRIMARY` of table `test/test` trx id 0 93789797 lock_mode X locks rec but not gap waiting
Record lock, heap no 77 PHYSICAL RECORD: n_fields 15; compact format; info bits 0 
 0: len 4; hex 80474fd6; asc  GO ;; 1: len 6; hex 000005970680; asc       ;; 2: len 7; hex 000017c02b176c; asc     + l;; 3: len 4; hex 80000003; asc     ;; 4: len 8; hex 800000000da0c93a; asc        :;; 5: len 8; hex 800000000eb2ea7e; asc        ~;; 6: len 4; hex c771fe44; asc  q D;; 7: len 4; hex 8000003e; asc    >;; 8: len 8; hex 8000123eb9e5dfd5; asc    >    ;; 9: len 4; hex 8000003a; asc    :;; 10: len 8; hex 8000123eb9e43603; asc    >  6 ;; 11: len 4; hex 80000035; asc    5;; 12: len 8; hex 8000123eb9d6c130; asc    >   0;; 13: len 4; hex 80000033; asc    3;; 14: len 8; hex 8000123eb9c7c853; asc    >   S;;
 
---------------------
---TRANSACTION 0 93789679, ACTIVE 31082 sec, process no 9535, OS thread id 388972583 starting index read, thread declared inside InnoDB 6
mysql tables in use 4, locked 4
11614 lock struct(s), heap size 683328
MySQL thread id 23731, query id 194861117 elpaso 192.168.0.31 robot

我们看到,第一个事务已经等待锁等了20秒,它同样标有对应的表和索引。

同样,上面还显示了当前有4个表在使用,4个表被锁住,但是输出信息没有说明具体是哪些表。

可以看出,这里有一些对我们有用的信息,但是并不多。

当我们遇到查询在等待锁时,知道在等待什么锁,以及哪些事务正在拥有锁,有时候并没有太大帮助。

不过比之什么都没有好一点,如果我们只是看到两个事务,那么肯定是一个拥有锁,一个等待锁。

在上面的输出当中,是否有看到令人毛骨悚然的地方?

连接23731的事务0 93789771,已经活动了8个半小时?!

要找到它的拥有者,用processlist,然后用kill杀死这个连接。

mysql> show full processlists;
mysql> kill 1234;

换句话说,在innodb表遇到表锁,如果幸运的话,我们值看到一个其他的事务带有锁,

那么,它就极有可能是它锁住了你的请求。

 

英文原文:

 

 

退出移动版