pt-deadlock-logger
官方：https://www.percona.com/doc/percona-toolkit/3.0/pt-deadlock-logger.html
主要参数
- --create-dest-table ：创建指定的表。
- --dest ：创建存储死锁信息的表。
- --database ：-D，指定链接的数据库。
- --table ：-t，指定存储的表名。
- --log ：指定死锁日志信息写入到文件。
- --run-time ：运行次数，默认永久
- --interval ：运行间隔时间，默认30s。
模拟死锁
| session1 | session2 |
|---|---|
| begin;update tb1 set a = '隔壁老李' where id = 2; | |
| begin;update tb1 set a = '隔壁老潘' where id =3; | |
| update tb1 set a = '隔壁老苗' where id =3; | |
| update tb1 set a = '隔壁老响' where id =2;<BR><BR>ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
打印死锁
pt-deadlock-logger --create-dest-table --dest D=percona_test,t=deadlocks u=yq,P=3306,h=192.168.66.101 --ask-pass --charset=utf8
Enter MySQL password:
Enter MySQL password:
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
192.168.66.101 2020-12-30T19:05:13 3 0 12 root localhost abc tb1 PRIMARY RECORD X w 0 update tb1 set a = '隔壁老苗' where id =3
192.168.66.101 2020-12-30T19:05:13 4 0 9 root localhost abc tb1 PRIMARY RECORD X w 1 update tb1 set a = '隔壁老响' where id =2
指定表中也记录着死锁信息
mysql> select * from percona_test.deadlocks;
+----------------+---------------------+--------+--------+----------+------+-----------+----+-----+-----+---------+-----------+-----------+-----------+--------+-----------------------------------------------+
| server | ts | thread | txn_id | txn_time | user | hostname | ip | db | tbl | idx | lock_type | lock_mode | wait_hold | victim | query |
+----------------+---------------------+--------+--------+----------+------+-----------+----+-----+-----+---------+-----------+-----------+-----------+--------+-----------------------------------------------+
| 192.168.66.101 | 2020-12-30 19:05:13 | 3 | 0 | 12 | root | localhost | | abc | tb1 | PRIMARY | RECORD | X | w | 0 | update tb1 set a = '隔壁老苗' where id =3 |
| 192.168.66.101 | 2020-12-30 19:05:13 | 4 | 0 | 9 | root | localhost | | abc | tb1 | PRIMARY | RECORD | X | w | 1 | update tb1 set a = '隔壁老响' where id =2 |
+----------------+---------------------+--------+--------+----------+------+-----------+----+-----+-----+---------+-----------+-----------+-----------+--------+-----------------------------------------------+
2 rows in set (0.00 sec)
打印上一次死锁后退出
pt-deadlock-logger h=host1 --iterations 1
将死锁保存到另一个库中去
pt-deadlock-logger h=host1 --dest h=host2,D=percona_schema,t=deadlocks
