背景
相信死锁deadlock
这个词语对DBA
,还是开发
来说都是听不愿意面对,因为往往死锁
会导致数据库产生一些性能问题,而且锁
这个东西,在数据库中是一种比较昂贵的资源,一旦死锁
,不管是DBA还是开发出来这些东西都非常头疼。
虽然对于MySQL
来说,我们可以通过show engine innodb status\G
看到最近一次的deadlock
信息,也可以通过打开innodb_print_all_deadlocks
将所有的deadlock
信息输出到数据库的error log
中,但是有一些弊端:
show engine innodb status\G
只能看到最近一次的死锁
信息。innodb_print_all_deadlocks
虽然可以看到所有的死锁
信息,但是死锁日志的内容会很多,在经常发生死锁
的数据库往往会产生大量的日志,导致error log
提及庞大,以及排查问题很难看到对应的死锁
信息。
所以,今天主角pt-deadlock-logger
他来了。
工具简介
pt-deadlock-logger
usage: pt-deadlock-logger [OPTIONS] DSN
1.输出数据库死锁日志信息到文件中
2.输出数据库死锁信息到DBA中心机的表中
官方文档:
https://www.percona.com/doc/percona-toolkit/LATEST/pt-deadlock-logger.html
常用参数
--iterations 打印出死锁信息的次数
使用示例
首先先造一个deadlock
session 1 | session2 |
---|---|
select * from t where id=100000 lock in share mode; | |
update t set num=100 where id=100000; | |
update t set num=100 where id=100000; | |
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
1.输出数据库死锁日志信息到文件中
我们可以通过crontab 每分钟或者多少秒去定期跑一遍,输出到一个日志文件中。
但是有个弊端,每台机器的死锁信息只能打印到自己的机器log上,不方便统一管理。
#输出死锁信息,可以追加输出到日志中
$ pt-deadlock-logger h=localhost,u=root,p=pass,S=/data/mysql/mysql.sock
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
localhost 2022-03-19T17:29:24 66 0 71 root localhost test t PRIMARY RECORD X w 0 update t set num=100 where id=100000
localhost 2022-03-19T17:29:24 67 0 8 root localhost test t PRIMARY RECORD X w 1 update t set num=100 where id=100000
^C# Caught SIGINT. Use 'kill -ABRT 12623' if the tool does not exit normally in a few seconds.
2.输出数据库死锁信息到DBA中心机的表中
相信管理过一定数量的DB的DBA,肯定都会有一个中心机
去存放一些原数据信息,比如每个集群主从的机器数量,IP地址,数据库名称等等。
所以如果我们能将死锁信息输到中心机的表上
,然后做一个邮件告警
每天监控,会帮助我们检测数据库的一些潜在问题。
刚好我们的工具就支持这个,具体操作如下:
#1.在中心机机器上创建死锁表
mysql [localhost:8026] {msandbox} ((none)) > use test;
Database changed
mysql [localhost:8026] {msandbox} (test) > CREATE TABLE 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;
Query OK, 0 rows affected (0.03 sec)
#2.命令将死锁信息输出到中心机表
$ pt-deadlock-logger h=localhost,u=root,p=pass,S=/data/mysql/mysql.sock --dest h=localhost,u=msandbox_rjw,p=pass,S=/tmp/mysql_sandbox8026.sock,P=8026,D=test,t=deadlocks
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
localhost 2022-03-19T17:29:24 66 0 71 root localhost test t PRIMARY RECORD X w 0 update t set num=100 where id=100000
localhost 2022-03-19T17:29:24 67 0 8 root localhost test t PRIMARY RECORD X w 1 update t set num=100 where id=100000
#3.我们查询一下deadlocks表
mysql [localhost:8026] {msandbox} (test) > select * from deadlocks;
+-----------+---------------------+--------+--------+----------+------+-----------+----+------+-----+---------+-----------+-----------+-----------+--------+--------------------------------------+
| server | ts | thread | txn_id | txn_time | user | hostname | ip | db | tbl | idx | lock_type | lock_mode | wait_hold | victim | query |
+-----------+---------------------+--------+--------+----------+------+-----------+----+------+-----+---------+-----------+-----------+-----------+--------+--------------------------------------+
| localhost | 2022-03-19 17:29:24 | 66 | 0 | 71 | root | localhost | | test | t | PRIMARY | RECORD | X | w | 0 | update t set num=100 where id=100000 |
| localhost | 2022-03-19 17:29:24 | 67 | 0 | 8 | root | localhost | | test | t | PRIMARY | RECORD | X | w | 1 | update t set num=100 where id=100000 |
+-----------+---------------------+--------+--------+----------+------+-----------+----+------+-----+---------+-----------+-----------+-----------+--------+--------------------------------------+
2 rows in set (0.00 sec)
#我们可以清楚的看到锁的细节,锁类型sql等等,很简洁,直观
一般会有下面几种锁类型
记录锁(LOCK_REC_NOT_GAP): lock_mode X locks rec but not gap
间隙锁(LOCK_GAP): lock_mode X locks gap before rec
Next-key 锁(LOCK_ORNIDARY): lock_mode X
插入意向锁(LOCK_INSERT_INTENTION): lock_mode X locks gap before rec insert intention
最后
可能很多DBA工作中处死锁
的经验非常少,但是在这里也是希望能帮助大家掌握更多的处理死锁问题,解决死锁的能力。