MySQL-锁

前言

最近在面试时，被问到了一个MySQL死锁相关的问题，在当时并没有意识到这会产生死锁，那么就需要来学习一下MySQL中锁🔒的相关知识啦

介绍一下当时的题目：现在有一张数据表，表结构如下

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `NO_IDX` (`no`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

表中有一些简单的数据：

现在有一个方法伪代码如下:

begin;
    delete from user where no = 10;
    insert into user(no) values(10);
commit

当有多个线程同时执行如上方法时，会发生什么呢？

答案先按下不表，下面先来了解一些MySQL中锁的相关概念

共享锁（S）和排它锁（X）

从锁的级别可以大体分为两种：共享锁（share lock）和排它锁（exclusive lock），他们的区别就是在兼容性

	共享锁（S）	排它锁（X）
共享锁（S）	兼容	不兼容
排它锁（X）	不兼容	不兼容

通常情况下Select语句是不加锁的，在读已提交和可重复读两种隔离级别下，select是一种快照读。

而Insert、Update、Delete语句都是默认加排它锁的。

共享锁和排它锁根据锁的范围还可以再分为：表级、行级两种粒度。

那么什么情况下才会用行锁？什么情况下才会用表锁？

表锁和行锁

表锁

表锁的是锁整张表的，因此它的粒度是比较大的，但是加锁开销相对于行锁就小了。

一般在以下情况就会使用到表锁：

存储引擎不支持行锁，例如MyIASM引擎
语句中的查询条件没有匹配到对应的索引，或者发现使用索引还要进行全表扫描，就会变成使用表锁

行锁

行锁粒度较小，加锁开销较大，同时它还可以细分为

记录锁：即仅锁定一行
间隙锁：锁定一个范围，两边都是开区间
临键锁：锁定一个范围，并锁定记录本身

记录锁（Record Lock）

当查询条件为相等条件索引同时数据存在时使用记录锁

例如以下SQL在两个事务分别同时进行，是可以正常执行的，这是因为他们都用到了相等条件索引并数据都存在

session1	session2
begin; SELECT *FROM user where no = 6 FOR UPDATE;	begin; SELECT *FROM user where no = 5 FOR UPDATE;
正常输出	正常输出

间隙锁（Gap Lock）

那么如果使用相等条件索引但数据不存在呢？这个时候就会使用到间隙锁了，它将一个区间的锁住，用来防止这个区间有其他事务插入数据。

session1	session2
begin; SELECT *FROM user where no = 11 FOR UPDATE;	begin; INSERT INTO user(no) values(12)
	堵塞
commit
	执行成功/超时回滚

如果查询条件是范围条件也会用到间隙锁

临键锁（Next-Key Lock）

待确认

意向共享锁（IS）和意向排它锁（IX）

意向锁是一个不与行级锁冲突的表级锁，它是用来优化加表锁的效率的，事务在获得某些行的锁时，需要先获得表的意向锁

意向共享锁（intention shared lock）：事务有意对表中的某些行加上共享锁
意向排它锁（intention exclusive lock）：事务有意对表中某些行加上排它锁

	意向共享锁（IS）	意向排它锁（IX）
意向共享锁（IS）	兼容	兼容
意向排它锁（IX）	兼容	兼容

可以看到意向锁之间都是互相兼容的，但它对其他的正常表锁是互斥的

	意向共享锁（IS）	意向排它锁（IX）
共享锁（S）	兼容	不兼容
排它锁（X）	不兼容	不兼容

那么假设现在没有意向锁，有个事务需要添加表级排它锁锁，那么他需要满足两个条件

当前没有其他的事务在表上添加锁
当前没有其他的事务在表里的任何一个行添加锁

可以看到为了检测条件2，在正常条件就需要去遍历检测，但如果添加了意向锁，在添加表锁时就只需要简单判断下相关的意向锁是否存在即可。

那么意向锁的作用就是可以避免当有事务要加表锁时，不需要去一行行的扫描是否有相关的行锁，大大的提升了效率。

一致性锁定读和一致性非锁定读

一致性非锁定读

一致性非锁定读指的是当要读取的行被加上了排它锁时，这时读操作不会去等待排它锁的释放，而是会去读取行的一个快照数据。

根据事务的隔离级别读取快照的情况也会不一样

在读已提交的隔离级别中：一致性非锁定读总是读取当前行的最新快照或者当前行的最新数据
在可重复读的隔离级别中：一致性非锁定读总是读取事务开始时当前行的快照，这是为了避免不可重复读的问题

一致性锁定读

正常情况下读操作都是快照读，但在有些时候为了保证数据逻辑的一致性，我们需要对读操作显性的加锁

select … for update 显性的加一个排它锁
select … lock in share mode 显性的加一个共享锁

回顾

现在我们已经大致了解：表锁、行锁、共享锁、排它锁、意向共享锁、意向排它锁、一致性锁定读、一致性非锁定读等相关概念。那么现在回顾最初的题目。

session1	session2
begin; delete from user where no = 10;
	begin; delete from user where no = 10;
	insert into user(no) values(10);
	堵塞
insert into user(no) values(10);
堵塞

假设两个线程已如上的顺序运行，就会发生一个死锁。

当session1执行delete语句时，由于no列中不存在10值，这时就会变成临键锁，它会锁住[10，无穷大）的区间范围。
当session2执行delete语句时，同理，一样会锁住[10，无穷大）的区间范围，随后执行Insert语句，但由于session1持有相关的区间锁，因此被堵塞。
当session1在执行Insert语句时，由于session2持有相关的区间锁，也会堵塞。
这样双方session都在等待对方释放对应的区间锁，导致死锁。

解决方法有两种：

将双方事务隔离级别设置为读已提交
设置合理的堵塞超时时间