前言

数据库的死锁，是开发和DBA都非常关注的信息，但是在MySQL中，查看死锁信息却不是非常方便，通过show engine innodb status只能查看最近一次发生的死锁信息，之前的死锁信息会被覆盖掉；这时候我们可以利用pt-deadlock-logger这个工具实现这个需求。

pt-deadlock-logger，通过定时拉取和解析show engine innodb status相关信息，并将死锁信息打印在屏幕 或 写到日志文件 或 写到表，从而实现将所有发生的死锁信息都保存下来；该工具默认是永久运行的，除非设置了--run-time 或 --iterations选项。

基本用法

（1）基本语法

pt-deadlock-logger [OPTIONS] DSN

（2）常见选项

--host：-h，IP地址
--port：-P，端口
--socket：-S，套接字文件
--user：-u，用户名
--password：-p，密码
--charset：-A，字符集
--database：-D，数据库
--ask-pass：提示手动输入密码
--daemonize：后台运行
--dest：指定存储死锁信息的表
--create-dest-table：自动创建存储死锁信息的表
--columns：死锁信息存储表的列信息
--log：指定存储死锁信息的日志文件
--run-time：运行时间，默认永久
--interval：运行间隔，默认30s
--iterations：运行次数，默认无限

（3）DSN选项

h:IP地址
P：端口
S：套接字文件
u：用户名
p：密码
A：字符集
D：数据库
t：表

场景模拟

（1）后台运行pt-deadlock-logger

$ pt-deadlock-logger h=10.1.4.9,P=10057,u=test,p=xxx --create-dest-table --dest h=10.1.4.9,P=10057,u=test,p=xxx,D=percona_schema,t=deadlocks --daemonize

（2）session 1和session 2模拟死锁

session 1：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update sbtest set pad='test' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

session 2:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update sbtest set pad='test' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

session 1(锁等待)：
mysql> update sbtest set pad='test' where id=2;

session 2(形成死锁)
mysql> update sbtest set pad='test' where id=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

（3）查询表，死锁信息已记录

mysql> select * from deadlocks;
+----------+---------------------+--------+--------+----------+------+-----------+----+------+--------+---------+-----------+-----------+-----------+--------+-----------------------------------------+
| server   | ts                  | thread | txn_id | txn_time | user | hostname  | ip | db   | tbl    | idx     | lock_type | lock_mode | wait_hold | victim | query                                   |
+----------+---------------------+--------+--------+----------+------+-----------+----+------+--------+---------+-----------+-----------+-----------+--------+-----------------------------------------+
| 10.1.4.9 | 2021-04-29 14:36:10 |     67 |      0 |       49 | root | localhost |    | test | sbtest | PRIMARY | RECORD    | X         | w         |      0 | update sbtest set pad='test' where id=2 |
| 10.1.4.9 | 2021-04-29 14:36:10 |     85 |      0 |       29 | root | localhost |    | test | sbtest | PRIMARY | RECORD    | X         | w         |      1 | update sbtest set pad='test' where id=1 |
+----------+---------------------+--------+--------+----------+------+-----------+----+------+--------+---------+-----------+-----------+-----------+--------+-----------------------------------------+
2 rows in set (0.00 sec)

（4）session 1和session 2再次模拟死锁

session 1：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update sbtest set pad='test' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

session 2:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update sbtest set pad='test' where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

session 1(锁等待):
mysql> update sbtest set pad='test' where id=3;

session 2(形成死锁)：
mysql> update sbtest set pad='test' where id=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

（5）再次查询表，死锁信息也已记录，之前的死锁信息也没有被覆盖

mysql> select * from deadlocks;
+----------+---------------------+--------+--------+----------+------+-----------+----+------+--------+---------+-----------+-----------+-----------+--------+-----------------------------------------+
| server   | ts                  | thread | txn_id | txn_time | user | hostname  | ip | db   | tbl    | idx     | lock_type | lock_mode | wait_hold | victim | query                                   |
+----------+---------------------+--------+--------+----------+------+-----------+----+------+--------+---------+-----------+-----------+-----------+--------+-----------------------------------------+
| 10.1.4.9 | 2021-04-29 14:36:10 |     67 |      0 |       49 | root | localhost |    | test | sbtest | PRIMARY | RECORD    | X         | w         |      0 | update sbtest set pad='test' where id=2 |
| 10.1.4.9 | 2021-04-29 14:36:10 |     85 |      0 |       29 | root | localhost |    | test | sbtest | PRIMARY | RECORD    | X         | w         |      1 | update sbtest set pad='test' where id=1 |
| 10.1.4.9 | 2021-04-29 14:40:19 |     67 |      0 |       25 | root | localhost |    | test | sbtest | PRIMARY | RECORD    | X         | w         |      0 | update sbtest set pad='test' where id=3 |
| 10.1.4.9 | 2021-04-29 14:40:19 |     85 |      0 |       14 | root | localhost |    | test | sbtest | PRIMARY | RECORD    | X         | w         |      1 | update sbtest set pad='test' where id=1 |
+----------+---------------------+--------+--------+----------+------+-----------+----+------+--------+---------+-----------+-----------+-----------+--------+-----------------------------------------+
4 rows in set (0.00 sec)

总结

通过pt-deadlock-logger工具，可以将数据库发生的死锁信息，持续记录下来；当然了，innodb_print_all_deadlocks这个参数也可以将所有死锁信息打印到错误日志，不过还是相对麻烦一些的