a表中不存在就插入_不知道MySQL锁没关系，图解+实操，一文带你了解清清楚楚...-CSDN博客

本文链接：https://blog.csdn.net/weixin_35349247/article/details/112101853

在最一开始学习数据库的时候，我们就知道数据库有一个特性：保证数据的执行，虽然数据库有这么多，但是这是一种规则，那MySQL肯定也不例外，但是同样的，MySQL由于其自身所有的架构特性，存储引擎的选择也存在多样化，针对的应用场景也不太一样，导致的结果就是不同的存储引擎所设计的锁机制也不太一样，那在工作和面试中该怎么处理呢？今天，我们就看一下最常见的InnoDB的相关锁机制吧

不要觉得难，有图有实操，跟着代码学理论，这才是程序员该有的学习方式，看正题

InnoDB锁分类

对InnoDB锁按颗粒度进行划分，可分为表锁和行锁

行锁有以下几种：

• 共享锁与排他锁(Shared and Exclusive Lock)。

• 记录锁(Record Lock)。

• 间隙锁(Gap Lock)。

• 记录锁与间隙锁的组合(Next-Key Lock)。

• 插入意向锁(Insert Intention Lock)。

表锁有以下几种：

• 意向锁(Intention Lock)。

• 自增锁(AUTO-INC Lock)。

下面对每一种锁分别进行介绍。

行锁

1. 共享锁与排他锁

行共享锁(S)与排他锁(X)较好理解，S锁与X锁互相冲突。

• 当读取一行记录时，为了防止别人修改，则需要添加S锁。

• 当修改一行记录时，为了防止别人同时进行修改，则需要添加X锁。

这里需要知道MySQL具有MVCC特性，所以，在通常情况下，普通的查询属于非锁定读，不会添加任何锁(即一致性读)。还有一种是锁定读(即当前读)，例如：

• SELECT… FOR SHARE(MySQL 8.0中新增的方式，在以前版本中上锁方式为SELECT ... LOCK IN SHARE MODE)，添加S锁，其他事务可以读但修改会被阻塞。

• SELECT… FOR UPDATE，添加X锁，其他事务修改或者执行SELECT … FOR SHARE都会被阻塞。

2. 记录锁

在MySQL中记录锁都是添加在索引上的，即使表中没有索引，也会在默认创建的聚集索引上添加记录锁。

3. 间隙锁

间隙锁的锁定范围是索引记录之间的间隙，或者第一条或最后一条索引记录之前的间隙，间隙锁是针对事务隔离级别为可重复读或以上级别的。例如一个事务执行SELECT * FROM t WHERE c1 > 10 AND c1 < 20 FOR UPDATE ;，那么当插入c1=15时就会被阻塞；否则，再次查询得到的结果就与第一次不一致。

4. 记录锁与间隙锁的组合

Next-Key Lock是记录锁与间隙锁的组合，也就是索引记录本身加上之前的间隙。间隙锁保证在REPEATABLE-READ级别下不会出现幻读现象，防止在同一个事务内得到的结果不一致。间隙锁在执行show engine innodb 时输出结果如下(后面会有详细解释)：

5. 插入意向锁

插入意向锁是针对INSERT操作设置的一种特殊的间隙锁，主要是为了优化INSERT操作的并发能力。这个锁表示插入的意图，即插入具有相同索引间隙的多个事务，如果插入的值不同，则不需要互相等待。

假设存在值为4和7的索引记录，现在分别尝试插入值为5和6的事务，在获取插入行上的排他锁之前，会添加插入意向锁锁定4和7之间的间隙，但是不会互相阻塞，因为插入的行是不冲突的。

注意：插入意向锁之间是不冲突的，但是插入意向锁可能和其他锁是冲突的，比如Next-Key Lock。

表锁

1. 意向锁

意向锁在MySQL中是表级别锁，表示将来要对表添加什么类型的锁(IX/IS)。

• SELECT… FOR SHARE，添加意向共享锁(IS)。

• SELECT … FOR UPDATE，添加意向排他锁(IX)。

在获取表中某行的共享锁之前，首先必须获取表的IS锁。在获取表中某行的独占锁之

前，首先必须获取表的IX锁。意向锁和行锁之间的冲突及兼容列表如表20-1所示。

意向锁不会阻止除表锁请求(例如，执行LOCK table … WRITE语句)之外的锁。

可以这样理解：

在申请表锁(执行LOCK table语句)时表记录不能存在锁，在没有意向锁的情况下，就需要扫描表中每一条记录，查看记录上是否存在锁；但是有了意向锁之后，只要判断表上是否存在意向锁即可，如果有意向锁存在，则说明表中某行记录已被锁定或者将要被锁定，表锁的申请语句(LOCK table)会等待，意向锁设计提高了效率。

2. 自增锁

自增锁是插入到具有AUTO_INCREMENT字段的表中的事务所采用的特殊表级锁。在最简单的情况下，如果一个事务正在向表中插入值，则其他任何事务都必须等待插入语句执行完成，这样才能保证后面事务插入的主键值是连续的。

innodb_autoinc_lock_mode参数用于控制自增锁的算法，通过控制自增值生成的策略来提高并发能力。

锁模式对应的含义

当我们使用show engine innodb语句查看锁信息时，经常会看到LOCK_MODE字段，也就是锁模式，只有知道各种模式都分别代表什么意思才能更好地去分析锁等待和死锁问题。这是一个非常重要的知识点。

• IX：代表意向排他锁。

• X：代表Next-Key Lock锁定记录本身和记录之前的间隙(X)。

• S：代表Next-Key Lock锁定记录本身和记录之前的间隙(S)。

• X, REC_NOT_GAP：代表只锁定记录本身(X)。

• S, REC_NOT_GAP：代表只锁定记录本身(S)。

• X, GAP：代表间隙锁，不锁定记录本身(X)。

• S, GAP：代表间隙锁，不锁定记录本身(S)。

• X, GAP,INSERT_INTENTION：代表插入意向锁。

加锁验证

前面已经介绍了InnoDB中相关的锁概念，相信很多MySQL DBA或开发人员对这些锁都有所了解，也知道它们的作用，但问起具体加锁情况时，则很难做出比较详细的解释和清晰的描述，比如锁和事务隔离级别的关系，以及和主键、索引之间是否有影响等。下面我们就通过一些测试来解惑。

设置参数：GLOBAL innodb_status_output_locks=ON，然后执行show engine innodb语句，可以打印出更多的锁信息，在未发生锁等待时也能看到持有锁情况，对于我们理解各种加锁情况非常有帮助。

创建一个表t，没有索引和主键，并插入测试数据：

CREATE TABLE t(id int (11)DEFAULT NULL,name  char(20) DEFAULT NULL);insert into t values (10, ' donghongyu') ,(20,'lichun'), (30,'luoxiaobo' ) ;

REPEATABLE-READ隔离级别+表无显式主键和索引

手动开启事务，执行语句并采用for update方式(当前读)：

这里可以采用两种方式来查看持有锁的信息。

第一种方式是使用show engine innodb语句查看：

第二种方式是通过performance_schema.data_locks表查看：

通过返回的信息可以看到，对表添加了IX锁(在获取表中某行的独占锁之前，首先必须获取表的IX锁)和4个记录锁。在表中的3条记录上分别添加了Next-Key Lock锁(LOCK_MODE字段显示的是“X”)，防止有数据变化发生幻读，例如进行了更新、删除操作。那么“0:len 8; hex 73757072656d756d; asc supremum;;”是什么意思呢？在REPEATABLE-READ隔离级别下，为了防止发生幻读，会将最大索引值之后的间隙锁住并用“supremum”表示高于表中任何一个索引的值。整体加锁的顺序是：

①对表添加IX锁。

②在“supremum”上添加Next-Key Lock锁。

③在3条记录上分别添加Next-Key Lock锁。

这里有读者会有疑问，这是否和执行语句没有where条件有关？如果带有where条件，则不会有Next-Key Lock锁。下面我们测试一下。

查看performance_schema.data_lock表：

我们看到锁信息与之前一样，同样会有“supremum pseudo-record”。正如前面所讲的，虽然where条件是id= 10，但是每次插入记录时所生成的聚集索引(DB_ROW_ID)还是自增的，每次都会在表的最后插入，所以就有可能插入id = 10这条记录。因此，需要添加一条“supremum pseudo-record”防止数据插入。产生自增id的代码如下：

这里还有一个问题：为什么有了where条件，但是还会在不满足where条件的记录上添加Next-Key Lock锁呢？这主要也是为了防止发生幻读。如果不添加Next-Key Lock锁，这时若有其他会话执行DELETE或者UPDATE语句，则都会造成幻读。在READ-

COMMITTED隔离级别下，对于不满足where条件的记录会释放得早一些。

下面我们看一下表有显式主键的情况。这时插入任何数据都会被阻塞，因为都是在表的最后插入的，会与“supremum pseudo-record”发生冲突。

锁信息如下:

通过锁冲突信息可以清楚地看到，是申请添加的插入意向锁与“supremum pseudo- record”发生了冲突，这也证明了插入时都是在表的最后插入的。

REPEATABLE-READ隔离级别+表有显式主键无索引

这里我们要分如下几种情况进行分析，对于不同的情况加锁方式也有所不同。

• 不带where条件。

• where条件是主键字段。

• where条件包含主键字段和非主键字段。

表结构如下：

1. 不带where条件

不带where条件的情况相对比较简单，相信大家也能推测出加锁方式，我们通过实际操作来验证一下。

查看data_locks表的加锁情况：

可以看到，没有where条件时加锁方式与20.2.1节中不带where条件的加锁方式相同，

有where条件就不同了。

2. where条件是主键字段

查看data_locks表的加锁情况：

可以看到，只对表添加了IX锁和对主键添加了记录锁(X, REC_NOT_GAP)，并且只锁住了where条件id= 10这条记录。因为主键已经保证了唯一性，所以在插入时就不会是id = 10这条记录了。因此，这里也不需要间隙锁。

3. where条件包含主键字段和非主键字段

查看data_locks表的加锁情况：

可以看到，加锁方式与where条件是主键字段的加锁方式相同，因为根据主键字段可以直接定位一条记录。

REPEATABLE-READ隔离级别+表无显式主键有索引

这里分如下几种情况。

• 不带where条件。

• 普通索引：

■ where条件是索引字段。

■ where条件包含索引字段和非索引字段。

• 唯一索引：

■ where条件是索引字段。

■ where条件包含索引字段和非索引字段。

1. 不带where条件

这种情况的加锁方式与20.2.1节中不带where条件的加锁方式相同，相信大家也能理解为何这样加锁，这里就不演示了，我们重点看一下不一样的地方。

2. 普通索引

(1) where条件是索引字段

查看data_locks表的加锁情况：

当where条件是普通索引字段时，加锁顺序是：

①对表添加IX锁。

②对id = 10对应的索引添加Next-Key Lock锁，区间是(-∞, 10]。

③对索引对应的聚集索引添加X记录锁。

④为防止发生幻读(因为是普通索引，所以可以再插入id = 10这条记录)，对索引记录区间(10,20)添加间隙锁。

这时如果插入id= 9到id = 19之间的记录都会被阻塞，而插入id = 20这条记录则不会被阻塞，因为它不在间隙锁范围内。

当插入id = 9这条记录时同样需要添加间隙锁(其实是插入意向锁，当发生锁等待时使用show engine innodb status语句可以看到lock_mode X locks gap before rec insert intention waiting)，与id = 10这条记录的Next-Key Lock锁发生了冲突，所以看到申请X锁、间隙锁、插入意向锁时状态是WAITING。

当插入id = 19这条记录时同样需要添加间隙锁，与id = 10这条记录的间隙锁发生了冲突，所以看到申请X锁、间隙锁、插入意向锁时状态是WAITING。

但如果插入id = 20这条记录，则不会被阻塞。

(2) where条件包含索引字段和非索引字段

此情况与where条件是普通索引字段的情况相同，这里不再演示。

3. 唯一索引

(1) where条件是索引字段

这里与“where条件是主键字段”的加锁情况相同，表无显式主键则会把唯一索引作为主键，因为是主键，所以不能再插入id = 10这条记录了。因此，这里也不需要间隙锁。

(2) where条件包含索引字段和非索引字段

此情况与where条件是唯一索引字段的情况相同，这里不再演示。

REPEATABLE-READ隔离级别+表有显式主键和索引

这里分如下几种情况。

• 表有显式主键和普通索引：

■ 不带where条件。

■ where条件是普通索引字段。

■ where条件是主键字段。

■ where条件同时包含普通索引字段和主键字段。

• 有显式主键和唯一索引：

■ 不带where条件。

■ where条件是唯一索引字段。

■ where条件是主键字段。

■ where条件同时包含唯一索引字段和主键字段。

1. 表有显式主键和普通索引

表结构如下(id字段是主键，name字段是普通索引)：

(1) 不带where条件

这里加锁就比较多了，加锁顺序如下：

①对表添加IX锁。

②对supremum pseudo-record添加Next-Key Lock锁。

③对索引添加Next-Key Lock锁。

④对主键索引添加X记录锁。

(2) where条件是普通索引字段

此情况与“where条件是索引字段”情况相同，只因索引字段不同，加锁的是name字段而已。

(3) where条件是主键字段

此情况与20.2.2节中的“where条件是主键字段”情况相同，这里不再演示。

(4) where条件同时包含普通索引字段和主键字段

在这种情况下，要看SQL执行计划用到的是主键索引还是普通索引，如果是主键索引，则与20.2.2节中的“where条件是主键字段”情况相同；如果是普通索引，则与20.2.3节中的“where条件是索引字段”情况相同，只因索引字段名称不同，加锁的是name字段而已。

2. 表有显式主键和唯一索引

(1) 不带where条件

此情况与20.2.4节中的“不带where条件”的加锁情况相同，这里不再演示。

(2) where条件是唯一索引字段

此情况where条件是索引字段”的加锁情况相同，这里不再演示。

(3) where条件是主键字段

此情况与20.2.2节中的“where条件是主键字段”的加锁情况相同，这里不再演示。

(4) where条件同时包含唯一索引字段和主键字段

READ-COMMITTED隔离级别+表无显式主键和索引

先看一下不带where条件的情况。

与REPEATABLE-READ隔离级别不同，在READ-COMMITTED隔离级别下添加的锁都是X记录锁，而不是间隙锁。

再看一下where条件的情况。

这里与REPEATABLE-READ隔离级别也有所不同，在REPEATABLE-READ隔离级别下会对不满足id = 10的记录添加X锁，而在READ-COMMITTED隔离级别下则不会。其实是因为在REPEATABLE-READ隔离级别下为避免发生幻读，对不满足的记录释放锁的时间会晚一些(持有锁到事务结束)，而在READ-COMMITTED隔离级别下会提前一些时间。有兴趣的读者可以尝试看一下在这两种隔离级别下对unlock_row函数打断点后的效果。