189 8069 5689

MySQL的死锁机制以及避免死锁的方法

本篇内容主要讲解“MySQL的死锁机制以及避免死锁的方法”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL的死锁机制以及避免死锁的方法”吧!

创新互联建站是工信部颁发资质IDC服务器商，为用户提供优质的成都服务器托管服务

一 前言 
   死锁是每个MySQL DBA 都会遇到的技术问题，本文自己针对死锁学习的一个总结,了解死锁是什么，MySQL如何检测死锁，处理死锁，死锁的案例，如何避免死锁。
二 死锁
   死锁 是并发系统中常见的问题，同样也会出现在Innodb系统中。当两个及以上的事务，双方都在等待对方释放已经持有的锁或者因为加锁顺序不一致造成循环等待锁资源，就会出现"死锁"。举例来说A 事务持有x1锁 ，申请x2锁，B 事务持有x2锁，申请x1 锁。A和B 事务持有锁并且申请对方持有的锁进入循环等待，就造成死锁。

从死锁的定义来看，MySQL出现死锁的几个要素:

  1. a 两个或者两个以上事务。

  2. b 每个事务都已经持有锁并且申请新的锁。

  3. c 锁资源同时只能被同一个事务持有或者不兼容。

  4. d 事务之间因为持有锁和申请锁导致了循环等待。

三 MySQL的死锁机制
死锁机制包含两部分:检测和处理。
把事务等待列表和锁等待信息列表通过事务信息进行wait-for graph 检测，如果发现有闭环，则回滚undo log 量少的事务；死锁检测本身也会算检测本身所需要的成本，以便应对检测超时导致的意外情况。
MySQL的死锁机制以及避免死锁的方法
3.1 死锁检测
当InnoDB事务尝试获取(请求)加一个锁，并且需要等待时，InnoDB 会进行死锁检测. 正常的流程如下:

  1. 1.InnoDB的初始化一个事务，当事务尝试申请加一个锁，并且需要等待时(wait_lock)，innodb会开始进行死锁检测(deadlock_mark)

  2. 2.进入到lock_deadlock_check_and_resolve()函数进行检测死锁和解决死锁。

  3. 3.检测死锁过程中，是有计数器来进行限制的，在等待wait-for graph 检测过程中遇到超时或者超过阈值，则停止检测。

  4. 4.死锁检测的逻辑之一是等待图的处理过程，如果通过锁的信息和事务等待链构造出一个图，如果图中出现回路，就认为发生了死锁。

  5. 5.死锁的回滚，内部代码的处理逻辑之一是比较undo的数量，回滚undo数量少的事务。

3.2 如何处理死锁
《数据库系统实现》里面提到的死锁处理

  1. 1.超时死锁检测:当存在死锁时，想所有事务都能同时继续执行通常是不可能的，因此，至少一个事务必须中止并重新开始。超时是最直接的办法，对超出活跃时间的事务进行限制和回滚

  2. 2.等待图：等待图的实现，是可以表明哪些事务在等待其他事务持有的锁，可以在数据库的死锁检测里面加上这个机制来进行检测是否有环的形成。

  3. 3.通过元素排序预防死锁:这个想法很美好，但现实很残酷,通常都是发现死锁后才去想办法解决死锁的原因

  4. 4.通过时间戳检测死锁:对每个事务都分配一个时间戳，根据时间戳来进行回滚策略。

四 Innodb 的锁类型
首先我们要知道对于MySQL有两种常规锁模式

  1. LOCK_S（读锁，共享锁）

  2. LOCK_X（写锁，排它锁）

最容易理解的锁模式，读加共享锁(in share mode)，写加排它锁.
有如下几种锁的属性

  1. LOCK_REC_NOT_GAP      （锁记录）

  2. LOCK_GAP              （锁记录前的GAP）

  3. LOCK_ORDINARY         （同时锁记录+记录前的GAP，也即Next Key锁）

  4. LOCK_INSERT_INTENTION   （插入意向锁，其实是特殊的GAP锁）

锁的属性可以与锁模式任意组合。例如.

  1. lock->type_mode       可以是Lock_X 或者Lock_S

  2. locks gap before rec  表示为gap锁：lock->type_mode & LOCK_GAP

  3. locks rec but not gap 表示为记录锁，非gap锁：lock->type_mode & LOCK_REC_NOT_GAP

  4. insert intention      表示为插入意向锁：lock->type_mode & LOCK_INSERT_INTENTION

  5. waiting               表示锁等待：lock->type_mode & LOCK_WAIT

注 关于Innodb 锁的详细介绍 可以移步 官方文档 或者 MySQL · 引擎特性 · InnoDB 事务锁系统简介
五 Innodb 不同事务加锁类型
实例 update tab  set  x=1 where  id= 1 ; 
1 索引列是主键，RC隔离级别 
  对记录记录加X锁
2 索引列是二级唯一索引，RC隔离级别
  若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name=’d’,id=10]的记录。
3 索引列是二级非唯一索引，RC隔离级别
  若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。
4 索引列上没有索引，RC隔离级别
  若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。
5 索引列是主键，RR隔离级别
  对记录记录加X锁
6 索引列是二级唯一索引，RR隔离级别
  对表加上两个X锁，唯一索引满足条件的记录上一个，对应的聚簇索引上的记录一个。
7 索引列是二级非唯一索引，RR隔离级别
  结论:Repeatable Read隔离级别下，id列上有一个非唯一索引，对应SQL:delete from t1 where id = 10; 
  首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。
8 索引列上没有索引，RR隔离级别
  则锁全表
这里需要重点说明insert 和delete的加锁方式，因为目前遇到的大部分案例或者部分难以分析的案例都是和delete，insert 操作有关。
insert 的加锁方式
对于并发insert造成唯一键冲突的时候 insert的加锁策略是 

  1. 第一阶段 唯一性约束检查，先申请LOCK_S + LOCK_ORDINARY

  2. 第二阶段 获取阶段一的锁并且insert成功之后,插入的位置有Gap锁:LOCK_INSERT_INTENTION,为了防止其他insert 唯一键冲突。

  3. 新数据插入:LOCK_X + LOCK_REC_NOT_GAP

  4. 对于insert操作来说，若发生唯一约束冲突，则需要对冲突的唯一索引加上S Next-key Lock。从这里会发现，即使是RC事务隔离级别，也同样会存在Next-Key Lock锁，从而阻塞并发。然而，文档没有说明的是，对于检测到冲突的唯一索引，等待线程在获得S Lock之后，还需要对下一个记录进行加锁，在源码中由函数row_ins_scan_sec_index_for_duplicate进行判断

引自 死锁案例之三
delete 的加锁方式

  1. 1 在非唯一索引的情况下，删除一条存在的记录是有gap锁，锁住记录本身和记录之前的gap

  2. 2 在唯一索引和主键的情况下删除一条存在的记录，因为都是唯一值，进行删除的时候，是不会有gap存在

  3. 3 非唯一索引，唯一索引和主键在删除一条不存在的记录，均会在这个区间加gap锁

  4. 4 通过非唯一索引和唯一索引去删除一条标记为删除的记录的时候，都会请求该记录的行锁，同时锁住记录之前的gap

  5. 5 RC 情况下是没有gap锁的，除了遇到唯一键冲突的情况，如插入唯一键冲突。

引自文章 MySQL DELETE 删除语句加锁分析
六 死锁案例
 关于死锁的案例不在本文做详细分析，这里给出我做的几个例子(后续会有其他案例分享)，四个案例基本均和RR 模式下的gap锁有关。
死锁案例之一  delete申请gap锁与insert 的gap锁冲突导致死锁
死锁案例之二  并发delete不存在记录申请gap锁导致死锁
死锁案例之三  两个事务并发insert 唯一键冲突 和gap锁一起导致的死锁案例
死锁案例之四  三个并发insert 语句导致的死锁
七  如何查看死锁
1. 查看事务锁等待状态情况
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;
select * from information_schema.innodb_trx;
下面的查询可以得到当前状况下数据库的等待情况:via《innodb技术内幕中》

  1. select r.trx_id       wait_trx_id,

  2. r.trx_mysql_thread_id wait_thr_id,

  3. r.trx_query           wait_query,

  4. b.trx_id              block_trx_id,

  5. b.trx_mysql_thread_id block_thrd_id,

  6. b.trx_query           block_query

  7. from information_schema.innodb_lock_waits w

  8. inner join information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id

  9. inner join information_schema.innodb_trx r on r.trx_id =w.requesting_trx_id

2. 打开下列参数，获取更详细的事务和死锁信息。
  innodb_print_all_deadlocks  = ON
   innodb_status_output  =ON
   innodb_status_output_locks = ON
3. 查看innodb状态(包含最近的死锁日志)
   show engine innodb status;

八 如何尽可能避免死锁
1 事务隔离级别使用read committed和binlog_format=row ，避免RR模式带来的gap锁竞争。
2 合理的设计索引,区分度高的列放到组合索引前列，使业务sql尽可能的通过索引定位更少的行，减少锁竞争。
3 调整业务逻辑 SQL执行顺序，避免update/delete 长时间持有锁sql在事务前面，(该优化视情况而定)
4 选择合理的事务大小，小事务发生锁冲突的几率也更小；
5 访问相同的表时，应尽量约定以相同的顺序访问表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
6 5.7.15 版本之后提供了新的功能 innodb_deadlock_detect 参数,可以关闭死锁检测，提高并发TPS。

到此，相信大家对“MySQL的死锁机制以及避免死锁的方法”有了更深的了解，不妨来实际操作一番吧！这里是创新互联网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！


分享文章：MySQL的死锁机制以及避免死锁的方法
新闻来源：http://cdxtjz.cn/article/igehci.html

其他资讯