接上一篇,未提交的事务导致锁等待,锁住一段时间后又提交了(也可能是回滚),锁释放业务继续运行。那么我们如何知道产生锁的事务是在跑什么SQL?
今天讲的这个工具,就可以通过监听3306端口的数据,把每个事务的起始时间,SQL命令都打印出来,而且可以设置事务时长,比如事务时长超过10秒的。
参考链接:https://highdb.com/%E8%BF%BD%E8%B8%AAmysql%E4%B8%AD%E9%95%BF%E6%97%B6%E9%97%B4%E8%BF%90%E8%A1%8C%E7%9A%84%E4%BA%8B%E5%8A%A1/
github链接:https://github.com/yoshinorim/MySlowTranCapture,下载后解压
#安装
How to use:* Install libpcap, libpcap-devel, boost, and boost-devel ifnot installed*make*make install* Run myslowtrancapture
#运行
[root@xxxx MySlowTranCapture-master]# myslowtrancapture -f "tcp port 3306"Monitoring anyinterface..
Filtering rule: tcp port3306Logging transactions that take more than4000milliseconds..
........开始等待符合条件的输出
#默认检测事务时长是4秒,我们构造一个显式事务,持续时间5秒,看看输出结果
#MYSQL SESSION
mysql> select * fromt1;+------+
| id |
+------+
| 3 |
| 4 |
+------+
2 rows in set (0.00sec)
mysql> begin;delete from t1 where id=3;select sleep(5);rollback;
Query OK,0 rows affected (0.00sec)
Query OK,1 row affected (0.00sec)+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
1 row in set (5.00sec)
Query OK,0 rows affected (0.00sec)
mysql>
From 10.3.171.21:55871
2017/02/27 11:25:18.679918 ->begin #事务开始2017/02/27 11:25:18.679992 deletefrom t1 where id=3 #这里看到了事务SQL
2017/02/27 11:25:18.680336
select sleep(5)2017/02/27 11:25:23.680669 rollback #事务回滚
#事务时长=11:25:23-11:25:18=5秒
除了rollback,commit之外,给当前session设置autocommit=0/1,也会提交当前事务-------这句话有问题,单独开事务测试并没有提交,在这里看日志又像是事务终止了,需要再调查下。
From 10.3.171.21:46378
2017/02/27 12:33:37.484762 ->begin2017/02/27 12:33:37.484806 deletefromtest2017/02/27 12:33:37.485061
select sleep(200)2017/02/27 12:36:57.485276 TRAN_END BYset autocommit=0 #set autocommit=0From10.3.139.151:6610
产生上面输出的SQL是这样的:
mysql> begin;delete from test;select sleep(200);set autocommit=0;
Query OK,0 rows affected (0.00sec)
Query OK,5 rows affected (0.00sec)+------------+
| sleep(200) |
+------------+
| 0 |
+------------+
1 row in set (3 min 19.98sec)
Query OK,0 rows affected (0.00sec)
mysql>