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

目录

• 概述
• 问题重现与解析
• 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 语句 对插入的行设置排他（独占）锁。这个锁是一个索引记录锁，而不是间隙锁，并且不会阻止其他会话在插入的行之前区间中插入数据。