死锁案例 GAP 锁 没有就插入，存在就更新

 https://mp.weixin.qq.com/s/oF6rro5HjrrUJp8YNbfj9w

漫谈死锁

有赞coder
 
 2019年1月25日 18:02

文 | 杨一 on 运维

转 | 来源：公众号yangyidba

 

一、前言

死锁是每个 MySQL DBA 都会遇到的技术问题，本文自己针对死锁学习的一个总结，了解死锁是什么，MySQL 如何检测死锁，处理死锁，死锁的案例，如何避免死锁。

二、死锁

死锁是并发系统中常见的问题，同样也会出现在 Innodb 系统中。当两个及以上的事务，双方都在等待对方释放已经持有的锁或者因为加锁顺序不一致造成循环等待锁资源，就会出现"死锁"。

举例来说 A 事务持有 x1锁 ，申请 x2 锁，B 事务持有 x2 锁，申请 x1 锁。A 和 B 事务持有锁并且申请对方持有的锁进入循环等待，就造成死锁。

图片

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

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

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

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

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

三、MySQL 的死锁机制

死锁机制包含两部分：检测和处理。

把事务等待列表和锁等待信息列表通过事务信息进行 wait-for graph 检测，如果发现有闭环，则回滚 undo log 量少的事务；死锁检测本身也会算检测本身所需要的成本，以便应对检测超时导致的意外情况。

图片

3.1 死锁检测

当 InnoDB 事务尝试获取(请求)加一个锁，并且需要等待时，InnoDB 会进行死锁检测。正常的流程如下:

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

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

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

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

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

3.2 如何处理死锁

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

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

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

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

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

四、Innodb 的锁类型

首先我们要知道对于 MySQL 有两种常规锁模式

  • LOCK_S（读锁，共享锁）

  • 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 锁的详细介绍可以移步官方文档

五、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 N

  1. 找到大于 N 的第一条记录 M，以及前一条记录 P

  2. 如果 M 上面没有 gap/next-key lock，进入第三步骤，否则等待(对其 next-rec 加 insert intension lock，由于有 gap 锁，所以等待)

  3. 检查 P：判断 P 是否等于 N：

 

 

  1. 如果不等: 则完成插入（结束）

  2. 如果相等: 再判断P是否有锁，

  3.    a 如果没有锁:报1062错误(duplicate key),说明该记录已经存在，报重复值错误

  4.    b 加S-lock,说明该记录被标记为删除, 事务已经提交，还没来得及purge

  5.    c 如果有锁: 则加S-lock,说明该记录被标记为删除，事务还未提交.

该结论引自: http://keithlan.github.io/2017/06/21/innodblocksalgorithms/

delete 的加锁方式

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

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

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

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

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

引自文章 MySQL DELETE 删除语句加锁分析

六、如何查看死锁

1. 查看事务锁等待状态情况

 

  1.   select * from information_schema.innodb_locks;

  2.   select * from information_schema.innodb_lock_waits;

  3.   select * from information_schema.innodb_trx;

  4.   ```

下面的查询可以得到当前状况下数据库的等待情况：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. 打开下列参数，获取更详细的事务和死锁信息

 

  1.   innodb_print_all_deadlocks = ON

  2.   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。

 

 

https://mp.weixin.qq.com/s/2obpN57D8hyorCMnIu_YAg

 

死锁案例八

 

文 | 杨一 on 运维

转 | 来源：公众号yangyidba

 

一、前言

死锁其实是一个很有意思也很有挑战的技术问题，大概每个 DBA 和部分开发朋友都会在工作过程中遇见。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。

二、案例分析

2.1 业务场景

业务上的主要逻辑:

首先执行插入数据，如果插入成功，则提交。如果插入的时候报唯一键冲突，则执行更新。 如果同时出现三个并发在执行数据初始化动作，sess1 插入成功，sess2 和 sess3 插入遇到唯一键冲突，插入失败，则都执行执行更新，于是出现死锁。

2.2 环境准备

MySQL 5.6.24 事务隔离级别为 RR

  1. create table ty (

  2. id int not null primary key auto_increment ,

  3. c1 int not null default 0,

  4. c2 int not null default 0,

  5. c3 int not null default 0,

  6. unique key uc1(c1),

  7. unique key uc2(c2)

  8. ) engine=innodb ;

  9.  

  10. insert into ty(c1,c2,c3) values(1,3,4),(6,6,10),(9,9,14);

2.3 测试用例

为了方便分析死锁日志，三个会话插入的 c3 的值分别为1 2 3 ，生产上其实是相同的值。

 

 

2.4 死锁日志

  1. 2018-03-28 10:04:52 0x7f75bf2d9700

  2. *** (1) TRANSACTION:

  3. TRANSACTION 1870, ACTIVE 76 sec starting index read

  4. mysql tables in use 1, locked 1

  5. LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)

  6. MySQL thread id 399265, OS thread handle 12, query id 9 localhost root updating

  7. update ty set c3=5 where c1=4

  8. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

  9. RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table `test`.`ty` trx id 1870 lock_mode X locks rec but not gap waiting

  10. *** (2) TRANSACTION:

  11. TRANSACTION 1871, ACTIVE 32 sec starting index read, thread declared inside InnoDB 5000

  12. mysql tables in use 1, locked 1

  13. 3 lock struct(s), heap size 1136, 2 row lock(s)

  14. MySQL thread id 399937, OS thread handle 16, query id 3 localhost root updating

  15. update ty set c3=5 where c1=4

  16. *** (2) HOLDS THE LOCK(S):

  17. RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table `test`.`ty` trx id 1871 lock mode S

  18. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

  19. RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table `test`.`ty` trx id 1871 lock_mode X locks rec but not gap waiting

  20. *** WE ROLL BACK TRANSACTION (2)

其实单单从日志上查看只看到两个事务的 update 相互竞争，在缺乏业务逻辑场景的情况下，很难得到有效思路。

2.5 分析死锁日志

T2 s1 执行 insert 操作，检查唯一性且插入成功,持有 c1=4 记录行的行锁。

T3 s2 insert遇到唯一键冲突，申请加锁 Lock S Next-key Lock 日志显示为 index uc1 of table test.ty trx id 1870 lock mode S waiting

T4 与 s2 相同, s3 insert 遇到唯一键冲突，申请加锁 Lock S Next-key Lock 日志显示为 index uc1 of table test.ty trx id 1870 lock mode S waiting

T5 sess1 执行 commit 操作, 此时 sess2 和 sess3 同时获取 Lock S Next-key Lock。

T6 应用收到唯一键冲突，sess2 执行 update 操作需要申请 c=4 的行锁，与 sess3的持有的 Lock S Next-key Lock 不兼容，等待 sess3 释放Lock S Next-key Lock。

T7 与sess2 类似 sess3 执行update 操作需要申请 c=4 的行锁，与 sess2 的持有的 Lock S Next-key Lock 不兼容，等待 sess2 释放 Lock S Next-key Lock 。出现循环等待，发生死锁。

2.6 解决方法

本案例的解决方式其实和前文 死锁案例之七 一致，使用 insert on duplicate key。案例七与本案例导致死锁业务逻辑极为相似，为什么呢？因为都是同一组开发哥哥写的。

三、小结

导致死锁的根本原因是不同事务申请锁的顺序不一样出现循环等待，开发同学在设计高并发的业务场景时，需要着重思考这一点，并且尽量规避业务场景设计不合理导致死锁。

另外就是 insert 的加锁机制相对 update 其实比较复杂，需要多动手实践，理清加锁流程。

扩展阅读

1. 漫谈死锁

2. 如何阅读死锁日志

3. 死锁案例一

4. 死锁案例二

5. 死锁案例三

6. 死锁案例四

7. 死锁案例五

8. 死锁案例六

9. 死锁案例七

 

https://mp.weixin.qq.com/s/ZknxiA5RuRZpefbF1bM82Q

死锁案例七

 

一、前言

死锁，其实是一个很有意思也很有挑战的技术问题，大概每个 DBA 和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。

二、案例分析

2.1 业务场景

业务开发同学想同步数据，他们的逻辑是通过 update 更新操作，如果更新记录返回的 affect_rows为0，然后就调用 insert 语句进行插入初始化。如果插入失败则再进行更新操作，多个会话并发操作的情况下就出现死锁。

2.2 环境说明

MySQL 5.6.24 事务隔离级别为 RR

  1. create table ty (

  2. id int not null primary key auto_increment ,

  3. c1 int not null default 0,

  4. c2 int not null default 0,

  5. c3 int not null default 0,

  6. unique key uc1(c1),

  7. unique key uc2(c2)

  8. ) engine=innodb ;

  9.  

  10. insert into ty(c1,c2,c3)

  11. values(1,3,4),(6,6,10),(9,9,14);

2.3 测试用例

2.4 死锁日志

  1. 2018-03-27 17:59:23 0x7f75bf39d700

  2. *** (1) TRANSACTION:

  3. TRANSACTION 1863, ACTIVE 76 sec inserting

  4. mysql tables in use 1, locked 1

  5. LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1

  6. MySQL thread id 382150, OS thread handle 56640, query id 28 localhost root update

  7. insert into ty (c1,c2,c3) values(3,4,2)

  8. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

  9. RECORD LOCKS space id 28 page no 5 n bits 72 index uc2 of table `test`.`ty` trx id 1863 lock_mode X locks gap before rec insert intention waiting

  10. *** (2) TRANSACTION:

  11. TRANSACTION 1864, ACTIVE 65 sec inserting, thread declared inside InnoDB 5000

  12. mysql tables in use 1, locked 1

  13. 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1

  14. MySQL thread id 382125, OS thread handle 40032, query id 62 localhost root update

  15. insert into ty (c1,c2,c3) values(3,4,2)

  16. *** (2) HOLDS THE LOCK(S):

  17. RECORD LOCKS space id 28 page no 5 n bits 72 index uc2 of table `test`.`ty` trx id 1864 lock_mode X locks gap before rec

  18. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

  19. RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table `test`.`ty` trx id 1864 lock mode S waiting

  20. *** WE ROLL BACK TRANSACTION (2)

2.5 分析死锁日志

首先我们要再次强调 insert 插入操作的加锁逻辑。

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

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

新数据插入完成之后：LOCK_X + LOCK_REC_NOT_GAP

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

其次 我们需要了解锁的兼容性矩阵。

从兼容性矩阵我们可以得到如下结论:

INSERT 操作之间不会有冲突。

GAP，Next-Key 会阻止 Insert。

GAP 和 Record，Next-Key 不会冲突。

Record 和 Record、Next-Key 之间相互冲突。

已有的 Insert 锁不阻止任何准备加的锁。

已经持有的 GAP 锁会阻塞插入意向锁 INSERT_INTENTION。

另外 对于通过唯一索引更新或者删除不存在的记录，会申请加上 GAP 锁。

分析

了解上面的基础知识，我们开始对死锁日志进行分析:

T1: sess1 通过唯一键更新数据，由于 c2=4 不存在，返回 affect row 为 0，MySQL 会申请(3,6)之间的 GAP 锁。

T2: sess2 的情况和 sess1 类似，也会申请(3,6)之间的 GAP 锁，从上面的兼容性矩阵来看两个 GAP 锁并不会冲突。

T3: sess1 根据 update 语句返回 affect row 为 0，执行 insert 操作，此时需要申请插入意向锁，sess2 会话持有的 GAP 锁和 sess1 申请的插入意向锁冲突，出现等待。

index uc2 of table test.ty trx id 1863 lock_mode X locks gap before rec insert intention waiting

T4:sess2 与 sess1类似，根据 update 语句返回 affect row 为 0，执行 insert 操作。 申请的插入意向锁与sess1 的 update 语句持有的 GAP 锁冲突。sess1（持有 GAP 锁），sess2（持有 GAP 锁），sess1（插入意向锁等待 sess2 的 GAP 锁释放） sess2（插入意向锁等待 sess1 的 GAP 锁释放） 构成循环等待，进而导致死锁。

2.6 解决方法

从业务场景的处理逻辑上看，业务需要发送两次请求一次 update，一次 insert 才能完成业务逻辑，不够友好和优化。

其实我们可以和开发同学沟通好，确认业务的幂等性，使用 insert on duplicate key的方式，没有就插入，存在就更新，一次调用即可完成之前 2 次操作的功能，提高性能。

三、小结

最后想说关于解决死锁问题的思路:

1. 具备扎实的锁相关的基础知识。

2. 单单根据死锁日志其实比较难以判断具体的 sql 执行情况，需要和开发同学沟通好，理清业务执行 sql 的逻辑，然后去模拟测试。

 

posted @ 2020-07-17 23:12  papering  阅读(1030)  评论(0)    收藏  举报