MySQL锁阻塞分析

日常维护中，经常会碰到线程被阻塞，导致数据库响应非常慢，下面就看看如何获取是哪个线程导致了阻塞的。

blog地址：http://blog.youkuaiyun.com/hw_libo/article/details/39080809

1. 环境说明

RHEL 6.4 x86_64 + MySQL 5.6.19
事务隔离级别：RR

2. 测试过程

3. 查看锁阻塞线程信息

这里用几中方法进行分析：

3.1  使用show processlist查看

MySQL [(none)]> show processlist;
+----+------+-----------+------+---------+------+--------------+------------------------------------------+
| Id | User | Host      | db   | Command | Time | State        | Info                                     |
+----+------+-----------+------+---------+------+--------------+------------------------------------------+
|  2 | root | localhost | NULL | Query   |    0 | init         | show processlist                         |
|  3 | root | localhost | test | Query   |   70 | Sending data | select count(*) from t3 a,t3 b           |
|  4 | root | localhost | test | Query   |   65 | updating     | delete from emp where empno=7788         |
|  7 | root | localhost | test | Query   |   68 | updating     | update emp set sal=3500 where empno=7788 |
+----+------+-----------+------+---------+------+--------------+------------------------------------------+
4 rows in set (0.00 sec)

如果数据库存在较多线程的话，这种方法确实不太好确认的。

3.2  直接使用show engine innodb status查看

------------
TRANSACTIONS
------------
Trx id counter 4131
Purge done for trx's n:o < 4119 undo n:o < 0 state: running but idle
History list length 126
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 2, OS thread handle 0x7f953ffff700, query id 115 localhost root init
show engine innodb status
---TRANSACTION 4130, ACTIVE 41 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 l