超全面MySQL语句加锁分析（下篇）

编程小6 (2) 2024-10-08 08:12

Hi，大家好，我是编程小6，很荣幸遇见你，我把这些年在开发过程中遇到的问题或想法写出来，今天说一说
超全面MySQL语句加锁分析（下篇）,希望能够帮助你!!!。

INSERT语句

前边唠叨锁的细节时说过，INSERT语句一般情况下不加锁，不过当前事务在插入一条记录前需要先定位到该记录在B+树中的位置，如果该位置的下一条记录已经被加了gap锁（next-key锁也包含gap锁，之后就不强调了），那么当前事务会在该记录上加上一种类型为插入意向锁的锁，并且事务进入等待状态。关于插入意向锁由于我们之前已经详细唠叨过了，就不多说了。

下边要看的是两种INSERT语句遇到的特殊情况：

遇到重复键（duplicate key）

在插入一条新记录时，首先要做的事情其实是定位到这条新记录应该插入到B+树的哪个位置。如果定位位置时发现了有已存在记录的主键或者唯一二级索引列与待插入记录的主键或者唯一二级索引列相同（不过可以有多条记录的唯一二级索引列的值同时为NULL），那么此时此时是会报错的。比方说我们插入新记录，该记录的主键值已经被包含在hero表中了：

mysql> BEGIN; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO hero VALUES(20, 'g关羽', '蜀'); ERROR 1062 (23000): Duplicate entry '20' for key 'PRIMARY'

当然，在生成报错信息前，其实还需要做一件非常重要的事情 —— 对聚簇索引中number值为20的那条记录加S锁。不过具体的行锁类型在不同隔离级别下是不一样的：

在READ UNCOMMITTED/READ COMMITTED隔离级别下，加的是S型正经记录锁。
在REPEATABLE READ/SERIALIZABLE隔离级别下，加的是S型next-key锁。

如果是唯一二级索引列值重复，比方说我们再把普通二级索引idx_name改为唯一二级索引uk_name：

ALTER TABLE hero DROP INDEX idx_name, ADD UNIQUE KEY uk_name (name);

然后执行

mysql> BEGIN; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO hero VALUES(30, 'c曹操', '魏'); ERROR 1062 (23000): Duplicate entry 'c曹操' for key 'uk_name'

很显然，hero表中之前就包含name值为'c曹操'的记录，如果再插入一条name值为'c曹操'的新记录时，虽然插入对应的聚簇索引记录没问题，但是在插入uk_name唯一二级索引记录时便会报错，不过在报错之前还是会把name值为'c曹操'那条二级索引记录加一个S锁。需要注意的是，不管是哪个隔离级别，针对在插入新记录时遇到重复的唯一二级索引列的情况，会对已经在B+树中的唯一二级索引记录加next-key锁。

小贴士：按理说在READ UNCOMMITTED/READ COMMITTED隔离级别下，不应该出现next-key锁，这主要是考虑到如果只加正经记录锁的话，在一些情况下可能出现有多条记录的唯一二级索引列都相同的情况。当然，出现这种情况的原因比较复杂，我们这里就不多说了。

另外，如果我们使用的是INSERT ... ON DUPLICATE KEY ...这样的语法来插入记录时，如果遇到主键或者唯一二级索引列值重复的情况，会对B+树中已存在的相同键值的记录加X锁，而不是S锁。

外键检查

大家别忘了MySQL还是一个支持外键的数据库，比方说我们再为三国英雄的战马建一个表：

CREATE TABLE horse ( number INT PRIMARY KEY, horse_name VARCHAR(100), FOREIGN KEY (number) REFERENCES hero(number) )Engine=InnoDB CHARSET=utf8;

这样hero表就算是一个父表，新建的horse表就算一个子表，其中horse表的number列是参照hero表的number列。现在如果我们向子表中插入一条记录时：

如果待插入记录的number值在hero表中能找到。比方说我们为horse表中新插入的记录的number值为8，而在hero表中number值为8的记录代表曹操，他的马是绝影：mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO horse VALUES(8, '绝影');
Query OK, 1 row affected (5 min 58.04 sec)在插入成功之前，不论当前事务的隔离级别是什么，只需要直接给父表hero的number值为3的记录加一个S型正经记录锁。
如果待插入记录的number值在hero表中找不到。比方说我们为horse表中新插入的记录的number值为2，而在hero表中不存在number值为2的记录：mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO horse VALUES(5, '绝影');
Query OK, 1 row affected (5 min 58.04 sec)虽然插入失败了，但是这个过程中需要对父表hero的某些记录进行加锁：