Percona Toolkit使用之pt-deadlock-logger

     pt-deadlock-logger的功能是记录MySQL死锁。

     用法如下：

pt-deadlock-logger [OPTIONS] DSN

     pt-deadlock-logger记录给出DSN（MySQL访问连接）上MySQL死锁的信息。死锁信息会打印到STDOUT，也可以通过指定“ --dest ”选项保存到一张表。除非指定了“ --run-time ”或者“ --iterations ”选项，否则工具会一直执行下去。

     ①打印host1上的死锁信息（守护进程不会退出）：

pt-deadlock-logger h=host1

     ②打印host1上的死锁信息1次然后退出：

pt-deadlock-logger h=host1 --iterations 1

     ③把host1上的死锁信息保存到host2的`percona_schema`.`deadlocks`库表中：

pt-deadlock-logger h=host1 --dest h=host2,D=percona_schema,t=deadlocks

     pt-deadlock-logger通过查询和解析“ SHOW ENGINE INNODB STATUS ”来打印MySQL死锁信息。当一个新的死锁出现时，就会被打印到STDOUT，或者如果有选项指定，保存到“ --dest ”。

     新的死锁才会被打印。通过死锁的元数据“ server ”、“ ts ”和“ thread ”值（即便这些字段没有被“ --columns ”选项指定），每个死锁会生成一个fingerprint。如果一个死锁的fingerprint值异于上一个死锁的fingerprint，这个死锁就会被打印。

     通过使用“ INSERT IGNORE ”语句，避免了往“ --dest ”里面写入重复的死锁信息。因此，每个死锁被保存“ --iterations ”次。

     以下为个人本地环境的测试数据。

     首先是手动造一个死锁。这包括创建一个示例表、插入示例数据和开启两个会话模拟一个简单的“ AB - BA ”死锁。

/* prepare env */
CREATE TABLE `player`.`player` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`role` VARCHAR(15) NOT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

INSERT INTO `player`.`player` (`role`) VALUES ('goalkeeper');
INSERT INTO `player`.`player` (`role`) VALUES ('leftdefender');

/* SESSION 1 */
START TRANSACTION;

UPDATE `player`.`player` SET `role` = 'leftdefender' WHERE `id` = 1;

-- here wait to execute SESSION 2

UPDATE `player`.`player` SET `role` = 'goalkeeper' WHERE `id` = 2;

-- free lock at last

ROLLBACK;

/* SESSION 2 */
START TRANSACTION;

-- block update
UPDATE `player`.`player` SET `role` = 'goalkeeper' WHERE `id` = 2;
UPDATE `player`.`player` SET `role` = 'leftdefender' WHERE `id` = 1;

-- free lock at last

ROLLBACK;

     终端命令行执行死锁检测。

root@ubuntu:~# pt-deadlock-logger -h192.168.112.129 -P3306 -uroot -p123456
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
192.168.112.129 2018-02-12T15:19:17 20 0 20 root  192.168.112.1 player player PRIMARY RECORD X w 1 UPDATE `player`.`player` SET `role` 
= 'goalkeeper' WHERE `id` = 2192.168.112.129 2018-02-12T15:19:17 21 0 7 root  192.168.112.1 player player PRIMARY RECORD X w 0 UPDATE `player`.`player` SET `role` =
 'leftdefender' WHERE `id` = 1
 

     同样是终端命令行执行死锁检测，但只打印（迭代）1次。

root@ubuntu:~# pt-deadlock-logger -h192.168.112.129 -P3306 -uroot -p123456 --iterations=1
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
192.168.112.129 2018-02-12T15:19:17 20 0 20 root  192.168.112.1 player player PRIMARY RECORD X w 1 UPDATE `player`.`player` SET `role` 
= 'goalkeeper' WHERE `id` = 2192.168.112.129 2018-02-12T15:19:17 21 0 7 root  192.168.112.1 player player PRIMARY RECORD X w 0 UPDATE `player`.`player` SET `role` =
 'leftdefender' WHERE `id` = 1
root@ubuntu:~# 

     将死锁信息写入表中。这需要去“ --dest ”中手动创建表。

CREATE TABLE `player`.`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@ubuntu:~# pt-deadlock-logger h=192.168.112.129, P=3306, u=root, p=123456 --dest h=192.168.112.128,P=3306,u=root,p=123456,D=player,t=deadlocks --iterations=1
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
192.168.112.129 2018-02-12T15:19:17 20 0 20 root  192.168.112.1 player player PRIMARY RECORD X w 1 UPDATE `player`.`player` SET `role` 
= 'goalkeeper' WHERE `id` = 2192.168.112.129 2018-02-12T15:19:17 21 0 7 root  192.168.112.1 player player PRIMARY RECORD X w 0 UPDATE `player`.`player` SET `role` =
 'leftdefender' WHERE `id` = 1
root@ubuntu:~# 

mysql> SELECT * FROM `player`.`deadlocks`\G
*************************** 1. row ***************************
   server: 192.168.112.129
       ts: 2018-02-12 15:19:17
   thread: 20
   txn_id: 0
 txn_time: 20
     user: root
 hostname: 
       ip: 192.168.112.1
       db: player
      tbl: player
      idx: PRIMARY
lock_type: RECORD
lock_mode: X
wait_hold: w
   victim: 1
    query: UPDATE `player`.`player` SET `role` = 'goalkeeper' WHERE `id` = 2
*************************** 2. row ***************************
   server: 192.168.112.129
       ts: 2018-02-12 15:19:17
   thread: 21
   txn_id: 0
 txn_time: 7
     user: root
 hostname: 
       ip: 192.168.112.1
       db: player
      tbl: player
      idx: PRIMARY
lock_type: RECORD
lock_mode: X
wait_hold: w
   victim: 0
    query: UPDATE `player`.`player` SET `role` = 'leftdefender' WHERE `id` = 1
2 rows in set (0.00 sec)

mysql> 

参考：

     https://www.percona.com/doc/percona-toolkit/LATEST/pt-deadlock-logger.html