记录一次MySQL死锁的问题与引发的思考

2022-10-16T09:41:07+00:00

作为最为流行的开源数据库软件之一，MySQL有着广泛的应用。由于是比较典型的运营系统后台，12分区devops系统也选择了用它来存储数据。不过，开发过程中却意外地因为一个看似简单的数据库死锁问题卡住了许久，这里把问题和对应的知识点记录下来供以后翻阅，也避免其他人踩坑。

背景

一个服务有多个二进制，每个二进制有对应的编译信息，判断编译信息不存在时，则插入数据。

执行的时候却发现，对一个服务的多个二进制的编译信息进行插入时，发生了死锁。

简化问题

抛开业务属性，对于表building

CREATE TABLE `building` (
 `appid` int(11) DEFAULT 0,
 `buildid` int(11) DEFAULT 0,
 KEY `idx1` (`appid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

执行以下语句时，发生了死锁：

begin
    select * from building where appid = 5 for update;
    // 判断结果为空，则插入数据
    insert into building values (5, 1);
commit

即，并发执行select for update + insert时导致死锁

先解决问题

死锁的原因

首先，使用的MySQL版本为5.7.18，使用了默认的隔离级别可重复读（Repeatable read）。假设执行语句前数据库里有如下数据：

appid（有索引，起外键作用)	Build-id
10	111
20	222
30	333

执行过程如下

transaction1	transaction2
begin // 1	begin // 2
select * from building where appid = 5 for up date; // 3, 成功执行	select * from building where appid = 6 for up date; // 4, 成功执行
insert into building values (5, 444) // 5, 卡住	insert into building values (6, 555) // 6, 死锁

说明原因：

步骤3和4同时持有了gap锁，非互斥，都到了下一步
步骤5的插入意向锁与步骤4的gap锁冲突，卡住
步骤6的插入意向锁和步骤3的gap锁冲突，卡住，但是数据库发现有环，直接报错死锁
ps: 步骤6报错并回滚之后，transaction 1是可以执行的。

解决方案：

可以修改隔离级别为已提交读（Read Committed），就不会有gap lock的问题【备注：未解决业务问题】
可以修改索引类型为唯一索引，然后就不需要先select再insert了，直接insert（可能报错）或者insert ignore，insert ... on duplicate key update【备注：实际情况复杂一些，不只是appid单列的唯一索引】

引发的思考：

隔离级别有影响么？
表中包含哪些原始数据，有关系么？
语句3和4在where条件一样的时候就会互斥么？
间隙锁（gap lock）和插入意向锁(intert intention lock)作用到底是什么？
最初写这个事务的目的是为了防止同appid的并发插入问题（即上述例子中，步骤4的 where语句条件为appid=5），能正确执行么？

MySQL的知识点

隔离级别

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读(Phanto)
未提交读（Read Uncommitted）	可能	可能	可能
已提交读（Read Committed）	不可能	可能	可能
可重复读（Repeatable Read）	不可能	不可能	可能
可串行化（Serializable）	不可能	不可能	不可能

一般来讲，上表中从上到下按照隔离的级别由低到高，越高的隔离，效率越差。

脏读 :一个事务读取到另一事务未提交的更新数据
不可重复读 : 在同一事务中,多次读取同一数据返回的结果有所不同, 换句话说, 后续读取可以读到另一事务已提交的更新数据。相反, “可重复读”在同一事务中多次读取数据时, 能够保证所读数据一样, 也就是后续读取不能读到另一事务已提交的更新数据。
幻读 : 并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的select操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些select某记录是否存在，不存在，准备插入此记录，但执行insert时发现此记录已存在，无法插入，此时就发生了幻读。

不可重复读例子：

设置隔离级别为Read Committed。

transaction 1	transaction 2
begin // 1	begin // 2
select * from building where appid = 5 // 3, 空数据	insert into building values (5, 555); // 4, 成功执行
select * from building where appid = 5 // 5, 空数据	commit // 6, 成功执行
select * from building where appid = 5 // 7, 数据非空

幻读例子

设置隔离级别为Repeatable Read，将appid改为唯一索引。

transaction 1	transaction 2
begin // 1	begin // 2
select * from building where appid = 40 // 3, 没有for update 空数据	insert into building values (40, 444); // 4 成功执行
select * from building where appid = 40 // 5, 空数据	commit; // 6. 成功
select * from building where appid = 40 // 7, 空数据
insert into building values (40, 444) // 8, 报错Duplic ate entry '40'

锁分类

1. 锁类型

表锁：
- 对一整张表加锁，一般是DDL(数据定义语言)处理时使用，也可以自己在SQL中指定。由MySQL服务器实现。
- 特点：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行锁:
- 锁定某一行数据或某几行，或行和行之间的间隙。由存储引擎实现，常见的就是InnoDB。行锁是加在索引上的（MyISAM存储引擎只能使用表锁）。
- 特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
行锁细分（上面的问题主要就集中在这里介绍的锁上）
- 记录锁（Record Lock）
  - 锁直接加在索引记录上面，锁住的是key。没有显示索引时，会用数据库隐式创建的一个索引。如果Where条件中指定的列是非聚簇索引，那么记录锁不仅会加在该索引上，还会加在对应的聚簇索引上。
- 间隙锁（Gap Lock）
  - 锁定索引记录间隙，确保索引记录的间隙不变。间隙锁在事务隔离级别为可重复读或以上级别时生效。 MySQL使用间隙锁可以防止其他事务在该范围内插入或修改记录，保证两次读取这个范围内的记录不会变，从而防止出现幻读现象。对于上面的例子，执行事务前间隙包括：（-∞,10），（10,20），（20,30），（30,+∞）。执行期间where语句的条件落在了同一个间隙中，因此互相锁住。同时，值得注意的是间隙锁之间并不互斥。
- Next-Key Lock
  - 即行锁和间隙锁组合起来，是加在某条记录以及这条记录前面间隙上的锁。（疑问：实际操作发现，where命中记录时，条件前后的间隙都会加锁，而不是只有前面间隙） Next-Key锁同间隙锁一样在事务隔离级别为可重复读或以上级别时生效。Locking reads（select ... for update或lock in share mode），Update和Delete时，除了对唯一索引的唯一搜索外都会获取gap锁或next-key锁。即锁住其扫描的范围。"唯一索引的唯一搜索"表示where条件用了唯一索引且搜索到了记录。
- 插入意向锁（Insert Intention Locks）
  - 一种特殊的间隙锁，用于Insert的时候。插入意向锁只会和间隙锁或Next-Key锁冲突，和插入意向锁并不冲突。

锁兼容矩阵

事务A想对数据R获取(row)的锁，但是数据已持有(col)的锁时的兼容性。

已持有的锁\想获取的锁	Record	Gap	Next-Key	Insert Intention
Record	0	1	0	1
Gap	1	1	1	0
Next-Key	0	1	0	0
Insert Intention	1	1	1	1

第一列表示已经有的锁，第二列到第五列表示待加的锁。

注意此矩阵并不对称。
主要是一个事务已经获取了插入意向锁，对其他事务是没有任何影响的。而一个事务想要获取插入意向锁，如果有其他事务已经加了间隙锁或Next-Key锁，则会阻塞.

2. 锁模式

读锁（S）
- 又称共享锁，加了读锁的记录，所有的事务都可以读取，但是不能修改，并且可同时有多个事务对记录加读锁。
写锁（X）
- 又称排他锁，加了写锁的记录，只有拥有该锁的事务可以读取和修改，其他事务都不可以读取和修改，并且同一时间只能有一个事务加写锁。
读意向锁（IS）
- 为表级锁，当事务试图读某一条记录时，会先在表上加上读意向锁，这样判断表中是否有记录加锁就很简单了。
写意向锁（IX）
- 为表级锁，当事务试图写某一条记录时，会先在表上加上写意向锁，这样判断表中是否有记录加锁就很简单了（例如，判断其他事务已表上加IX锁，则本事务无法在表上加X锁）。

锁兼容矩阵：

已持有的锁\想获取的锁	IX	S	IS
X	0	0	0
IX	1	0	1
S	0	1	1
IS	1	1	1

3.相关命令

查看全局和会话事务隔离级别

select @@global.tx_isolation;
select @@session.tx_isolation;
select @@tx_isolation;

设置当前session的隔离级别

set session transaction isolation level repeatable read

展示事务的相关信息（包括锁）

show engine innodb status // 结果中的TRANSACTIONS部分

展示锁相关信息

select * from information_schema.innodb_locks;

细分问题的解答

隔离级别有影响么？

答：有，RR隔离级别及以上才有Gap lock, Next-Key lock。

表中包含哪些原始数据，有关系么？

有，影响Gap lock的范围

语句3和4在where条件一样的时候就会互斥么？

不是，需要where中使用唯一索引且命中记录

gap lock和intert intention lock作用到底是什么？
最初写这个事务的目的是为了防止同appid的并发插入问题（即上述例子中，步骤4的where语句条件为appid=5，能正确执行么？

这个问题需要再稍微展开说明一下。其实最初select + insert的本意是为了先查找某条数据（的索引），确认其不存在，然后插入数据。没意识到查找会影响其他数据（即上面的appid = 5和appid = 6互相影响了）。
而用select ... where ... for update的初衷应该是希望对同一个索引有相互阻塞作用，讲人话就是想要在一条不存在的记录上加上互斥的读锁（如果已存在的话，insert不会发生，不会产生额外的数据，也就无所谓了）。
那么，这个情况是MySQL能解决的么？
- 很遗憾，答案是不能。
- 可这种情况确实存在怎么办呢？可能有如下的解法：
  1. 用mutex table。
    - 即在另一张有唯一索引，且确保索引对应的记录存在的行上进行select ... for update操作。对上面的例子来说，select * from app where id = 5 for update即可（因为用appid=5查building的时候，id为5的app记录已经存在了）
  2. 改用唯一索引，并舍弃掉select...for update操作。
    - 可以改用insert ignore即可，让MySQL自身保证不会有重复数据。
  3. 重试。
    - 不管是前面例子的问题，还是本问题，其实MySQL发现死锁后，会立即回滚，结果就是有一个事务会成功。失败的事务，重试即可。【备注：并发多的时候，可能会需要重试多次】
  4. 用GET_LOCK / RELEASE_LOCK。
  5. 用redis等其他系统来加锁。
上面的例子中方法1，2，3应该都可以。值得注意的是，部分情况下2不可行，例如判断如果不存在，则一次插入多条数据的情况。

Jettcc Club - 问题记录