一个mysql死锁场景实例分析

前言

最近遇到一个mysql在RR级别下的死锁问题,感觉有点意思,研究了一下,做个记录。

涉及知识点:共享锁、排他锁、意向锁、间隙锁、插入意向锁、锁等待队列

场景

隔离级别:Repeatable-Read

表结构如下

create table t (
 id int not null primary key AUTO_INCREMENT,
 a int not null default 0,
 b varchar(10) not null default '',
 c varchar(10) not null default '',
 unique key uniq_a_b(a,b),
 unique key uniq_c(c)
);

初始化数据

insert into t(a,b,c) values(1,'1','1');

有A/B两个session,按如下顺序执行两个事务

结果是

  • B执行完4之后还是一切正常
  • A执行5的时候,被block
  • B接着执行6,B报死锁,B回滚,A插入数据

show engine innodb status中可以看到死锁信息,这里先不贴,先解释几种锁的概念,再来理解死锁过程

共享(S)锁/互斥(X)锁

  • 共享锁允许事务读取记录
  • 互斥锁允许事务读写记录

这两种其实是锁的模式可以和行锁、间隙锁混搭,多个事务可以同时持有S锁,但是只有一个事务能持有X锁

意向锁

一种表锁(也是一种锁模式),表明有事务即将给对应表的记录加S或者X锁。SELECT ... LOCK IN SHARE MODE会在给记录加S锁之前先给表加IS锁,SELECT ... FOR UPDATE会在给记录加X锁之前给表加IX锁。

这是一种mysql的锁优化策略,并不是很清楚意向锁的优化点在哪里,求大佬指教

两种锁的兼容情况如下

行锁

很简单,给对应行加锁。比如update、select for update、delete等都会给涉及到的行加上行锁,防止其他事务的操作

间隙锁

在RR隔离级别下,为了防止幻读现象,除了给记录本身,还需要为记录两边的间隙加上间隙锁。
比如列a上有一个普通索引,已经有了1、5、10三条记录,select * from t where a=5 for update除了会给5这条记录加行锁,还会给间隙(1,5)和(5,10)加上间隙锁,防止其他事务插入值为5的数据造成幻读。
当a上的普通索引变成唯一索引时,不需要间隙锁,因为值唯一,select * from t where a=5 for update不可能读出两条记录来。

间隙锁相互兼容,因为如果互斥,事务A持有左半段(1,5),事务B持有右半段(1,10),那么当前面那个例子中a=5的记录被删除时,理论上左右两个间隙锁得合并成一个新锁(1,10),那么这个新的大范围锁属于谁呢?所以间隙锁相互兼容,不管是S间隙锁还是X间隙锁

插入意向锁

插入意向锁其实是一种特殊的间隙锁,从前面对间隙锁的描述中可以得知,两个事务在真正insert之前可以同时持有一段间隙的间隙锁,锁不住真正insert的这个动作。真正insert之前,mysql还会尝试获取对应记录的插入意向锁,表明有在间隙中插入一个值的意向。

插入意向锁和间隙锁互斥,比如事务1锁了(1,5)这个间隙,事务2就不能获取到a=3的插入意向锁,所以需要锁等待。

死锁过程分析

接下来就可以来分析前面那个例子中的死锁过程了,先看show engine innodb status

 *** (1) TRANSACTION:
TRANSACTION 5967, ACTIVE 8 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 9, OS thread handle 140528848688896, query id 537 192.168.128.1 root update
insert into t(a,b) values(0,'0')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 64 page no 4 n bits 72 index uniq_a_b of table `t2`.`t` trx id 5967 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc  ;;
 1: len 1; hex 31; asc 1;;
 2: len 4; hex 80000001; asc  ;;

*** (2) TRANSACTION:
TRANSACTION 5968, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 140528848484096, query id 538 192.168.128.1 root update
insert into t(a,b) values(0,'0')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 64 page no 4 n bits 72 index uniq_a_b of table `t2`.`t` trx id 5968 lock_mode X locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc  ;;
 1: len 1; hex 31; asc 1;;
 2: len 4; hex 80000001; asc  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 64 page no 4 n bits 72 index uniq_a_b of table `t2`.`t` trx id 5968 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc  ;;
 1: len 1; hex 31; asc 1;;
 2: len 4; hex 80000001; asc  ;;

*** WE ROLL BACK TRANSACTION (2)

session A(即TRANSACTION 5967)正在等待记录(a=1,b='1')之前的插入意向锁,session B(即TRANSACTION 5968)持有记录(a=1,b='1')之前的间隙锁,却也在等待那个插入意向锁。这说的什么玩意儿,是不是很诡异?

从头开始分析过程

  1. A、B分别begin,开始事务
  2. A先执行select * from t where a=0 and b='0' for update; ,先加了IX锁,然后原本意图为给(0, '0')这条记录加排他行锁,但是记录不存在,所以变成了排他间隙锁(-∞,1)
  3. B再执行select * from t where a=0 and b='0' for update; ,也是先加了IX锁,因为记录不存在,所以加上了排他间隙锁(-∞,1),但是由于间隙锁相互兼容,所以没有block
  4. A执行insert into t(a,b) values(0,'0'); ,这时候,要开始真正insert了,A需要获得(0,'0')上的插入意向锁,由于和B持有的(-∞,1)排他间隙锁冲突,所以锁等待,进入记录(0,'0')的锁等待队列(虽然记录并不存在)
  5. B执行insert into t(a,b) values(0,'0'); ,要获取插入意向锁,发现虽然B自己是持有(-∞,1)的排他间隙锁,但是A也有,所以进入等待队列,等待A释放
  6. 叮,死锁发生

死锁信息解读

事务1(TRANSACTION 5967),等待获得锁index uniq_a_b of table t2.t trx id 5967 lock_mode X locks gap before rec insert intention waiting,即在唯一索引uniq_a_b上的插入意向锁(lock_mode X locks gap before rec insert intention)
锁的边界为

 0: len 4; hex 80000001; asc  ;;
 1: len 1; hex 31; asc 1;;
 2: len 4; hex 80000001; asc  ;;

表明两行记录

  • 0和1表示uniq_a_b上的值,a=1,b=0x31(即'1'的ascii码)
  • a=1,b='1'对应的主键id=1,因为innodb的索引结构决定的,二级索引(非主键索引)指向主键索引,主键索引再指向数据,所以需要给主键加索引

至于int值按位或上的0x80000000就不是很清楚为什么了,需要大佬解读

事务2(TRANSACTION 5968),持有间隙锁index uniq_a_b of table t2.t trx id 5968 lock_mode X locks gap before rec,等待插入意向锁index uniq_a_b of table t2.t trx id 5968 lock_mode X locks gap before rec insert intention,所以死锁发生。

原则上是innodb引擎判断哪个事务回滚代价小就回滚哪个事务,但是具体评判标准不是很清楚(再一次需要大佬),这里innodb选择了回滚事务2。至此,死锁过程分析完毕

One More Thing

还没完。。。有个神奇的现象是,如果表结构变成

create table t (
 id int not null primary key AUTO_INCREMENT,
 a int not null default 0,
 b varchar(10) not null default '',
 c varchar(10) not null default '',
 unique key uniq_c(c),
 unique key uniq_a_b(a,b)
);
insert into t(a,b,c) values(1,1,1);

只是把c上的唯一索引uniq_c放到了uniq_a_b前面,那么最后的死锁信息就变了!

 *** (1) TRANSACTION:
TRANSACTION 5801, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 140528848688896, query id 380 192.168.128.1 root update
insert into t2(a,b) values(0,'0')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 56 page no 5 n bits 72 index uniq_a_b of table `t2`.`t2` trx id 5801 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc  ;;
 1: len 1; hex 31; asc 1;;
 2: len 4; hex 80000001; asc  ;;

*** (2) TRANSACTION:
TRANSACTION 5802, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 6, OS thread handle 140528848484096, query id 381 192.168.128.1 root update
insert into t2(a,b) values(0,'0')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 56 page no 5 n bits 72 index uniq_a_b of table `t2`.`t2` trx id 5802 lock_mode X locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc  ;;
 1: len 1; hex 31; asc 1;;
 2: len 4; hex 80000001; asc  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 56 page no 4 n bits 72 index uniq_c of table `t2`.`t2` trx id 5802 lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 0; hex ; asc ;;
 1: len 4; hex 80000002; asc  ;;

*** WE ROLL BACK TRANSACTION (2)

事务2等待的锁由前面的插入意向锁变成了共享锁。什么鬼?

由于没看过源码,只能根据现象倒推:因为表结构上c的唯一索引在(a,b)前面,而插入的时候没指定c的值,用的默认值0,innodb需要先去查一下有没有0这条记录,有的话就要报唯一键冲突了,所以先要加S锁,但是在(0,'0')这条记录上已经有了IX锁,看一下前面的兼容性矩阵,S锁和IX锁互斥,所以也只能锁等待

总结

看似一句简单的select和insert,底下设计非常复杂的锁机制,理解这些锁机制有利于写出高效的SQL(至少是正确的😂)

遗留问题:

  1. 意向锁的优化点是哪
  2. 锁信息里,行记录按位或上的0x80000000是啥
  3. 锁互斥的判定顺序,场景1中,(0,'0')上有兼容的间隙锁,也有等待队列中的锁,先判定哪个?
  4. innodb计算事务回滚代价的算法

参考资料

  • http://hedengcheng.com/?p=771
  • https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-insert-intention-locks
  • https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html
  • https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-understanding-innodb-locking.html

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对我们的支持。

(0)

相关推荐

  • 详解MySQL中的死锁情况以及对死锁的处理方法

    当多个事务同时持有和请求同一资源上的锁而产生循环依赖的时候就产生了死锁.死锁发生在事务试图以不同的顺序锁定资源.以StockPrice表上的两个事务为例: 事务1 START TRANSACTION; UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01'; UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '20

  • Mysql使用kill命令解决死锁问题(杀死某条正在执行的sql语句)

    在使用mysql运行某些语句时,会因数据量太大而导致死锁,没有反映.这个时候,就需要kill掉某个正在消耗资源的query语句即可, KILL命令的语法格式如下: KILL [CONNECTION | QUERY] thread_id 每个与mysqld的连接都在一个独立的线程里运行,您可以使用SHOW PROCESSLIST语句查看哪些线程正在运行,并使用KILL thread_id语句终止一个线程. KILL允许自选的CONNECTION或QUERY修改符:KILL CONNECTION与不

  • 一次Mysql死锁排查过程的全纪录

    前言 之前接触到的数据库死锁,都是批量更新时加锁顺序不一致而导致的死锁,但是上周却遇到了一个很难理解的死锁.借着这个机会又重新学习了一下mysql的死锁知识以及常见的死锁场景.在多方调研以及和同事们的讨论下终于发现了这个死锁问题的成因,收获颇多.虽然是后端程序员,我们不需要像DBA一样深入地去分析与锁相关的源码,但是如果我们能够掌握基本的死锁排查方法,对我们的日常开发还是大有裨益的. PS:本文不会介绍死锁的基本知识,mysql的加锁原理可以参考本文的参考资料提供的链接. 死锁起因 先介绍一下数

  • mysql 数据库死锁原因及解决办法

    死锁(Deadlock) 所谓死锁:是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程.由于资源占用是互斥的,当某个进程提出申请资源后,使得有关进程在无外力协助下,永远分配不到必需的资源而无法继续运行,这就产生了一种特殊现象死锁. 一种情形,此时执行程序中两个或多个线程发生永久堵塞(等待),每个线程都在等待被其他线程占用并堵塞了的资源.例如,如果线程A锁住了记

  • MySQL数据库的一次死锁实例分析

    1.故事起因于2016年11月15日的一个生产bug.业务场景是:归档一个表里边的数据到历史表里边,同是删除主表记录. 2.背景场景简化如下(数据库引擎InnoDb,数据隔离级别RR[REPEATABLE]) -- 创建表test1 CREATE TABLE test1 ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(10) NOT NULL, PRIMARY KEY (id) ); insert into test1 values('hel

  • MySQL Innodb表导致死锁日志情况分析与归纳

    案例描述在定时脚本运行过程中,发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时,mysql会检测出死锁,并打印出日志.两个sql语句如下:(1)insert into backup_table select * from source_table(2)DELETE FROM source_table WHERE Id>5 AND titleWeight<32768 AND joinTime<'$daysago_1week'teamUser表的表结构如下:PRIMARY

  • MySQL死锁问题分析及解决方法实例详解

    MySQL死锁问题是很多程序员在项目开发中常遇到的问题,现就MySQL死锁及解决方法详解如下: 1.MySQL常用存储引擎的锁机制 MyISAM和MEMORY采用表级锁(table-level locking) BDB采用页面锁(page-level locking)或表级锁,默认为页面锁 InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁 2.各种锁特点 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低 行级锁:开销大,加锁慢;

  • MySQL中由load data语句引起死锁的解决案例

    一个线上项目报的死锁,简要说明一下产生原因.处理方案和相关的一些点. 1.背景 这是一个类似数据分析的项目,数据完全通过LOAD DATA语句导入一个InnoDB表中.为方便描述,表结构简化为如下: Create table tb(id int primary key auto_increment, c int not null) engine=innodb; 导入数据的语句对应为 Load data infile 'data1.csv' into table tb; Load data inf

  • Mysql 数据库死锁过程分析(select for update)

    近期有一个业务需求,多台机器需要同时从Mysql一个表里查询数据并做后续业务逻辑,为了防止多台机器同时拿到一样的数据,每台机器需要在获取时锁住获取数据的数据段,保证多台机器不拿到相同的数据. 我们Mysql的存储引擎是innodb,支持行锁.解决同时拿数据的方法有很多,为了更加简单,不增加其他表和服务的情况下,我们考虑采用select... for update的方式,这样X锁锁住查询的数据段,表里其他数据没有锁,其他业务逻辑还是可以操作. 这样一台服务器比如select .. for upda

  • 查找MySQL线程中死锁的ID的方法

    如果遇到死锁了,怎么解决呢?找到原始的锁ID,然后KILL掉一直持有的那个线程就可以了, 但是众多线程,可怎么找到引起死锁的线程ID呢? MySQL 发展到现在,已经非常强大了,这个问题很好解决. 直接从数据字典连查找. 我们来演示下. 线程A,我们用来锁定某些记录,假设这个线程一直没提交,或者忘掉提交了. 那么就一直存在,但是数据里面显示的只是SLEEP状态. mysql> set @@autocommit=0; Query OK, 0 rows affected (0.00 sec) mys

随机推荐