MySQL 死锁问题简单分析

死锁是什么

死锁是在并发场景下由于竞争资源而出现的一种阻塞的现象,此时若无外力介入,他们将一直处于这个僵局。
下图是MySQL中简单的死锁场景举例:
file

死锁的产生条件

  • 互斥条件
  • 请求和保持条件
  • 不剥夺条件
  • 循环等待条件

MySQL(InnoDB) 锁类型

file

注:上图画错了,页级锁是BDB引擎的
页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。
表级锁速度快,但冲突多,行级冲突少,但速度慢。因此,采取了折衷的页级锁,一次锁定相邻的一组记录。BDB 支持页级锁。特点开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

锁粒度

表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

锁类型

共享锁:行级锁,允许拥有共享锁的所有事务对当前数据读,但不允许写;
排他锁:行级锁,允许拥有锁的事务更新或删除当前数据;
意向共享锁:表级锁,事务在试图获得表中一行或某几行数据的共享锁时须先申请IS锁;
意向排他锁:表级锁,事务在试图获得表中一行或某几行数据的排他锁时须先申请IX锁;

简单来说就是共享锁允许拥有它的多个事务同时读,排他锁允许拥有它的事务(且同一时间只有一个事务拥有)进行更新或删除;

锁算法

  • next KeyLocks锁,同时锁住记录(数据),并且锁住记录前面的Gap(索引间隙)
  • Gap锁,不锁记录,仅仅记录前面的Gap
  • Recordlock锁(锁数据,不锁Gap)但是总是会锁上索引记录(唯一索引除外)

死锁日志获取

此部分参考MySQL死锁系列-线上死锁分析

发生死锁异常后 可以用show engine innodb status获取死锁信息,但该命令只能获取最近一次的思索信息。所以实际业务中,可以开启InnoDB的监控机制来获取实时的死锁信息,他会周期性地输出到日志文件中。

InnoDb 的监控较为重要的有标准监控(Standard InnoDB Monitor)和 锁监控(InnoDB Lock Monitor),通过对应的系统参数可以将其开启。

-- 开启标准监控 
set GLOBAL innodb_status_output=ON; 
-- 关闭标准监控
set GLOBAL innodb_status_output=OFF;
-- 开启锁监控
set GLOBAL innodb_status_output_locks=ON;
-- 关闭锁监控
set GLOBAL innodb_status_output_locks=OFF;

-- 记录死锁日志
set GLOBAL innodb_print_all_deadlocks=ON;

注解:死锁日志被记录到错误日志中,/var/log/mysql/mysqld.log

死锁案例分析

DELETE & UPDATE

当前表情况:
file

事务执行顺序:
file

死锁日志:
file

trx id 1717 lock_mode X locks gap before rec
T1(1716)先执行delete from item where id=15 由于记录不存在持有了(4,+∞)间隙锁防止幻读

随后T2(1717)执行delete from item where id=18 一样申请了间隙锁(4,+∞)

trx id 1717 lock_mode X insert intention waiting Record lock
T2(1717)的INSERT申请插入意向锁,但插入意向锁跟T1持有的间隙写锁冲突,所以它需要等待T1的锁释放。

trx id 1716 lock_mode X insert intention waiting Record lock
这时T1(1616)的INSERT语句也来申请插入意向锁,但同样地要求等待T2的间隙锁释放。

造成 T2 INSERT等待T1 DELETE,T1 INSERT 等待T2 DELETE造成循环等待。造成死锁

间隙锁的出现主要集中在同一个事务中先delete 后 insert的情况下, 当我们通过一个参数去删除一条记录的时候, 如果参数在数据库中存在, 那么这个时候产生的是普通行锁, 锁住这个记录, 然后删除, 然后释放锁。如果这条记录不存在,问题就来了, 数据库会扫描索引,发现这个记录不存在, 这个时候的delete语句获取到的就是一个间隙锁,然后数据库会向左扫描扫到第一个比给定参数小的值, 向右扫描扫描到第一个比给定参数大的值, 然后以此为界,构建一个区间, 锁住整个区间内的数据, 一个特别容易出现死锁的间隙锁诞生了。
————————————————
版权声明:本文为CSDN博主「Scott Johnson」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/weixin_32799203/article/details/113189832

事前怎么样避免死锁

  • 合理设计索引,区分度高的列放在组合索引的前排,使业务SQL尽可能通过索引可以定位更少的行,减少业务竞争
  • 避免大事务,尽量将大事务拆分为小事务处理
  • 以固定顺序访问表和行(打破循环等待)
  • 尽量按主键/索引查找记录,范围查找增加了锁冲突的可能性
  • 在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁概率。

事后解决

  • 超时回滚
  • 死锁检测

小结

由于技术原因,这篇博文写的比较浅尝辄止,后面还要去多学习一下这块知识。

参考

mysql死锁问题分析
这六个MySQL 死锁案例,能让你理解死锁的原因!
MySQL死锁系列-线上死锁分析