MySql - 死锁「pt-deadlock-logger」

线上遇见死锁可以第一时间使用 show engine innodb status 命令来获取最近一次的死锁信息.

MySQL 提供了一套 InnoDB 的监控机制，用于周期性（每隔 15s）输出 InnoDB 的运行状态到 Mysqld 服务的标准错误输出 （stderr）。默认情况下监控是关闭的，只有当需要分析问题的时候才会开启，并且在分析问题之后，建议将监控关闭，因为他对数据库的性能有一定的影响，另外每 15 s 输出一次日志，会使日志文件变得特别大。

InnoDB 的监控主要分四种：

• 标准监控（Standard InnoDB Monitor）
• 锁监控（InnoDB Lock Monitor）
• 表空间监控（InnoDB Tablespace Monitor）
• 表监控（InnoDB Table Monitor）

后面两种监控基本上废弃了，关于各种监控的作用可以参考 MySQL 的官方文档 。
要获取死锁日志，我们需要开启 InnoDB 的标准监控，推荐将锁监控也打开，它可以提供一些额外的锁信息，在分析死锁问题时会很有用。

一、如何开启锁监控 🔓

在 MySQL 5.6.16 之后，可以通过设置系统参数来开启锁监控，如下：

-- 开启标准监控
set global innodb_status_output=ON;

-- 关闭标准监控
set global innodb_status_output=OFF;

-- 开启锁监控
set global inndb_status_output_locks=ON;

-- 关闭锁监控
set global inndb_status_output_locks=OFF;

另外， MySQL 还提供了一个系统参数 innodb_print_all_deadlocks专门用于记录死锁日志，当发生死锁时，死锁日志会记录到 MySQL 的错误日志文件中。

set global innodb_print_all_deadlocks=ON;

除了 MySQL 自带的监控机制，还有一些监控工具也很用，例如 Innotop 和 Percona Toolkit 里面的小工具 pt-deadlock-logger

二、利用 pt-deadlock-logger 设置 & 查看死锁

1、下载安装：

$ wget percona.com/get/pt-deadlock-logger

设置软连接

$ ln -s /opt/pt-deadlock-logger /usr/bin

2、基础语法

$ pt-deadlock-logger [OPTIONS] DSN

3、创建选项

4、日常执行 pt-deadlock-logger

• 首先需要创建一张 deadlocks 的表来存储死锁信息

CREATE TABLE kvalitadog.deadlocks
(
    server    char(20)          NOT NULL COMMENT '发生死锁的 MySQL 实例，IP:PORT 标识',
    ts        timestamp         NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发生死锁的时间',
    thread    int UNSIGNED      NOT NULL COMMENT '该事务所属的线程ID，及应用端连接的会话ID 信息',
    txn_id    bigint UNSIGNED   NOT NULL COMMENT '事务ID',
    txn_time  smallint UNSIGNED NOT NULL COMMENT '事务执行时间',
    user      char(16)          NOT NULL COMMENT '应用连接的用户',
    hostname  char(20)          NOT NULL COMMENT '应用端 hostname 名',
    ip        char(15)          NOT NULL COMMENT '应用端 ip 地址', -- alternatively, ip int unsigned NOT NULL
    db        char(64)          NOT NULL COMMENT '事务语句所在的 database',
    tbl       char(64)          NOT NULL COMMENT '事务关联的表',
    idx       char(64)          NOT NULL COMMENT '使用的索引信息',
    lock_type char(16)          NOT NULL COMMENT '当前事务语句持有锁的类型',
    lock_mode char(1)           NOT NULL COMMENT '引起死锁的锁模式（S，X 等）',
    wait_hold char(1)           NOT NULL COMMENT '该事务是否在等待锁（w） 还是在持有锁（h）',
    victim    tinyint UNSIGNED  NOT NULL COMMENT '1 表示该事务被回滚',
    query     text              NOT NULL COMMENT '事务的 sql 语句（注：innodb status 只显示该事务中最后更新的一条 sql，如果一个事务有多条更新语句，之前的 sql 不会显示出来）',
    PRIMARY KEY (server, ts, thread)
) ENGINE = InnoDB;

• 开启死锁进程监控

$ ./pt-deadlock-logger h=172.16.06.05,P=3306,u=root,p=root --dest h=172.16.06.05,P=3306,D=test,t=deadlocks,u=root,p=root

• deadlock 表记录信息

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                                                                                  |
+-----------+---------------------+--------+--------+----------+---------+----------+----------------+------------+---------+---------+-----------+-----------+-----------+--------+----------------------------------------------------------------------------------------+
| 127.0.0.1 | 2021-08-26 21:07:52 |    106 |      0 |      142 | beanbag |          | 115.236.000.00 | dmeo       | demo_tt | PRIMARY | RECORD    | X         | w         |      1 | /* ApplicationName=DataGrip 2020.2.3 */ UPDATE demo_tt SET `desc`='222222' WHERE id =2 |
| 127.0.0.1 | 2021-08-26 21:07:52 |    107 |      0 |      140 | beanbag |          | 115.236.000.00 | dmeo       | demo_tt | PRIMARY | RECORD    | X         | w         |      0 | /* ApplicationName=DataGrip 2020.2.3 */ UPDATE demo_tt SET `desc`='111111' WHERE id =1 |
+-----------+---------------------+--------+--------+----------+---------+----------+----------------+------------+---------+---------+-----------+-----------+-----------+--------+----------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)