pt-deadlock-logger使用

死锁监控pt-deadlock-logger

首先我们要创建一个表用来保存死锁的信息：

CREATE TABLE deadlocks (
server char(20) NOT NULL,
ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
thread int unsigned NOT NULL,
txn_id bigint unsigned NOT NULL,
txn_time smallint unsigned NOT NULL,
user char(16) NOT NULL,
hostname char(20) NOT NULL,
ip char(15) NOT NULL, -- alternatively, ip int unsigned NOT NULL
db char(64) NOT NULL,
tbl char(64) NOT NULL,
idx char(64) NOT NULL,
lock_type char(16) NOT NULL,
lock_mode char(1) NOT NULL,
wait_hold char(1) NOT NULL,
victim tinyint unsigned NOT NULL,
query text NOT NULL,
PRIMARY KEY (server,ts,thread)
) ENGINE=InnoDB；

 

然后开启死锁进程模拟死锁：

[root@mxqmongodb2 bin]# ./pt-deadlock-logger h=172.16.16.35,P=3306,u=root,p=123456 --dest h=172.16.16.35,P=3306,D=test,t=deadlocks,u=root,p=123456
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
172.16.16.35 2017-06-21T14:32:54 17050 0 69 root 172.16.16.35 test test idx RECORD X w 0 update test set id1=0 where id=2
172.16.16.35 2017-06-21T14:32:54 17066 0 37 root 172.16.16.35 test test idx RECORD X w 1 update test set id1=0 where id=1

 

在MySQL打开两个终端：

先看一下模拟表结构：

mysql> show create table test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(11) DEFAULT NULL,
`id1` int(11) DEFAULT NULL,
KEY `idx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from test;
+------+------+
| id | id1 |
+------+------+
| 1 | 1 |
| 2 | 0 |
| 1 | 1 |
+------+------+
3 rows in set (0.00 sec)

 

session1:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select id1 from test where id=1 for update;
+------+
| id1 |
+------+
| 1 |
| 1 |
+------+
2 rows in set (0.00 sec)
mysql> update test set id1=0 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit

 

session2:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select id1 from test where id=2 for update;
+------+
| id1 |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
mysql> update test set id1=0 where id=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql> commit

 

可见，MySQL提示已经出现了死锁，我们去看一下存储表：

mysql> select * from deadlocks\G
*************************** 1. row ***************************
server: 172.16.16.35
ts: 2017-06-21 14:32:54
thread: 17050
txn_id: 0
txn_time: 69
user: root
hostname:
ip: 172.16.16.35
db: test
tbl: test
idx: idx
lock_type: RECORD
lock_mode: X
wait_hold: w
victim: 0
query: update test set id1=0 where id=2
*************************** 2. row ***************************
server: 172.16.16.35
ts: 2017-06-21 14:32:54
thread: 17066
txn_id: 0
txn_time: 37
user: root
hostname:
ip: 172.16.16.35
db: test
tbl: test
idx: idx
lock_type: RECORD
lock_mode: X
wait_hold: w
victim: 1
query: update test set id1=0 where id=1
2 rows in set (0.00 sec)

 

这会记录死锁的相关信息，但是这个对性能还是有一定影响的，一般情况就别开启了。