遇到mysql死锁问题,我们应该怎么排查分析呢?之前线上出现一个insert on duplicate死锁问题,本文将基于这个死锁问题,分享排查分析过程,希望对大家有帮助。
表结构:
create table `song_rank` ( `id` int(11) not null auto_increment, `songid` int(11) not null, `weight` int(11) not null default '0', primary key (`id`), unique key `songid_idx` (`songid`) using btree ) engine=innodb default charset=utf8;
隔离级别:
mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | repeatable-read | +-----------------+ 1 row in set, 1 warning (0.00 sec)
数据库版本:
+------------+ | @@version | +------------+ | 5.7.21-log | +------------+ 1 row in set (0.00 sec)
关闭自动提交:
mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) mysql> set autocommit=0; query ok, 0 rows affected (0.00 sec) mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 0 | +--------------+ 1 row in set (0.00 sec)
表中的数据:
mysql> select * from song_rank; +----+--------+--------+ | id | songid | weight | +----+--------+--------+ | 1 | 10 | 30 | | 2 | 20 | 30 | +----+--------+--------+ 2 rows in set (0.01 sec)
死锁案发原因:
并发环境下,执行insert into … on duplicate key update…导致死锁
死锁模拟复现:
事务一执行:
mysql> begin; //第一步 query ok, 0 rows affected (0.00 sec) mysql> insert into song_rank(songid,weight) values(15,100) on duplicate key update weight=weight+1; //第二步 query ok, 1 row affected (0.00 sec) mysql> rollback; //第七步 query ok, 0 rows affected (0.00 sec)
事务二执行:
mysql> begin; //第三步 query ok, 0 rows affected (0.00 sec) mysql> insert into song_rank(songid,weight) values(16,100) on duplicate key update weight=weight+1; // 第四步 query ok, 1 row affected (40.83 sec)
事务三执行:
mysql> begin; //第五步 query ok, 0 rows affected (0.00 sec) mysql> insert into song_rank(songid,weight) values(18,100) on duplicate key update weight=weight+1; //第六步
事务一,事务二,事务三执行:
步骤 | 事务一 | 事务二 | 事务三 |
---|---|---|---|
第一步 | begin; | ||
第二步 | insert into song_rank(songid,weight) values(15,100) on duplicate key update weight=weight+1; (query ok, 1 row affected (0.00 sec) ) | ||
第三步 | begin; | ||
第四步 | insert into song_rank(songid,weight) values(16,100) on duplicate key update weight=weight+1; //被阻塞 | ||
第五步 | begin; | ||
第六步 | insert into song_rank(songid,weight) values(18,100) on duplicate key update weight=weight+1; //被阻塞 | ||
第七步 | rollback; | ||
结果 | query ok, 1 row affected (40.83 sec) | error 1213 (40001): deadlock found when trying to get lock; try restarting transaction |
死锁浮出水面:
error 1213 (40001): deadlock found when trying to get lock; try restarting transaction
遇到死锁问题时,我们应该怎么处理呢?分一下几个步骤
当数据库发生死锁时,可以通过以下命令获取死锁日志:
show engine innodb status;
上面例子insert on duplicate死锁问题的日志如下:
*** (1) transaction: transaction 27540, active 19 sec inserting mysql tables in use 1, locked 1 lock wait 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 mysql thread id 23, os thread handle 14896, query id 582 localhost ::1 root update insert into song_rank(songid,weight) values(18,100) on duplicate key update weight=weight+1 *** (1) waiting for this lock to be granted: record locks space id 116 page no 4 n bits 72 index songid_idx of table `test2`.`song_rank` trx id 27540 lock_mode x locks gap before rec insert intention waiting record lock, heap no 3 physical record: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000002; asc ;; *** (2) transaction: transaction 27539, active 41 sec inserting, thread declared inside innodb 1 mysql tables in use 1, locked 1 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1 mysql thread id 22, os thread handle 6976, query id 580 localhost ::1 root update insert into song_rank(songid,weight) values(16,100) on duplicate key update weight=weight+1 *** (2) holds the lock(s): record locks space id 116 page no 4 n bits 72 index songid_idx of table `test2`.`song_rank` trx id 27539 lock_mode x locks gap before rec record lock, heap no 3 physical record: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000002; asc ;; *** (2) waiting for this lock to be granted: record locks space id 116 page no 4 n bits 72 index songid_idx of table `test2`.`song_rank` trx id 27539 lock_mode x locks gap before rec insert intention waiting record lock, heap no 3 physical record: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000002; asc ;;
如何分析死锁日志呢? 分享一下我的思路
从日志我们可以看到事务1正在执行的sql为:
insert into song_rank(songid,weight) values(18,100) on duplicate key update weight=weight+1
该条语句正在等待索引songid_idx的插入意向排他锁:
lock_mode x locks gap before rec insert intention waiting
从日志我们可以看到事务2正在执行的sql为:
insert into song_rank(songid,weight) values(16,100) on duplicate key update weight=weight+1
该语句持有一个索引songid_idx的间隙锁:
lock_mode x locks gap before rec
该条语句正在等待索引songid_idx的插入意向排他锁:
lock_mode x locks gap before rec insert intention waiting
考虑到有些读者可能对上面insert intention锁等不太熟悉,所以这里这里补一小节锁相关概念。
innodb 锁类型思维导图:
我们主要介绍一下兼容性以及锁模式类型的锁
1.共享锁与排他锁:
innodb 实现了标准的行级锁,包括两种:共享锁(简称 s 锁)、排它锁(简称 x 锁)。
如果事务 t1 持有行 r 的 s 锁,那么另一个事务 t2 请求 r 的锁时,会做如下处理:
如果 t1 持有 r 的 x 锁,那么 t2 请求 r 的 x、s 锁都不能被立即允许,t2 必须等待t1释放 x 锁才可以,因为x锁与任何的锁都不兼容。
2.意向锁
比如:事务1在表1上加了s锁后,事务2想要更改某行记录,需要添加ix锁,由于不兼容,所以需要等待s锁释放;如果事务1在表1上加了is锁,事务2添加的ix锁与is锁兼容,就可以操作,这就实现了更细粒度的加锁。
innodb存储引擎中锁的兼容性如下表:
3.记录锁(record locks)
select c1 from t where c1 = 10 for update
记录锁的事务数据(关键词:lock_mode x locks rec but not gap
),记录如下:
record locks space id 58 page no 3 n bits 72 index `primary` of table `test`.`t` trx id 10078 lock_mode x locks rec but not gap record lock, heap no 2 physical record: n_fields 3; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; 1: len 6; hex 00000000274f; asc 'o;; 2: len 7; hex b60000019d0110; asc ;;
4.间隙锁(gap locks)
5.next-key locks
6.插入意向锁(insert intention)
事务数据类似于下面:
record locks space id 31 page no 3 n bits 72 index `primary` of table `test`.`child` trx id 8731 lock_mode x locks gap before rec insert intention waiting record lock, heap no 3 physical record: n_fields 3; compact format; info bits 0 0: len 4; hex 80000066; asc f;; 1: len 6; hex 000000002215; asc " ;; 2: len 7; hex 9000000172011c; asc r ;;...
通过分析死锁日志,我们可以找到发生死锁的sql,以及相关等待的锁,我们再对对应的sql进行加锁分析,其实问题就迎刃而解了。
ok,我们回到对应的sql,insert into song_rank(songid,weight) values(16,100) on duplicate key update weight=weight+1 执行过程到底加了什么锁呢?
insert加锁策略:
insert语句会对插入的这条记录加排他记录锁,在加记录锁之前还会加一种 gap 锁,叫做插入意向(insert intention)锁,如果出现唯一键冲突,还会加一个共享记录(s)锁。
(sql加锁分析非常重要,在这里给大家推荐一篇文章,讲的非常好,解决死锁之路 - 常见 sql 语句的加锁分析)
insert on duplicate key加锁验证
为了验证一下insert on duplicate key加锁情况,我们拿上面demo的事务1和2在走一下流程。
事务1:
mysql> begin; //第一步 query ok, 0 rows affected (0.00 sec) mysql> insert into song_rank(songid,weight) values(15,100) on duplicate key update weight=weight+1; //第二步 query ok, 1 row affected (0.00 sec)
事务2(另开窗口):
mysql> begin; //第三步 query ok, 0 rows affected (0.00 sec) mysql> insert into song_rank(songid,weight) values(16,100) on duplicate key update weight=weight+1; // 第四步
使用show engine innodb status查看当前锁请求信息,如图:
有图可得:
事务2持有:ix锁(表锁),gap x锁,insert intention lock(在等待事务1的gap锁)
所以,insert on duplicate 执行过程会上这三把锁。
回归到本文开头介绍的死锁案发模拟现场(事务1,2,3)以及死锁日志现场,
案发后事务1的锁:
案发后事务2的锁:
案发复原路线:
1.首先,执行事务1执行:
begin;
insert into song_rank(songid,weight) values(15,100) on duplicate key update weight=weight+1;
会获得 gap锁(10,20),insert intention lock(插入意向锁)
2.接着,事务2执行:
begin;
insert into song_rank(songid,weight) values(16,100) on duplicate key update weight=weight+1;
会获得 gap锁(10,20),同时等待事务1的insert intention lock(插入意向锁)。
3.再然后,事务3执行:
begin;
insert into song_rank(songid,weight) values(18,100) on duplicate key update weight=weight+1;
会获得 gap锁(10,20),同时等待事务1的insert intention lock(插入意向锁)。
4.最后,事务1回滚(rollback),释放插入意向锁,导致事务2,3同时持有gap锁,等待insert intention锁,死锁形成!
锁模式兼容矩阵(横向是已持有锁,纵向是正在请求的锁):
try{ insert(); }catch(duplicatekeyexception e){ update(); }
因为insert不会加gap锁,所以可以避免该问题。
既然这是mysql5.7的一个bug,那么可以考虑更改mysql版本。
gap锁跟索引有关,并且unique key 和foreign key会引起额外的index检查,需要更大的开销,所以我们尽量减少使用不必要的索引。
本文介绍了mysql5.7死锁的一个bug。我们应该怎样去排查死锁问题呢?
如对本文有疑问, 点击进行留言回复!!
服务器Centos部署MySql并连接Navicat过程详解
网友评论