首页 > itarticle > MySQL常见加锁场景分析一

MySQL常见加锁场景分析一

admin 12月 08, 2021 0

前言

在《MySQL中到底有哪些锁》一文中，描述了MySQL中存在的各种各样的锁，也大致说了一下各种锁出现的场景，但是整个文章通读下来，大家可能知道有这些锁了，或者啥也没有记住。今天这篇文章会找些实际的例子，来具体说说SQL加了什么锁。这样也好让大家加深理解，以便更好的解决真实开发中遇到的死锁问题。

你记住了么

加锁的约束

一个SQL对记录加什么锁，这个里面其实有很多的道道的，不是一句话两句话就能说的清楚。（那你就长话短说呗）那么废话不多说，就先来说一说SQL为记录加锁到底有哪些条件约束。

  1. 事务隔离级别：前面文章也说到过，间隙锁在一般情况下，只有在不小于可重复读的事务隔离级别下出现，但是特殊情况下，读已提交的隔离级别下也会有。这就很明确的告诉我们，SQL给记录加什么锁是受到事务隔离级别的影响的。
  2. SQL执行中是否使用使用索引和索引的类型：我们都知道索引可以分为普通索引、唯一索引、主键索引、联合索引和全文索引等待。MySQL的锁也是基于索引实现的，SQL用到不同的索引和没有到索引，记录上加的锁肯定是不一样的。
  3. 查询的方式：SQL是精确查询、模糊查询还是范围查询。
  4. SQL语句的类型：SELECT、INSERT、UPDATE、DELETE语句对记录加锁是不一样的。

知识补充

  1. 锁定读：指的是以下四种语句：
  • SELECT …… LOCK IN SHARE MODE
  • SELECT …… FOR UPDATE
  • UPDATE
  • DELETE

快照读：

  • 在读未提交的级别下，每次都读取记录的最新版本。
  • 在读已提交的级别下，每次都读取已经提交的版本。
  1. 事务隔离级别对加锁的影响

MySQL 的事务隔离等级对加什么锁有很大的影响，所以在分析具体的加锁场景时，首先要做的就是确定当前的是事务隔离级别。

  • 读未提交（Read Uncommitted 后续简称 RU）：读未提交，该级别下脏读、不可重复读、幻读都可能发生。基本没有该隔离级别应用的业务场景，所以直接忽略。

  • 读已提交（Read Committed 后续简称 RC）：该级别下不可重复读、幻读都可能发生，脏读不允许发生。对当前读获取的数据加记录锁。

  • 可重复读（Repeatable Read 后续简称 RR）：该级别下幻读都可能发生，脏读、不可重复读不可能发生，对当前读获取的数据加记录锁，同时对涉及的范围加间隙锁，防止新的数据插入导致幻读。

  • 序列化（Serializable）：不在使用 MVCC 并发控制，直接基于锁的并发控制，不存在快照读，都是当前读，并发效率急剧下降。该事物隔离级别不建议使用，所以也不是本文重点。

读未提交和序列化两个隔离级别因为各自的问题在实际的业务场景用的不多，本文就不对他俩再添笔墨，我们将重点关注读已提交和可重复读。

对于这两个隔离级别，你们项目是选择了哪个？那又为什么选择某个级别，你有想过么？

在这里给出个结论：一般情况下，在读已提交事务隔离级别下，加锁的基本单位是记录锁；在可重复读事务隔离级别下，加锁的基本单位是next key锁。另外，无论是在哪个隔离级别下，只要是唯一性搜索，并且读取的记录没有被标记删除，就为读取到的记录加记录锁。

满足以下四个条件就可以称之为唯一性搜索：

  1. 匹配模式为精确匹配
  2. 使用的索引为主键或者唯一二级索引
  3. 如果使用的索引是唯一二级索引，那么该索引必须不能存NULL值
  4. 如果唯一二级索引包含多个列，那每个列都要被用到（注意联合索引的最左匹配原则）

那接下来我们就来举例说明不同的SQL到底加了啥锁？

实验准备

在具体举例说明之前，我们先准备一些实验材料，例如创建个表test，具体机构如下：

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT '名称',
  `no` varchar(32) NOT NULL COMMENT '编号',
  `city` varchar(255) NOT NULL COMMENT '所在城市',
  `gender` char(1) NOT NULL COMMENT '性别',
  `remark` varchar(32) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_no` (`no`) USING BTREE,
  KEY `idx_name` (`name`) USING BTREE,
  KEY `idx_city_gender` (`city`,`gender`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
复制代码

往表test里插入一些数据：

INSERT INTO `test` VALUES ('5', '张三', '133132', 'n南京', '男', 'epbujch');
INSERT INTO `test` VALUES ('9', '王五', '123009', 'h合肥', '男', 'epbujchuf1k');
INSERT INTO `test` VALUES ('10', '李翔', '123000', 'n南京', '男', '3roa0');
INSERT INTO `test` VALUES ('15', '李建', '123132', 'b北京', '男', 'g3ruwrnoa0');
INSERT INTO `test` VALUES ('16', '李四', '123001', 's上海', '男', 'ufuwrn1kg3roa0');
INSERT INTO `test` VALUES ('17', '李丽', '123002', 'g广东', '女', 'ejchufuwrn1kg');
复制代码

在读已提交的事务隔离级别下

SQL到底加什么锁由于涉及到很多条件，所以这里先限定事务隔离级别：读已提交。

普通的SELECT语句

一般的情况下，系统里都是读多写少，那么我们就先看看普通的SELECT语句会加什么锁。例如下面的SQL：

SELECT * FROM test WHERE id = 10;
复制代码

上面的SQL中where条件id=10，其实不管他是否是主键、其他的索引还是没有用到索引，他都不会对任何数据加锁，他只是一个普通的SQL语句，只进行快照读，只是在不同的事务隔离级别下读取的快照不同：

  • 在读已提交（READ COMMITTED）隔离级别下，在每次执行普通的SELECT语句是都会生成一个ReadView，这里理解成执行的时候生成一个快照。执行时不加锁。
  • 在可重复读（REPEATABLE READ）隔离级别下，只在第一次执行普通的SELECT语句时生成一个ReadView，之后的SELECT操作都复用这个ReadView。执行时不加锁。

结论： 普通SELECT语句不加锁。

加锁的流程

MySQL中读取符合条件的记录大概过程如下：

  1. 首先要定位到符合边界条件(即用到的索引)的第一条记录，把该记录作为当前记录。

  2. 给当前记录加锁。

不同的事务隔离级别加锁的类型不同，在读已提交级别下加记录锁；在可重复读的隔离级别下加next key 锁。

  1. 判断索引下推的条件是否成立。

就是把查询中使用到的二级索引相关的条件下推到存储引擎中去判断。如果当前记录符合下推到引擎层的条件，那么跳到步骤4继续执行。若是不符合直接获取当前记录的下一条记录，作为第一条记录，并跳回步骤2，然后继续执行。前面在判断是否符合下推的条件的同时，还会判断记录是否符合边界条件和是否是最后一条记录，若不符合或者是最后一条记录则直接向server层返回“查询完毕”的信息。另外，该步骤中，不管记录是否符合所有的条件，都不释放步骤2中加的锁。

  1. 执行回表操作。

如读取的是二级索引记录，则需要进行回表操作，获取到记录对应的聚簇索引记录并给改聚簇索引记录加记录锁。

  1. 判断边界条件是否成立。

记录符合边界条件，则跳到步骤6继续执行，否则在读已提交事务隔离级别下，释放掉加的锁；在可重复读的隔离级别下，不释放锁；并且向server层返回“查询完毕”的信息。

  1. server层判断其余的条件是否成立。

除去索引下推的条件，其余的条件server层要判断条件是否成立。成立则将记录发送到客户端，不成立则在读已提交事务隔离级别下，释放掉加的锁；在可重复读的隔离级别下，不释放锁。

  1. 获取刚刚操作的记录所在的单向链表的下一条记录，当做第一条记录跳到第二步，然后重复上述的步骤。

通过主键到聚餐索引中获取完整的记录的过程就叫做回表，因为二级索引下面携带的主键信息，他不是相邻的id，即携带的主键是无序的，所以这个回表的过程是一个随机耗时的io操作。

索引下推（Index Condition PushDown，ICP），其实就是在查询过程中使用到的二级索引与之相关的搜索条件下推到存储引擎中判断，不是像之前返回到server层再判断。索引下推是一个使用到二级索引时一个简单但是有效的优化措施，他减少了回表次数。他适用于二级索引中的联合索引，且只适合SELECT语句，其他类型的SQL无效。

使用主键进行等值查询的场景
使用SELECT ... LOCK IN SHARE MODE的场景

假设有以下的SQL：

begin;
SELECT * FROM test WHERE id=5 LOCK IN SHARE MODE;

复制代码

为啥要在事务中使用SELECT ... LOCK IN SHARE MODE，因为在MySQL中，如果不显示的开启事务，那么每一条语句均为一个独立的事务。这样“SELECT * FROM test LOCK IN SHARE MODE;”的语句，一瞬间就执行完毕了，看不出加锁的效果。

**结论：**上面那个语句执行时需要访问聚餐索引中id=5的记录，给该记录加S型记录锁。如果id=5不存在则不加锁。

使用SELECT ... FOR UPDATE的场景

假设有以下的SQL：

begin;
SELECT * FROM test WHERE id=5 FOR UPDATE;

复制代码

**结论：**上面那个语句执行时需要访问聚簇索引中id=5的记录，给该记录加X型记录锁。如果id=5不存在则不加锁。

使用 UPDATE 的场景

使用UPDATE的场景里面还需要区分两种情况，一种是更新了索引，另外一种是没有更新索引的。

情况1：

UPDATE test SET remark = 'xx' WHERE id = 5;
复制代码

这里更新的字段没有涉及到索引列，所以加锁情况和使用SELECT ... FOR UPDATE的场景相同，就是给主键加了 一个X型排他锁。

情况2：

UPDATE test SET no = '133132' WHERE id = 5;
复制代码

上面的UPDATE的语句更新了二级索引列，他其实是用id = 5条件先定位到具体的聚簇索引位置，给该条记录加X型记录锁，然后由于他要更新二级索引列，所以也要给 索引 uk_no对应的记录加锁，也加上X型记录锁。

结论： 先给主键加X型记录锁再给对应的二级索引加上X型记录锁。

使用 DELETE 的场景
 DELETE  FROM test  WHERE id = 5;
复制代码

DELETE不用多说，这种肯定也是加X型排他锁的情况。他的执行过程其实就是先在B+树中定位到这条记录的位置，然后获取这个记录的X锁，最后执行delete mark操作。（删除一条记录只是打一个标记，并不是马上删除的）如果有涉及到二级索引的，肯定是在获取主键的X型记录锁之后，再获取二级索引对应记录的X型记录锁。

结论： 先给主键加X型记录锁，若有涉及到二级索引的，再给二级索引对应记录的X型记录锁。看这个结论其实和前面 UPDATE的第二种情况的加锁结论相同。

使用主键进行范围查询的场景
使用SELECT ... LOCK IN SHARE MODE的场景

假设有以下的SQL：

begin;
SELECT * FROM test WHERE id <= 15 LOCK IN SHARE MODE;

复制代码

这个SQL执行的时候，首先需要定位到符合条件的第一条记录id=5，然后顺着单向列表一个个往后找记录，符合条件的给加上S型记录锁。直到找到id=15的之后，正常来说他是一个主键，主键的特性就是全局唯一，id=15之后的记录，id一定是比他大的，但是实际上，SQL还是向后找到id=16的记录，先给他加锁，判断条件不符合之后，MySQL的Server再释放掉锁。所以这个过程中id=16的记录有先加锁，再释放锁的过程。

**结论：**上述SQL给记录[5,9,10,15]加S型记录锁，id=16的记录先加锁后又释放锁。

使用SELECT ... FOR UPDATE的场景

和使用SELECT ... LOCK IN SHARE MODE的场景类似，只不过加的是X型记录锁。

使用 UPDATE 的场景

使用UPDATE的场景里面还需要区分两种情况，一种是更新了索引，另外一种是没有更新索引的。

  • 更新二级索引

直接先给聚簇索引加X锁，然后给对应二级索引加X锁，依次类推。

  • 未更新二级索引

只给聚簇索引加X锁，依次类推。

注意上面说到的边界记录，先加锁后又判断条件不符合又释放锁的情况

使用 DELETE 的场景

使用 DELETE 的和使用 UPDATE 的场景相同，就不在赘述了。

使用二级索引进行等值查询的场景

这里的二级索引，不单单指的是普通的二级索引，也包含唯一二级索引。因为两者在加锁的过程是一样的，这里就一起说明了。
我们都知道二级索引相对于聚簇索引的区别，类似下面图一样：

二级索引相对于聚簇索引的区别

二级索引只保存索引列和主键，聚簇索引保存整个记录的数据。假设有SQL对二级索引加锁，但是不对主键进行加锁，会出现什么问题？这样就会有其他的事务对主键进行修改，导致二级索引对应的数据发生了错误；所以某事务对二级索引加锁时，同时也要获取聚餐索引上的锁。

使用SELECT ... LOCK IN SHARE MODE的场景
SELECT * FROM test WHERE no='123132'  LOCK IN SHARE MODE;
复制代码

上面的SQL，他首先通过二级索引 uk_no定位到满足no='123132'条件的记录，给他加上S型记录锁，再回表给对应的聚簇索引加一个S型记录锁。

这里的二级索引如果是一个普通的二级索引，譬如是idx_name,结果也是一样的。那是因为Innodb引擎对等值匹配的条件进行了特殊的处理，规定Innodb引擎在处理等值匹配时，在查找当前记录的下一条记录时，在对其加锁前要直接判断该记录是否满足等值匹配的条件，如果不满足直接返回，满足再加锁之后返回给server层。

**结论：**使用二级索引进行SELECT ... LOCK IN SHARE MODE时对二级索引及其后面的索引加S型记录锁，同时给聚簇索引也加S型记录锁。

使用SELECT ... FOR UPDATE的场景
SELECT * FROM test WHERE no='123132'  LOCK IN SHARE MODE;
复制代码

这个加锁基本上和使用 SELECT ... LOCK IN SHARE MODE的场景类似，区别只在于前者加的是S型记录锁，而这里给二级索引和聚簇索引加的是X型记录锁。

使用 UPDATE 的场景

这个和 SELECT ... FOR UPDATE的场景类似，只更新普通的字段时，只给二级索引和对应的聚簇索引加X型记录锁。若同时更新了其他的索引，也要给对应的索引记录加X型记录锁。

使用 DELETE 的场景

这个和UPDATE的场景相同，就不重复说了。

使用二级索引进行范围查询的场景

这里的二级索引包含普通的二级索引和唯一二级索引。

使用SELECT ... LOCK IN SHARE MODE的场景
SELECT * FROM test FORCE INDEX(uk_no) WHERE no<='123001'  LOCK IN SHARE MODE;
复制代码

因为目前表里的数据太少，MySQL有可能认为全表扫描更快就不走索引了，这里为了不干扰结果，所以这里用FORCE INDEX强制让SQL走索引。

**结论：**按照前文的SQL加锁流程的叙述，这里给uk_no索引[123000,123001,123002]加S型记录锁,以及对应的聚簇索引[10,16]也加S型记录锁

使用SELECT ... FOR UPDATE的场景

这个和使用SELECT ... LOCK IN SHARE MODE的场景类似，只不过加的是X型记录锁。

结论： 按照前文的SQL加锁流程的叙述，这里给uk_no索引[123000,123001,123002]加X型记录锁,以及对应的聚簇索引[10,16]也加X型记录锁。

使用 UPDATE 的场景

同理，UPDATE也是分两种情况，更新了索引和未更新索引。

# SQL1
UPDATE test SET remark='222' WHERE no<='123002';
# SQL2
UPDATE test SET name='大刀王五' WHERE no<='123002';
复制代码

假设SQL1使用到了索引uk_no,上面的SQL也就是先给索引uk_no[123000,123001,123002,123009]加X型记录锁和对应的聚簇索引记录[10,16，17]也加X型记录锁。

假设SQL2使用到了索引uk_no,上面的SQL也就是先给索引uk_no[123000,123001,123002,123009]加X型记录锁、索引idx_name[李翔,李四,李丽]和对应的聚簇索引记录[10,16,17]也加X型记录锁。

使用 DELETE 的场景

这个和UPDATE的场景相同，就不重复说了。

使用 INSERT 的场景

INSERT加什么锁情况比较复杂。一般情况下执行INSERT语句是不需要加锁的，但是如果一个事务T1插入数据，另外一个事务T2给这个数据使用DELETE、UPDATE、INSERT、SELECT ... LOCK IN SHARE MODE甚至是SELECT ... FOR UPDATE加锁，那就是不一样的情况了。在前面的文章(MySQL中到底有哪些锁)中有提到过这个场景。INSERT这里的大部分场景可以通过SQL展现具体加了什么锁，能展示的我就直接给出展示，毕竟要以事实说话嘛！

在可重复读的事务隔离级别下，如果一个即将插入的间隙已经被其他事务加了Gap锁，则本次的插入操作会阻塞，并将间隙上加入一个自己的间隙锁。这个是可重复读与读已提交事务隔离级别的差异。

假设事务T1插入了一条记录，但是未提交事务。这个时候其他事务，使用DELETE、UPDATE、INSERT、SELECT ... LOCK IN SHARE MODE甚至是SELECT ... FOR UPDATE加锁，这个时候MySQL是怎么处理的？

这些情况想都不要想，其他事务肯定是不能获取到这条记录的任何形式的锁，为什么这么说？还是那个老一套的答案：脏读、脏写的问题。

  • 假设其他事务T2能够获取这条记录的S锁，那不就是一个事务读取了另一个事务未提交的数据，这种情况不就是脏读。
  • 假设其他事务T2能够获取这条记录的X锁进行修改，那不就是一个事务修改了另一个未提交事务修改的数据，这不就是发生了脏写。

这又是脏读又是脏写的，这个是可用事务不可容忍的。所以MySQL开发者为了解决这个问题做了以下的动作：事务T2帮助事务T1，让事务T1给记录加一个X锁，事务T2自己为获得对应的锁进行等待。 事情又完美解决了，不会发生脏读或者幻读了。

接下来我们就来一一介绍不同场景的加锁区别：

  1. 同一个SQL在不同事务中执行
BEGIN;
INSERT INTO test ( name, no, city, gender, remark) 
VALUES ( '杨丽', '123003', 'd东莞', '女', 'ejufuwrn1kg');
复制代码

具体的执行截图如下：
事务T1：
事务T1

事务T2：
事务T2

从图中就可以看出事务1执行完INSERT语句后，再去事务2中执行同样的SQL时，事务2阻塞了。使用SQL查询当前事务加锁情况，具体如下：

SELECT trx.trx_id,trx.trx_state,trx.trx_query,trx.trx_isolation_level,
trx.trx_rows_locked,lock_mode,locks.lock_type,lock_index,lock_data
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN INFORMATION_SCHEMA.INNODB_LOCKS locks ON trx.trx_id = locks.lock_trx_id;
复制代码

事务加锁情况
从截图中我们可以看到，事务T1获取了索引uk_no的 123003 记录的X锁，事务T2获取了该记录的S锁。（这里我比较疑惑，为什么获得的是S锁，按理说应该是X锁啊)

  1. 同一个SQL指定同一个id在不同事务中执行

还是上面的SQL，在事务T1执行完后，获取插入记录的主键id=20，然后事务T2将SQL主键指定为20进行插入，具体情况如下：

INSERT INTO test (id, name, no, city, gender, remark) 
VALUES ('20', '杨丽', '123003', 'd东莞', '女', 'ejufuwrn1kg');
复制代码

指定id进行插入
加锁情况：

加锁情况
这时候锁已经是加在了主键id=20上，不再是锁二级索引 uk_no了，其他还是一样，事务T1获取主键的X锁，事务T2获取主键的S锁，读写不能并行，所以事务T2必须阻塞在那。

  1. 在二级索引uk_no后面的间隙插入数据

在二级索引 uk_no 的 123003 记录后面插入记录123004，具体SQL如下：

INSERT INTO test (name, no, city, gender, remark) 
VALUES ( '王凯丽', '123004', 'd东莞', '女', 'ejufuwrn1kg');
复制代码

事务T2
这个SQL在事务T1执行之后，再执行没有任何问题，不会阻塞。因为在读以提交的事务隔离级别下，无需解决幻读的问题，即事务T1没有在（123002，正无穷大）上加间隙锁。

  1. 使用 INSERT …… ON DUPLICATE KEY UPDATE语句

事务T1还是执行之前的那个SQL，事务T2执行下面的SQL

INSERT INTO test ( name, no, city, gender, remark) 
VALUES ( '杨丽', '123003', 'd东莞', '女', 'ejufuwrn1kg') 
ON DUPLICATE KEY UPDATE no='123004';
复制代码

事务T2执行情况

两个事务加锁情况如下：

两个事务加锁情况
从截图中我们可以看到，事务T1获取了索引uk_key2的443341830记录的X锁，事务T2也获取了该记录的X锁。这里的情况就正常了，都是获得记录的X锁了。

结论： 在使用INSERT …… ON DUPLICATE KEY UPDATE语法来插入记录时，如果遇到主键或者唯一二级索引列的值重复，则对数据表中已经存在的记录加X锁，而不是S锁。

  1. 主键重复（duplicate key）

这里我们只需要执行一条SQL即可，插入的SQL主键是已经存在的，具体如下：

INSERT INTO test ( id,name, no, city, gender, remark) 
VALUES ( 17,'杨丽', '123003', 's深圳', '女', 'ejufuwrn1kg');
复制代码

主键已经存在，重复插入

这里直接使用上面查看事务加锁的SQL查询加锁情况，是查询不到数据的，具体原因前文也有提到。这里我们再执行一个SQL获取这个主键id=17记录的X锁，看看具体情况如何：

直接获取主键id=17记录的X锁

事务加锁情况

从图中可以看出，前者获得了记录的S锁，后者要获取记录的X锁。因为前者获得到了S锁，所以后者阻塞在那里。

结论： 在主键重复的情况下，事务给重复的记录加上S锁。

其实还有一种情况，这里就直接给出结论：无论是在哪个隔离级别，插入新记录时遇到唯一二级索引列重复，都会给已经存在B+树中的那条二级索引加next key 锁。这个就是间隙锁出现在读已提交隔离级别的特殊场景。

全表扫描

对应的字段没有索引或者查询没有使用上索引，导致全表扫描的情况。比如下面的SQL：

SELECT * from test WHERE remark = 'epbujch' lock in share mode;
复制代码

因为这个remark字段没有索引，所以只能采用全表扫描的方式执行SQL，在定位到第一条聚簇索引，给他加S型记录锁。因为没有形成索引下推的条件，就返回到server层判断，记录符合条件的就返回给客户端，不符合的就释放掉锁。

使用SELECT ... FOR UPDATE的场景和上面的结果类似，只不过加的是X型记录锁。UPDATE和DELETE也是给对应的聚簇索引加X型记录锁。若UPDATE更新了二级索引的话，还会给二级索引记录加X型记录锁。

总结

读已提交事物隔离级别的加锁情况，就暂时先介绍这么多，可重复读隔离级别的加锁情况再开篇介绍。上面使用查询INFORMATION_SCHEMA.INNODB_TRX 和INFORMATION_SCHEMA.INNODB_LOCKS两个表的信息来分析SQL到底加了什么锁，其实是不够太直观的，后面会介绍如何使用show engine innodb status语句查看SQL的加锁情况。

[1] MySQL是怎样运行的

微海报分享

近期文章

近期评论

标签

热门

文章归档

分类目录

功能