• pt-deadlock-logger 是 percona 公司下 percona-toolkit 中的一款监控数据库死锁的工具
  • 死锁日志可以直接打印到 STDOUT ，也可以通过参数指定写入另一个库的指定表中

安装

Ubuntu

1
2

$ wget https://downloads.percona.com/downloads/percona-toolkit/3.3.1/binary/redhat/7/x86_64/percona-toolkit-3.3.1-1.el7.x86_64.rpm
$ sudo apt --fix-broken -y install ./percona-toolkit_3.3.1-1.focal_amd64.deb

参数

  • –host：指定 host
  • –user：指定用户名
  • –password：密码
  • –socket：socket
  • –iterations：检查到指定次数死锁后关闭监控
  • –run-time：运行时间，运行多久后退出程序，可选后缀 s=seconds, m=minutes, h=hours, d=days，默认为 s
  • –interval：检查死锁的频率，默认为 30s ,可选后缀 s=seconds, m=minutes, h=hours, d=days
  • –log：将锁信息写入指定文件中
  • –dest：将锁日志写入指定表中
  • –create-dest-table：如果表不存在，自动创建表
  • –daemonize：fork 至后台运行
  • –pid：指定路径，创建一个 PID file 并写入 pid

使用

监控锁并输出至STDOUT

  • 开启监控
1

$ pt-deadlock-logger --host="db_host" --port="db_port" --user="db_user" --password="db_pass"
  • 创建测试库、数据
1
2
3
4
5
6
7
8
9

-- 创建测试库
CREATE DATABASE `lock_test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;

-- 创建测试表
CREATE TABLE `lock_test`.`test_lock_tbl` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `line1` VARCHAR(15) NOT NULL, PRIMARY KEY (`id`) ) COLLATE='latin1_swedish_ci';

-- 写入测试数据
insert into test_lock_tbl value (1,0);
insert into test_lock_tbl value (2,0);
  • 开启两个 MySQL Shell ，模拟死锁冲突
    • 分别开启两个事务，在第一个事务中更改 id=1 的行，第二个事务中更改 id=2 的行，在 第一个事务中再更改 id=2 的行，然后在第二个事务中更改 id=1 的行。
1
2
3
4
5

-- TRANSACTION 1
START TRANSACTION;
update test_lock_tbl set line1="1-1" where id = 1;
update test_lock_tbl set line1="1-2" where id = 2;
COMMIT;

1
2
3
4
5

-- TRANSACTION 2
START TRANSACTION;
update test_lock_tbl set line1="2-2" where id = 2;
update test_lock_tbl set line1="2-1" where id = 1;
COMMIT;
  • 第二个事务更改 id=1 行时触发锁冲突
1
2

mysql> update test_lock_tbl set line1="2-1" where id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
  • pt-deadlock-logger 打印死锁相关信息
1
2
3

server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
rm-*******.mysql.rds.aliyuncs.com 2021-06-23T14:40:06 77480 0 19 xxx  61.***.***.*** lock_test test_lock_tbl PRIMARY RECORD X w 0 update test_lock_tbl set line1="1-2" where id = 2
rm-*******.rds.aliyuncs.com 2021-06-23T14:40:06 77481 0 12 xxx  61.***.***.*** lock_test test_lock_tbl PRIMARY RECORD X w 1 update test_lock_tbl set line1="2-1" where id = 1

监听并将锁信息写入库中

  • 创建存储监听日志的 DB
1

CREATE DATABASE `pt_tools_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
  • 开启监听服务
1

$ pt-deadlock-logger --host="db_host" --port="db_port" --user="db_user" --password="db_pass"  --dest h="dest_db_host",p="dest_db_port",u="dest_db_user",p="dest_db_pass",D="pt_tools_db",t=deadlock_log_tbl --create-dest-table
  • 触发锁
  • 查看锁日志
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

mysql> select * from `deadlock_log_tbl` \G
*************************** 1. row ***************************
   server: rm-xxxxxxxx
       ts: 2021-06-23 15:56:47
   thread: 77480
   txn_id: 0
 txn_time: 42
     user: xxx
 hostname:
       ip: 61.xx.xx.xx
       db: lock_test
      tbl: test_lock_tbl
      idx: PRIMARY
lock_type: RECORD
lock_mode: X
wait_hold: w
   victim: 0
    query: update test_lock_tbl set line1=0 where id = 2
*************************** 2. row ***************************
   server: rm-xxxxxxxx
       ts: 2021-06-23 15:56:47
   thread: 77481
   txn_id: 0
 txn_time: 6
     user: xxx
 hostname:
       ip: 61.xx.xx.xx
       db: lock_test
      tbl: test_lock_tbl
      idx: PRIMARY
lock_type: RECORD
lock_mode: X
wait_hold: w
   victim: 1
    query: update test_lock_tbl set line1=0 where id = 1
*************************** 3. row ***************************
   server: rm-xxxxxxxx
       ts: 2021-06-23 16:08:22
   thread: 77480
   txn_id: 0
 txn_time: 14
     user: xxx
 hostname:
       ip: 61.xx.xx.xx
       db: lock_test
      tbl: test_lock_tbl
      idx: PRIMARY
lock_type: RECORD
lock_mode: X
wait_hold: w
   victim: 0
    query: update test_lock_tbl set line1="1-2" where id = 2
*************************** 4. row ***************************
   server: rm-xxxxxxxx
       ts: 2021-06-23 16:08:22
   thread: 77481
   txn_id: 0
 txn_time: 8
     user: xxx
 hostname:
       ip: 61.xx.xx.xx
       db: lock_test
      tbl: test_lock_tbl
      idx: PRIMARY
lock_type: RECORD
lock_mode: X
wait_hold: w
   victim: 1
    query: update test_lock_tbl set line1="2-1" where id = 1

实践

  • 后台运行监控,并将锁信息写入文件中
1

$ pt-deadlock-logger --host="db_host" --port="db_port" --user="db_user" --password="db_pass" --log="/home/xxx/db_lock.log" --daemonize
  • 后台运行监控，并将锁信息写入库中
1

$  pt-deadlock-logger --host="db_host" --port="db_port" --user="db_user" --password="db_pass"  --dest h="dest_db_host",p="dest_db_port",u="dest_db_user",p="dest_db_pass",D="pt_tools_db",t=deadlock_log_tbl --create-dest-table --daemonize