MYSQL 锁以及死锁的产生跟解决

# InnoDB

MySQL InnoDB 存储引擎，实现的是基于多版本的并发控制协议 ——MVCC (Multi-Version Concurrency Control)

在 MVCC 并发控制中，读操作可以分成两类：
快照读 (snapshot read) 与当前读 (current read)。
快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。
当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

快照读

select * from table where ?;

当前读

select * from table where ? lock in share mode;

select * from table where ? for update;

insert into table values (…);

update table set ? where ?;

delete from table where ?;

所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。
其中，除了第一条语句，对读取记录加 S 锁 (共享锁) 外，其他的操作，都加的是 X 锁 (排它锁)。

# InnoDB 锁类型

# X 锁 or S 锁

在 InnoDb 中实现了两个标准的行级锁，可以简单的看为两个读写锁:

• S - 共享锁：又叫读锁，其他事务可以继续加共享锁，但是不能继续加排他锁。
• X - 排他锁：又叫写锁，一旦加了写锁之后，其他事务就不能加锁了。

兼容性：是指事务 A 获得一个某行某种锁之后，事务 B 同样的在这个行上尝试获取某种锁，如果能立即获取，则称锁兼容，反之叫冲突。

# 意向锁

意向锁在 InnoDB 中是表级锁，和他的名字一样他是用来表达一个事务想要获取什么。意向锁分为:

• IS - 意向共享锁 (lock in share mode): 表达一个事务想要获取一张表中某几行的共享锁。
• IX - 意向排他锁 (for update): 表达一个事务想要获取一张表中某几行的排他锁。

这个锁有什么用呢？为什么需要这个锁呢？
首先说一下如果没有这个锁，如果要给这个表加上表锁，一般的做法是去遍历每一行看看他是否有行锁，这样的话效率太低。
而我们有意向锁，只需要判断是否有意向锁即可，不需要再去一行行的去扫描。

注：意向锁之间兼容，排它锁（包含意向）跟共享锁冲突，共享锁（包含意向）之间是兼容的

# 间隙锁 (gap 锁)

<img src="/img/lock/gap.png">

间隙锁顾名思义锁间隙，不锁记录。锁间隙的意思就是锁定某一个范围，间隙锁又叫 gap 锁，其不会阻塞其他的 gap 锁，但是会阻塞插入间隙锁，这也是用来防止幻读的关键。

# 事务隔离级别

SHOW VARIABLES LIKE '%isolation%';

• Read Uncommited：可以读取未提交记录。此隔离级别，不会使用，忽略。
• Read Committed (RC)：针对当前读，RC 隔离级别保证对读取到的记录加锁 (记录锁)，存在幻读现象。
• Repeatable Read (RR)：针对当前读，RR 隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。
• Serializable：Serializable 隔离级别下，读写冲突，因此并发度急剧下降，在 MySQL/InnoDB 下不建议使用。

# 查看当前锁的状态

SELECT * FROM information_schema.INNODB_LOCKS;

# 一句简单的加锁分析

SQL1：select * from t1 where id = 10;

SQL2：delete from t1 where id = 10;

# 前提

• 前提一：当前系统的隔离级别是什么？
• 前提二：id 列是不是主键？
• 前提三：id 列如果不是主键，那么 id 列上有索引吗？
• 前提四：id 列上如果有二级索引，那么这个索引是唯一索引吗？

# 常用组合

• 组合一：id 列是主键，RC 隔离级别
• 组合二：id 列是二级唯一索引，RC 隔离级别
• 组合三：id 列是二级非唯一索引，RC 隔离级别
• 组合四：id 列上没有索引，RC 隔离级别
• 组合五：id 列是主键，RR 隔离级别
• 组合六：id 列是二级唯一索引，RR 隔离级别
• 组合七：id 列是二级非唯一索引，RR 隔离级别
• 组合八：id 列上没有索引，RR 隔离级别
• 组合九：Serializable 隔离级别

# 分析常用组合

# 组合一

<img src="/img/lock/medish.jpg">

主键为 id, 隔离级别是 RC，只需要在主键上加 X锁 。

# 组合二

<img src="/img/lock/medish2.jpg">

id 为唯一索引，假设主键为 name, 隔离级别是 RC。由于 id 是 unique 索引，因此 delete 语句会选择走 id 列的索引进行 where 条件的过滤，
在找到 id=10 的记录后，首先会将 unique 索引上的 id=10 索引记录加上 X 锁，同时，会根据读取到的 name 列，回主键索引 (聚簇索引)，
然后将聚簇索引上的 name = 'd' 对应的主键索引项加 X锁 。

问题：为什么聚簇索引上的记录也要加锁？

如果并发的一个 SQL，是通过主键索引来更新： update t1 set id = 100 where name = 'd';
此时，如果 delete 语句没有将主键索引上的记录加锁，那么并发的 update 就会感知不到 delete 语句的存在，
违背了同一记录上的更新 / 删除需要串行执行的约束。

# 组合三

<img src="/img/lock/medish3.jpg">

id 为非唯一索引，隔离级别是 RC, 相对于组合一、二，组合三又发生了变化，id 列上的约束又降低了，id 列不再唯一，只有一个普通的索引。
首先，id 列索引上，满足 id = 10 查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。
与组合二唯一的区别在于，组合二最多只有一个满足等值查询的记录，而组合三会将所有满足查询条件的记录都加锁。

# 组合四

<img src="/img/lock/medish4.jpg">

id 无索引，隔离级别是 RC, 由于 id 列上没有索引，因此只能走聚簇索引，进行全部扫描。
从图中可以看到，满足删除条件的记录有两条，但是，聚簇索引上所有的记录，都被加上了 X 锁。
无论记录是否满足条件，全部被加上 X 锁。既不是加表锁，也不是在满足条件的记录上加行锁。

# 组合五 = 组合一

# 组合六 = 组合二

# 组合七

<img src="/img/lock/medish5.jpg">

id 为非唯一索引，隔离级别是 RR, 首先，通过 id 索引定位到第一条满足查询条件的记录，加记录上的 X 锁，加 GAP 锁，然后加主键聚簇索引上的记录 X 锁，然后返回；
然后读取下一条，重复进行。直至进行到第一条不满足条件的记录，此时，不需要加记录 X 锁，但是仍旧需要加 GAP 锁，最后返回结束。

# 组合八

<img src="/img/lock/medish6.jpg">

如图，这是一个很恐怖的现象。首先，聚簇索引上的所有记录，都被加上了 X 锁。其次，聚簇索引每条记录间的间隙 (GAP)，也同时被加上了 GAP 锁。这个示例表，只有 6 条记录，一共需要 6 个记录锁，7 个 GAP 锁。试想，如果表上有 1000 万条记录呢？
在这种情况下，这个表上，除了不加锁的快照度，其他任何加锁的并发 SQL，均不能执行，不能更新，不能删除，不能插入，全表被锁死。
所以在 Repeatable Read 隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有 GAP，杜绝所有的并发 更新 / 删除 / 插入 操作。

# 组合九

Serializable 隔离级别，影响的是 SQL1：select * from t1 where id = 10; 这条 SQL。
在 RC，RR 隔离级别下，都是快照读，不加锁。但是在 Serializable 隔离级别，SQL1 会加读锁，也就是说快照读不复存在。
在 MySQL/InnoDB 中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable 隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

# 举个栗子

# 死锁

<img src="/img/lock/dead.png">

死锁：是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象。
说明有等待才会有死锁，解决死锁可以通过去掉等待，比如回滚事务。
如果出现回滚，通常来说 InnoDB 会选择回滚权重较小的事务，也就是 undo 较小的事务。

假设我们有一个用户表，然后插入几条数据：

CREATE TABLE `user` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(11) CHARACTER SET utf8mb4 DEFAULT NULL,

  `comment` varchar(11) CHARACTER SET utf8 DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `index_name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert user select 20,333,333;

insert user select 25,555,555;

insert user select 20,999,999;

然后我们开启事务 A 跟 事务 B ：

可以看见事务 A 出现被回滚了，而事务 B 成功执行。 具体每个时间点发生了什么呢？

• 时间点 3：事务 A 删除需要对 777 这个索引加上 X 锁，但是其不存在，所以只对 555-999 之间加间隙锁
• 时间点 4：同理事务 B 也对 555-999 之间加间隙锁。间隙锁之间是兼容的，所以正常执行，此时两边都有一个间隙锁
• 时间点 5：事务 B，执行 Insert 操作，首先插入意向锁，但是 555-999 之间有间隙锁，由于插入意向锁和间隙锁冲突，事务 B 阻塞，等待事务 A 释放间隙锁
• 时间点 6：事务 A 同理，等待事务 B 释放间隙锁。于是出现了 A->B,B->A 回路等待。
• 时间点 7：事务管理器检查到死锁选择回滚事务 A
• 时间点 8：事务 B 插入操作执行成功。

<img src="/img/lock/deadlock.png">

# 解决方案

• 方案一：隔离级别降级为 RC，在 RC 级别下不会加入间隙锁，所以就不会出现毛病了，但是在 RC 级别下会出现幻读，可提交读都破坏隔离性的毛病，所以这个方案不行。
• 方案二：较少的修改代码逻辑，在删除之前，可以通过快照查询 (不加锁)，如果查询没有结果，则直接插入，如果有通过主键进行删除，在之前第三节实验 2 中，通过唯一索引会降级为记录锁，所以不存在间隙锁。

# 防止死锁

• 以固定的顺序访问表和行。交叉访问更容易造成事务等待回路。
• 尽量避免大事务，占有的资源锁越多，越容易出现死锁。建议拆成小事务。
• 降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从 RR 调整为 RC，可以避免掉很多因为 gap 锁造成的死锁。
• 为表添加合理的索引。防止没有索引出现表锁，出现的死锁的概率会突增。

# 总结

做一个简单的总结，要做的完全掌握 MySQL/InnoDB 的加锁规则，甚至是其他任何数据库的加锁规则，需要具备以下的一些知识点：

• 了解数据库的一些基本理论知识：数据的存储格式 (堆组织表 vs 聚簇索引表)；并发控制协议 (MVCC vs Lock-Based CC)；Two-Phase Locking；数据库的隔离级别定义 (Isolation Level)；
• 了解 SQL 本身的执行计划 (主键扫描 vs 唯一键扫描 vs 范围扫描 vs 全表扫描)；
• 了解数据库本身的一些实现细节 (过滤条件提取；Index Condition Pushdown；Semi-Consistent Read)；
• 了解死锁产生的原因及分析的方法 (加锁顺序不一致；分析每个 SQL 的加锁顺序)