文档章节

记一次死锁问题分析--详解数据库innodb lock机制

j
 java_龙
发布于 05/16 21:01
字数 2526
阅读 2191
收藏 100

目录

  • 概述
  • 问题重现与解析
  • Innodb lock机制

概述

    正在奋力写"BUG"中,同事说出现死锁了,让帮忙看一下,so 放下手中工作,去瞅瞅, 一段很简单的代码,就是mysql数据库下使用多线程先删除数据再插入数据,造成了死锁

     顺便骗个赞,觉得写的还可以的,不要吝啬你的赞哟

问题重现与解析

    代码

    代码解释:

    第一张图,同事采用发令枪,多线程批量处理一批数据

    第二张图是批量处理数据的逻辑, 在一个事务中,先删除一条数据然后再插入一批数据

    异常提示

     堆栈异常: Deadlock found when trying to get lock; try restarting transaction;  意思很明了,就是尝试获取锁时发现死锁.

    疑惑

    这段代码做删除时,使用的索引列,凭直观理解,这应该只是个行级锁, 不应该会出现死锁的,看代码很清楚的理解到,每个线程持有一个事务操作,如果是行级锁肯定不会出现死锁,所以删除的时候肯定不止锁了一条数据

    拨云见日

    在生产环境中mysql的存储引擎绝大部分是 InnoDB ,为什么使用 InnoDB 呢? 点击查看详细答案, 就凭事务安全这一条,相信足以让选择InnoDb了。

    代码中delete操作的是非唯一索引列在innoDB引擎下会触发 next-key lock(间隙锁)。

    举例: 表t中有非唯一索引列 test_id为 1, 10, 18, 22, 26的5条数据,此时模拟操作:

事务A 删除一条不存在的数据,数据库就会去找从左开始找最近的索引值

delete from t where test_id= 27;

  事务B 删除一条不存在的数据,数据库就会去找从左开始找最近的索引值

delete from t where test_id= 28;

此时事务A和B就会分别产生一个(26,正无穷)间隙锁,然后继续操作

事务A

INSERT INTO t VALUES(27);

此时事务A阻塞,因为事务B在删除操作时拥有了区间锁 

事务B

INSERT INTO t VALUES(28);

此时事务B就会死锁,因为事务A在删除操作时拥有了区间锁

    解决之道

    1、删除时先判断数据是否存在

    2、删除和插入分两个事务处理

    3、将事务隔离级别设置为读已提交

关于InndDb的Lock机制解释此问题,详见下章

Innodb lock机制

    Innodb锁类型

  • 共享和排他(独占)锁

        共享锁允许持有锁的事务读取一行数据

        排他(独占)锁 允许持有锁的事务更新或者删除一行数据

  •  意图锁

        innodb支持多重粒度锁,即行锁和表锁共存

        意图共享锁(IS)表明一个事务对表中个别数据设置一个共享锁

        意图排他锁(IX)表明一个事务对表中个别数据设置一个排他锁

        举例:

         SELECT ... LOCK IN SHARE MODE  将会对查询的表设置意图共享(IS)锁, SELECT ... FOR UPDATE 将会对查询的表设置意图排他锁

  • 记录锁

        记录锁是一个索引记录的锁。索引记录不一定只是一条数据哟。

        示例: SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE  会对表t中c1=10的记录们(此处可以是多条数据)加记录锁,以免其他事务对c1=10的数据们做插入、更新、删除操作。即使表t没有索引,InnoDb也会创建一个隐藏的索引群组。

  • Gap Locks(区间锁)

        区间锁是一种索引记录之间或者某索引记录之前或者 某索引记录之后的锁。区间锁可以只跨越一个索引值,也可以是多个索引值,也可以只是个空区间。当事务隔离级别为 读已提交或者启用配置 innodb_locks_unsafe_for_binlog (此配置现已禁用)时,在搜索或者索引扫描是 区间锁是禁用的。

        举例0: SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 阻止其他事务插入t.c1=15这样的值,无论是否有这样的记录,因为锁定了t.c1=10到t.c1=20的区间。

        举例1:

SELECT * FROM child WHERE id = 100;

如果id是一个唯一索引列,以上语句只会产生一个id=100 的锁,如果id没有索引或者不是唯一索引,这条语句将会锁定 100以前的区间

  • Next-Key Locks (间隙锁)

        间隙锁是一种记录锁和区间(索引之前)锁的组合锁。本文例子的问题根源就是这个

        在搜索或者扫描索引时,InnoDb通过这种方式提供了行级锁,它在找到的索引记录上设置共享锁或者排他锁。行级锁就是索引记录锁。一个索引记录的间隙锁也会是个索引之前的区间锁。如果一个会话在索引中有共享或者排他锁,其他会话就不能在这个索引顺序之前的区间插入新的索引记录。

        举例:加入一个索引包含了10,11,13,20 。间隙锁包含以下场景:

(负无穷, 10]
(10, 11]
(11, 13]
(13, 20]
(20, 正无穷)

    默认情况下,InnoDb在可重复读 事务隔离级别下使用此锁,通过 SHOW ENGINE INNODB STATUS  命令可以查看事务处理数据的间隙锁状态。

  • 插入意图锁

        插入意图锁是在执行插入操作时的一种区间锁。此锁是为了多个事务在同一个索引区间锁之间插入时不必等待。

        示例:

        如果有两个索引记录为90和120

        在客户端A 开启事务执行命令

SELECT * FROM child WHERE id > 100 FOR UPDATE;

    此语句会产生一个在120之前的区间锁

    在客户端B 开启事务执行命令

INSERT INTO child (id) VALUES (101);

此条命令在120之前的区间锁之间插入一条数据,该事务在获取排他锁的同时,产生一个插入意图锁

  • 自增锁

        自增锁是一个事务在自增列中插入数据时产生的一种特殊的表级锁。简单的情况下,一个事务在表里插入数据,其他事务都必须等待往表里插自己的数据, 所以第一个事务接收连续的主键值插入到数据行。

         innodb_autoinc_lock_mode 配置项可以控制自增锁的算法,它允许在做插入操作时如何权衡自动增量值序列和插入操作的最大并发性。点击查看此配置详细信息

    不同sql在InnoDb下产生的锁

    在查询、更新、删除语句执行过程中通常会对每个扫描过的索引设置记录锁。在where语句中是否含有排除数据的条件并不重要,InnoDb不会记录具体的where条件,只知道扫描了哪些索引。这锁通常是间隙锁( next-key locks ),它会阻塞在记录之前的区间插入新数据,然而区间锁是可以被明确的禁用,这将使间隙锁失效。事务隔离级别也会影响锁。

     如果二级索引被用于搜索和索引记录就会设置一个排他锁,InnoDB也会检索相应的群集索引记录,并对其设置锁。

    如果sql语句没有合适的索引,mysql就会整张表,表的每一行都会被锁,就会阻塞其他用户往此表插入数据,所以建立一个适当的索引很重要。

        InnoDb引擎中不同sql语句设置锁如下:

  • SELECT ... FROM 一个读语句,读取数据库的快照过程中不会设置锁,除非将事务隔离级别设置为SERIALIZABLE。
  •   SELECT ... FOR UPDATE 或者 SELECT ... LOCK IN SHARE MODE, 会为每个扫描到的行设置锁,并且为不符合条件的数据释放锁,有时候,不会立刻释放锁,因为在查询的时候丢失了结果数据和源数据的关联,比如在一个 Union语句中,从一张表中扫描(和锁定)行被插入到一个临时表中,然后再评估它们是否符合结果集。 在这种情况下,在临时表中的行与原始表中的行之间的关系丢失,而后者直到查询执行结束时才会解锁。
  • SELECT ... LOCK IN SHARE MODE 语句在所有搜索到的索引设置共享间隙锁,然而,通过一个唯一索引搜索到唯一行就会产生一个索引记录锁
  • SELECT ... FOR UPDATE 语句在所有搜索到的索引设置排他(独占)间隙锁 。然而,通过一个唯一索引搜索到唯一行就会产生一个索引记录锁

        对于每个搜索到的索引, SELECT ... FOR UPDATE 阻塞了 SELECT ... LOCK IN SHARE MODE 或者在某一事务隔离级别读的其他会话,一般读语句都会忽略读视图中存在记录的任何锁。

  • DELETE FROM ... WHERE ...   UPDATE ... WHERE ... 在搜索遇到的每条记录上设置一个独占的间隙锁。 然而,通过一个唯一索引搜索到唯一行就会产生一个索引记录锁
  • INSERT 语句对插入的行设置排他(独占)锁。这个锁是一个索引记录锁,而不是间隙锁,并且不会阻止其他会话在插入的行之前区间中插入数据。

顺便骗个赞,觉得写的还可以的,不要吝啬你的赞哟

© 著作权归作者所有

共有 人打赏支持
j
粉丝 55
博文 83
码字总数 119646
作品 0
成都
程序员
加载中

评论(16)

pain_7
pain_7

引用来自“小井井”的评论

线上我们现在基本都是默认read commited :laughing:

引用来自“java_龙”的评论

可以根据自己的业务场景合理调整,read commited的弊端需要注意规避,尤其是幻读:joy:

引用来自“pain_7”的评论

:worried: RR 就没有幻读吗。。。

引用来自“java_龙”的评论

这个产生死锁的原因就是不会出现幻读的原因,可以了解一下

引用来自“pain_7”的评论

我的意思是 RR 跟 RC 都会出现幻读

引用来自“java_龙”的评论

是的 都会出现
所有啊,你之前那句话:可以根据自己的业务场景合理调整,read commited的弊端需要注意规避,尤其是幻读~~

给我的感觉是你说 RR 没有幻读
j
java_龙

引用来自“小井井”的评论

线上我们现在基本都是默认read commited :laughing:

引用来自“java_龙”的评论

可以根据自己的业务场景合理调整,read commited的弊端需要注意规避,尤其是幻读:joy:

引用来自“pain_7”的评论

:worried: RR 就没有幻读吗。。。

引用来自“java_龙”的评论

这个产生死锁的原因就是不会出现幻读的原因,可以了解一下

引用来自“pain_7”的评论

我的意思是 RR 跟 RC 都会出现幻读
是的 都会出现
pain_7
pain_7

引用来自“小井井”的评论

线上我们现在基本都是默认read commited :laughing:

引用来自“java_龙”的评论

可以根据自己的业务场景合理调整,read commited的弊端需要注意规避,尤其是幻读:joy:

引用来自“pain_7”的评论

:worried: RR 就没有幻读吗。。。

引用来自“java_龙”的评论

这个产生死锁的原因就是不会出现幻读的原因,可以了解一下
我的意思是 RR 跟 RC 都会出现幻读
j
java_龙

引用来自“小井井”的评论

线上我们现在基本都是默认read commited :laughing:

引用来自“java_龙”的评论

可以根据自己的业务场景合理调整,read commited的弊端需要注意规避,尤其是幻读:joy:

引用来自“pain_7”的评论

:worried: RR 就没有幻读吗。。。
这个产生死锁的原因就是不会出现幻读的原因,可以了解一下
pain_7
pain_7

引用来自“小井井”的评论

线上我们现在基本都是默认read commited :laughing:

引用来自“java_龙”的评论

可以根据自己的业务场景合理调整,read commited的弊端需要注意规避,尤其是幻读:joy:
:worried: RR 就没有幻读吗。。。
j
java_龙

引用来自“qints”的评论

小白表示学习到了很多知识~
觉得写的还可以,麻烦点个赞哟:bowtie:
j
java_龙

引用来自“伯一声”的评论

小白难看懂,没有举例的话:astonished:
(⊙o⊙)… 其实举了例的,哪一段没明白呢?
qints
qints
小白表示学习到了很多知识~
井井_
井井_

引用来自“小井井”的评论

线上我们现在基本都是默认read commited :laughing:

引用来自“java_龙”的评论

可以根据自己的业务场景合理调整,read commited的弊端需要注意规避,尤其是幻读:joy:
各个事务级别各有优缺点了,还是以业务为主。文章分析的不错,点赞 :smile:
j
java_龙

引用来自“小井井”的评论

线上我们现在基本都是默认read commited :laughing:
可以根据自己的业务场景合理调整,read commited的弊端需要注意规避,尤其是幻读:joy:
减少InnoDB死锁发生的方法有什么?MySQL学习

  死锁的情况发生在不同的的事务相互之间拥有对方需要的锁,而导致相互一直无限等待。死锁可能发生在不同的事务都会对多个相同的表和相同的行上施加锁,但事务对表的操作顺序不相同。   ...

mo默瑶
05/23
0
0
InnoDB死锁是什么情况?怎么规避?

  死锁的情况发生在不同的的事务相互之间拥有对方需要的锁,而导致相互一直无限等待。死锁可能发生在不同的事务都会对多个相同的表和相同的行上施加锁,但事务对表的操作顺序不相同。   ...

zhouzhou2018
05/23
0
0
MySQL数据库InnoDB存储引擎源代码调试跟踪分析

导读目录: 1 早期结论... 3 2 测试一:死锁检测... 4 3 测试二:cursor测试... 6 4 测试三:external_lock测试... 6 5 测试四:杂项测试... 6 6 测试五:autocommit测试... 7 7 测试六:unl...

吴问志
2011/12/06
0
0
MySQL 在并发场景下的问题及解决思路

原文出处:李平 1、背景 对于数据库系统来说在多用户并发条件下提高并发性的同时又要保证数据的一致性一直是数据库系统追求的目标,既要满足大量并发访问的需求又必须保证在此条件下数据的安...

李平
05/11
0
0
MySQL基础(三)【MySQL事务与存储引擎】

3.1-数据库事务 什么是事务 一系列有序的数据库操作: 要么全部成功 要么全部回退到操作前的状态 中间状态对其他连接不可见 事务的基本操作: 基本操作 说明 start transaction 开始事务 co...

yanfeilai528
06/26
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

day58-20180816-流利阅读笔记-待学习

苹果市值破万亿,iPhone 会涨价吗? Lala 2018-08-16 1.今日导读 苹果教父乔布斯曾经说过:“活着就是为了改变世界。”虽然他在 56 岁时就遗憾离世,但他极具创新和变革的精神早已深埋进苹果...

aibinxiao
16分钟前
4
0
[雪峰磁针石博客]python3快速入门教程1 turtle绘图-2函数

菲波那契序列: >>> # Fibonacci series:... # the sum of two elements defines the next... a, b = 0, 1>>> while b < 10:... print(b)... a, b = b, a+b...112......

python测试开发人工智能安全
今天
0
0
java环境变量配置最正确的方式

原贴:https://blog.csdn.net/qq_40007997/article/details/79784711,十分详细,亲测有效

kitty1116
今天
0
0
49.Nginx防盗链 访问控制 解析php相关 代理服务器

12.13 Nginx防盗链 12.14 Nginx访问控制 12.15 Nginx解析php相关配置(502的问题) 12.16 Nginx代理 扩展 502问题汇总 http://ask.apelearn.com/question/9109 location优先级 http://blog....

王鑫linux
今天
2
0
Nginx防盗链、访问控制、解析php相关配置、Nginx代理

一、Nginx防盗链 1. 编辑虚拟主机配置文件 vim /usr/local/nginx/conf/vhost/test.com.conf 2. 在配置文件中添加如下的内容 { expires 7d; valid_referers none blocked server_names *.tes......

芬野de博客
今天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部