一、背景说明
最近很多小伙伴因为数据库死锁的问题而烦恼，本文将着重介绍如何阅读、理解MySQL数据库的死锁日志，更好的分析死锁问题，解决问题。
二、环境以及测试案例
2.1 环境
本文采取MySQL5.7的RR模式下为案例进行解析
2.2 案例表结构以及数据
create table t2(
id int(11) not null auto_increment,
c1 varchar(20) default null,
c2 varchar(20) default null,
primary key(id),
key idx_c1(c1)
)engine=INNODB auto_increment=1 default charset=utf8mb4
insert into t2(c1,c2) values('asd','people'),('aud','jian'),('mou','longjian'),('cmn','python3');
2.3 测试案例
| T1 | T2 |
| begin; | begin; |
| delete from t2 where c1='asd'; | null |
| null | delete from t2 where c1='asd'; |
| insert into t2 (c1,c2) values ('aaa','mmm') | |
| ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
三、 通过show engine ennodb status\G;获取MySQL 的死锁信息
死锁日志以及死锁分析(分析日志#开始)
LATEST DETECTED DEADLOCK ###最后一次死锁的信息
------------------------
2020-06-30 11:52:49 0x7fb5531fa700 ###死锁发生的时间
*** (1) TRANSACTION: ##事务一
TRANSACTION 11435982, ##事务编号 ACTIVE 38 ##活跃时长以s计算 sec starting index read ##事务正在进行的状态，本案例显示正在读取索引数据
mysql tables in use 1, locked 1 ##当前事务有一个表上有锁，并且是一把锁
LOCK WAIT 2 lock struct(s), ##表示锁等待 size 1136, ##事务执行中分配给锁的内存大小，1 row lock(s)##当前事务持有锁的个数
MySQL thread id 52, ##表示MySQL的进程ID
OS thread handle 140416759858944,
query id 1983 ###表示SQL的id
localhost root updating ##表示root@'localhost'执行的 update操作
delete from t2 where c1='asd' ###表示事务中正在执行(等待)的SQL
##通常情况下通过show engine innodb 不能拿到完整的事务的信息，这个给DBA排查问题增加了不少麻烦，需要跟研发沟通结合SQL审计找出此事务的全部SQL
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: ##等待后去这个锁
RECORD LOCKS #记录锁 space id 181 page no 4 ##记录space id 为181,page序号为4(一般跟事务2上的hold lock信息相互对应)
n bits 80 index idx_c1 of table `test`.`t2` trx id 11435982 lock_mode X waiting
###说明等待test库t2表上的idx_c1索引的X 锁
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 ##表示记录锁的物理记录信息
0: len 3; hex 617364; asc asd;;
1: len 4; hex 80000002; asc ;;
##表示记录锁的字段信息 ##对应表中字段的值，存储为16进制，可以解析为十进制来对应表中锁的数据
*** (2) TRANSACTION:
TRANSACTION 11435977, ACTIVE 51 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 51, OS thread handle 140416760391424, query id 1984 localhost root update
##与上面分析类似
insert into t2 (c1,c2) values ('aaa','mmm')
*** (2) HOLDS THE LOCK(S): ##事务2已经获取的锁信息、与上面分析类同
RECORD LOCKS space id 181 page no 4 n bits 80 index idx_c1 of table `test`.`t2` trx id 11435977 lock_mode X
###表示目前拥有这行的数据记录锁
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 3; hex 617364; asc asd;;
1: len 4; hex 80000002; asc ;;
###与事务一类同
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: ##事务2等待获取的锁信息
RECORD LOCKS space id 181 page no 4 n bits 80 index idx_c1 of table `test`.`t2` trx id 11435977 lock_mode X locks gap before rec insert intention waiting ###表示等待获取gap锁，在RR模式下GAP锁，insert数据过程中会有lock x锁+ next key锁的组合操作
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 3; hex 617364; asc asd;;
1: len 4; hex 80000002; asc ;;
*** WE ROLL BACK TRANSACTION (1)
##结果回滚了事务1
四、总结
常见避免死锁的方法
合理创建索引，复合索引区分度比较高的字段放在最前，
优化SQL执行顺序，持有锁比较频繁或者时间长的SQL放在事务最后面
拆分大事务为小事务
尽可能以固定的顺序访问表
调整mysql事务隔离级别，默认为RR调整为RC 减少锁竞争
