无敌MySQL之InnoDB是如何加锁的

IT小东北
247 阅读8分钟

学习本文前提是要对MySQL当中的锁有充足的了解，知道各个锁的含有是什么。如果有不知道的小伙伴可以看我之前的文章，有对锁的介绍

8e5b5265c724e3c182023f6dfad03f94.jpg

1. 加锁受哪些因素影响

首先我们一条语句到底应该加什么锁受多种因素影响，例如：

  • 事务的隔离级别
  • 语句执行时的时候使用到的索引类型（比如聚簇索引，唯一二级索引、普通索引）
  • 是否是精确匹配
  • 是否是唯一性搜索
  • 具体执行的语句类型（SELECT、INSERT、DELETE、UPDATE）
  • 是否开启innodb_locks_unsafe_for_binlog系统变量 
  • 记录是否被标记删除

在详细分析语句的加锁过程前，首先强调一点：加锁 只是解决并发事务执行过程中引起的脏读、不可重复读、幻读这些问题的一种解决方案（MVCC也算是一种解决方案）。不同场景要解决的问题不一样，才导致加的锁不一样。 本文主要把语句分为三种大类：普通的Select、锁定读的语句，Insert语句

2. 普通的Select语句

普通的Select语句在

  • READ UNCOMMITED隔离级别下，不加锁，直接读取记录的最新版本，可能出现脏读、不可重复读、幻读的问题
  • READ COMMITED隔离级别下，在每次执行普通的select语句时都会生成一个ReadView。这样解决了脏读问题，但没有解决不可重复读问题和幻读问题
  • REPEATABLE READ隔离级别下，不加锁，只在第一次执行普通的select语句时生成一个ReadView，解决了脏读、不可重复读问题（有的文章说也解决了幻读问题，但是更加权威来说没有完全解决，这个稍后解释）
  • SERIALIZABLE隔离级别下，分为俩种情况讨论：
    • 在系统变量autocommit=0时，也就是禁用自动提交时，普通的select语句会被转换为selecet...LOCK IN SHARE MODE。也就是在读取记录前需要先获取记录的S锁。具体加锁情况和REPEATABLE READ隔离级别一下，这个稍后分析
    • 在系统变量autocommit=1时，也就是启用自动提交时，普通的select语句并不加锁。这是利用MVCC来生成一个READ VIEW来读取记录（为啥不加锁呢？？因为你自动提交了意味着一个事务只包含一条语句呀。不会出现一个sql执行俩次结果）

3. 锁定读的语句

本文主要分为4种来介绍：

  • 语句一：SELECT...LOCK IN SHARE MODE
  • 语句二：SELECT...FOR UPDATE
  • 语句三：UPDATE...
  • 语句四：DELETE....

开始之前让我们先创建一个表，并插入几条数据

CREATE TABLE `hero` (
  `number` int NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`number`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO hero
VALUES (1, 'l刘备', '蜀'),
       (3, 'z诸葛亮', '蜀'),
       (8, 'c曹操', '魏'),
       (15, 'x荀彧', '魏'),
       (20, 's孙权', '吴');

3.1 READ UNCOMMITED/READ COMMITTED 隔离级别下

在 READ UNCOMMITTED 下语句的加锁方式和 READ COMMITTED 隔离级别下语句的加锁方式基本一 致，采用加锁方式解决并发事务带来的问题时，其实脏读和不可重复读在任何一个隔离级别下都不会发生（因为读写要排队！！！）

3.1.1 对于使用主键进行等值查询的情况下

使用SELECT...LOCK IN SHARE MODE为记录加锁

SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE;

这个语句执行只需要访问一下聚簇索引中number=8的记录。所以它给这条记录加了一个S型行锁。

使用SELECT...FOR UPDATE

SELECT * FROM hero WHERE number = 8 for update ;

这个语句执行只需要访问一下聚簇索引中number=8的记录。所以它给这条记录加了一个X型行锁。

使用UPDATE...来为记录加锁

UPDATE hero SET country = '汉' WHERE number = 8;

这条语句不需要更新二级索引列，加锁方式和上面的SELECT...FOR UPDATE语句一致。加的是X型行锁。

如果UPDATE语句中更新了二级索引列。

update hero set name='IT小东北' where number='8';

该语句执行步骤简单来说为先更新对应的number值为8的聚簇索引记录，在更新对应的二级索引记录。加锁步骤为：

  1. 为number值为8的聚簇索引记录加上X型行锁
  2. 为该聚簇索引记录的idx_name二级索引记录加上X型行锁

使用DELETE...语句

DELETE FROM hero WHERE number = 8;

delete语句不仅仅要删除聚簇索引记录还要删除二级索引的记录因此和上面的update更新语句一样。

3.1.2 对于使用主键进行范围查询的情况下

SELECT...LOCK IN SHARE MODE

SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;

加锁流程：

  1. 先到聚簇索引中定位到满足number<=8的第一条记录为其加S型行锁。也就是 number 值为 1 的记录， 然后为其加锁。
  2. 判断一下该记录是否符合 索引条件下推 中的条件。--不符合
  3. 判断一下该记录是否符合范围查询的边界条件。本例子中利用主键查询。 InnoDB 规定每从聚簇索引中取出一条记录时都要判断一下该记录是否符合范围查询的边界条件，也就 是 number <= 8 这个条件。如果符合的话将其返回给 server层 继续处理，否则的话需要释 放掉在该记录上加的锁，并给 server层 返回一个查询完毕的信息。
  4. 将该记录返回到Server层进行判断server层如果收到存储引擎层提供的查询完毕的信息，就结束查询，否则继续判断那些没有进行索引条件下推的条件，在本例中就是继续判断 number <= 8 这个条件是否成立。InnoDB 的大叔采用的策略就是这么简单粗暴，把凡是没有经过 索引条件下推 的条件都需要放到 server 层再判断一遍。如果该记录符合剩余的条件（没有进行 索引条件下推 的条件），那么就把它发送给客户端，不然的话需要释放掉在该记录上加的锁。噫，不是在第3步中已经判断过了么
  5. 然后刚刚查询得到的这条记录（也就是 number 值为 1 的记录）组成的单向链表继续向后查找，得到了 number 值为 3 的记录，然后重复第 2 ， 3 ， 4 、 5 这几个步骤。

这里有个地方需要注意一下，当找到number值为8的那条记录时，还得再向后找一条记录，在存储引擎读取这条记录时，需要为这条记录加锁。然后在第三步判断不符合条件，然后在把锁匙放掉。

同时当我们执行 SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;这条语句后并且没有提交事务，这个时候也可以执行number<=8的更新和操作。这就是在READ UNCOMMITED和READ COMMITED隔离级别下会出现不可重复读和幻读问题。

SELECT ... FOR UPDATE

和 SELECT ... FOR UPDATE语句类似，只不过加的是 X型行锁

UPDATE ...

如果没有更新二级索引。和上面的SELECT...for update一致。都是只加了X型行锁

UPDATE hero SET name = 'cao曹操' WHERE number >= 8;

如果更新了二级索引，就是先为值为8的聚簇索引记录加上X型行锁在为值为8的二级索引记录在X行行锁

DELETE ...

使用DELETE 语句的加锁情况和上面UPDATE 语句一样。就不重复介绍了。

在READ UNCOMMITTED和READ COMMITTED隔离级别下，使用普通的二级索引和唯一二级索引进行 

加锁的过程是一样的，所以我们也就不分开讨论了。

3.2 REPEATABLE READ隔离级别下

REPEATABLE READ 隔离级别与 READ UNCOMMITTED 和 READ COMMITTED 这两个隔离级别相比不可重复读问题（也会解决一定的幻读问题，解决幻读要靠gap锁）

3.2.1 对于使用主键进行等值查询

使用SELECT...LOCK IN SHARE MOEDE

SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE;

我们知道主键具有唯一性，在一个事务中下次再执行这个查询语句的时候肯定不会有别的事务插入多条 number 值为8的记录，所以这种情况下和 READ UNCOMMITTED／READ COMMITTED 隔离级别下一样，我们只需要为这条 number 值为8的记录加一个 S型行锁。

如果主键查询的记录不存在怎么办：先看一下表里面现在的数据

image.png

SELECT * FROM hero WHERE number = 7 LOCK IN SHARE MODE;

由于 number 值为 7 的记录不存在，为了禁止 不可重复读 幻读 现象（这和隔离级别没关系啊因为你加锁了），在当前事务提交前我们需要预防别的 事务插入 number 值为 7 的新记录，所以需要在 number 值为 8 的记录上加一个 gap锁 ，也就是  不允许别的事务插入 number 值在 (3, 8) 这个区间的新记录。

其余语句的使用主键进行等值查询的情况与 READ UNCOMMITTED／READ COMMITTED 隔离级别类似，其实加的都是S型行锁或者X型行锁。这里就不赘述了。

3.2.2 对于主键进行范围查询

使用Select ... LOCK IN SHARED MODE

SELECT * FROM hero WHERE number >= 8 LOCK IN SHARE MODE;

主键本身唯一我们不需要担心number<8的记录。但是要保证禁止别的事务插入 number 值符合 number >= 8 的记录。所以：

  • 为number值等于于8的记录加一个S型行锁
  • 为number值大于8的记录都加一个S型next-key锁（包括 Supremum 伪记录）。**

特殊情况

SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;

因为没有使用 索引记录下推 ，所以在加锁时会把 number 值为 1 、 3 、 8 、 15 这四条记录都加 上S型next-key锁，不过之后 server层 判断 number 值为 15 的记录不满足 number <= 8 条件 后，与 READ UNCOMMITTED/READ COMMITTED 隔离级别下的处理方式不同， REPEATABLE READ 隔离级别下并不会把锁释放掉。这样在在 (-∞, 1) 、 (1, 3) 、 (3, 8) 、 (8, 15) 之间的话，是会进入等待状态的

SELECT FOR UPDATE

和Select ... LOCK IN SHARED MODE 类似，不过都变成了X型next-key锁。

UPDATE ...

如果update语句未更新二级索引列，

UPDATE hero SET country = '汉' WHERE number >= 8;

这条 UPDATE 语句并没有更新二级索引列，加锁方式和上边所说的 SELECT ... FOR UPDATE语句一致。

如果update语句更新了二级索引列

UPDATE hero SET name = 'cao曹操' WHERE number >= 8;

这种情况对聚簇索引加锁和SELECT...FOR UPDATE一致，但是对于二级索引记录来说会对number 值为 8 、 15 、 20 的二级索引记录加 X型正经记录锁。但不会加X型的next-key锁。

UPDATE hero SET country = '汉' WHERE number <= 8;

则会对 number 值为 1 、 3 、 8 、 15 的聚簇索引记录加 X型next-key ，但是只会对二级索引记录的number值为1、3、8的记录加X型行锁。

DELETE ...

和上面UPDATE 语句加锁方法一致，这里就不阐述了

3.2.3 对于使用唯一二级索引进行等值查询的情况

因为目前表没有唯一二级索引。这里就先把idx_name修改为唯一二级索引。

使用Select ... LOCK IN SHARED MODE

SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;

由于二级索引具有唯一性，所以这种情况下和READ UNCOMMITED/READ COMMITED隔离级别下一样，我们只需要为这条 name 值 为 'c曹操' 的二级索引记录加一个 S型行锁 ，然后再为它对应的聚簇索引记录加一个S型正经记录锁。

如果对唯一二级索引等值查询的值并不存在。和上面一样需要加一个gap锁。

SELECT ... FOR UPDATE语

SELECT * FROM hero WHERE name = 'c曹操' FOR UPDATE;

这种情况下与 SELECT ... LOCK IN SHARE MODE 语句的加锁情况一样。只不过加的是X型罢了。

UPDATE ...来为记录加锁

与SELECT...FOR UPDATE一致。但是如果被更新的列还有别的二级索引列的话。对应的二级索引列也会被加锁

DELETE ...

这种情况下与 SELECT ... LOCK IN SHARE MODE 语句的加锁情况一样。只不过加的是X型罢了。

对于使用唯一二级索引进行范围查询的情况

使用 SELECT ... LOCK IN SHARE MODE 来为记录加锁
SELECT * FROM hero FORCE INDEX(idx_name) WHERE name >= 'c曹操' LOCK IN SHARE MODE;

这种情况是二级索引中所有满足name>='曹操'的记录都会加S型next-key锁。对应的聚簇索引记录加S型行锁。不过需要注意一下加锁顺序，对一条二级索引记录加锁完后，会接着对它响应的聚簇索引记录加锁，完后才会对下一条二级索引记录进行加锁，以此类推～

SELECT * FROM hero WHERE name <= 'c曹操' LOCK IN SHARE MODE;

这个语句会先为曹操这条记录加S型next-key锁以及它对应的聚簇索引记录加S型行锁。前边在说为 number <= 8 这个条件进行加锁时，会把 number 值为 15 的记录也加一个锁，之 后 server层 判断不符合条件后再释放掉，现在换成二级索引就不用为下一条记录加锁了么？是的，这主要是因为我们开启了 索引条件下推 ，对于二级索引记录来说，可以先在存储引擎层 判断给定条件 name <= 'c曹操' 是否成立，如果不成立就不返回给 server层 了，从而避免了不必要的加锁。

SELECT...FOR UPDATE

和SELECT ... LOCK IN SHARE MODE语句类似，只不过加的都是X型的。

UPDATE ...

UPDATE hero SET country = '汉' WHERE name >= 'c曹操';

没有更新二级索引列，加锁方式和上边所说的 SELECT ... FOR UPDATE语句一致。如果有其他二级索引列也被更新，那么也会为这些二级索引记录进行加锁。

总结

从上面分析可以看出，与READ UNCOMMITED和READ COMMITED隔离级别相比，在REPEATABLE READ隔离界别下主要是加了next-key锁和gap锁来防止出现不可重复读和幻读问题

avatar
文章
阅读
粉丝