每日一博 | 死锁问题分析—详解数据库 innodb lock 机制

栏目: 数据库 · 发布时间: 5年前

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

目录

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

概述

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

问题重现与解析

代码

每日一博 | 死锁问题分析—详解数据库 innodb lock 机制

每日一博 | 死锁问题分析—详解数据库 innodb lock 机制

代码解释:

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

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

异常提示

堆栈异常: 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 语句 对插入的行设置排他(独占)锁。这个锁是一个索引记录锁,而不是间隙锁,并且不会阻止其他会话在插入的行之前区间中插入数据。

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

大演算

大演算

佩德羅.多明戈斯 / 張正苓,胡玉城 / 三采 / 2016-8-1 / 620

揭開大數據、人工智慧、機器學習的祕密, 打造人類文明史上最強大的科技——終極演算法! 有一個終極演算法,可以解開宇宙所有的祕密, 現在大家都在競爭,誰能最先解開它! .機器學習是什麼?大演算又是什麼? .大演算如何運作與發展,機器可以預測什麼? .我們可以信任機器學過的東西嗎? .商業、政治為什麼要擁抱機器學習? .不只商業與政治,醫學與科學界也亟需......一起来看看 《大演算》 这本书的介绍吧!

JS 压缩/解压工具
JS 压缩/解压工具

在线压缩/解压 JS 代码

html转js在线工具
html转js在线工具

html转js在线工具

正则表达式在线测试
正则表达式在线测试

正则表达式在线测试