MySQL数据库InnoDB引擎行级锁锁定范围详解

狗蛋的窝 2019-06-26

前言

每个数据库几乎都会实现自己的锁机制，锁机制是数据库区别于文件系统的主要标志之一，用于管理对共享资源的并发访问。

Mysql数据库InnoDB引擎支持行级锁，也就是说我们可以对表中某些行数据执行锁定操作，锁定操作的影响是：如果一个事物对表中某行执行了锁定操作，而另一个事务也需要对同样的行执行锁定操作，这样第二个事务的锁定操作有可能被阻塞，一旦被阻塞第二个事务只能等到第一个事务执行完毕（提交或回滚）或超时。

本文主要介绍InnoDB中的行锁相关概念，重点介绍行锁的锁定范围：

  • 什么样的SQL语句会加锁？
  • 加什么样的锁？
  • 加锁语句会锁定哪些行？

背景知识

上面我们简单的介绍了InnoDB的行级锁，为了理解后面的验证部分，需要补充一下背景知识。如果对相应知识非常了解，可以直接跳转到验证部分内容。

1. InnoDB锁的类型

InnoDB引擎使用了七种类型的锁，他们分别是：

  • 共享排他锁（Shared and Exclusive Locks）
  • 意向锁（Intention Locks）
  • 记录锁（Record Locks）
  • 间隙锁（Gap Locks）
  • Next-Key Locks
  • 插入意图锁（Insert Intention Locks）
  • 自增锁（AUTO-INC Locks）

本文主要涉及Shared and Exclusive Locks，Record Locks，Gap Locks，Next-Key Locks这几种锁，其他类型锁如果大家感兴趣可以自己深入了解，在此不在详述。

1.1 Shared and Exclusive Locks

共享锁（S锁）和排他锁（X锁）的概念在许多编程语言中都出现过。先来描述一下这两种锁在MySQL中的影响结果：

  • 如果一个事务对某一行数据加了S锁，另一个事务还可以对相应的行加S锁，但是不能对相应的行加X锁。
  • 如果一个事务对某一行数据加了X锁，另一个事务既不能对相应的行加S锁也不能加X锁。

用一张经典的矩阵表格继续说明共享锁和排他锁的互斥关系：

--SX
S01
X11

图中S表示共享锁X表示独占锁，0表示锁兼容1表示锁冲突，兼容不被阻塞，冲突被阻塞。由表可知一旦一个事务加了排他锁，其他个事务加任何锁都需要等待。多个共享锁不会相互阻塞。

1.2 Record Locks、Gap Locks、Next-Key Locks

这三种类型的锁都描述了锁定的范围，故放在一起说明。

以下定义摘自MySQL官方文档

  • 记录锁（Record Locks）:记录锁锁定索引中一条记录。
  • 间隙锁（Gap Locks）:间隙锁要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值。
  • Next-Key Locks:Next-Key锁是索引记录上的记录锁和在索引记录之前的间隙锁的组合。

定义中都提到了索引记录（index record）。为什么？行锁和索引有什么关系呢？其实，InnoDB是通过搜索或者扫描表中索引来完成加锁操作，InnoDB会为他遇到的每一个索引数据加上共享锁或排他锁。所以我们可以称行级锁（row-level locks）为索引记录锁（index-record locks），因为行级锁是添加到行对应的索引上的。

三种类型锁的锁定范围不同，且逐渐扩大。我们来举一个例子来简要说明各种锁的锁定范围，假设表t中索引列有3、5、8、9四个数字值，根据官方文档的确定三种锁的锁定范围如下：

  • 记录锁的锁定范围是单独的索引记录，就是3、5、8、9这四行数据。
  • 间隙锁的锁定为行中间隙，用集合表示为(-∞,3)、(3,5)、(5,8)、(8,9)、(9,+∞)。
  • Next-Key锁是有索引记录锁加上索引记录锁之前的间隙锁组合而成，用集合的方式表示为(-∞,3]、(3,5]、(5,8]、(8,9]、(9,+∞)。

最后对于间隙锁还需要补充三点：

  1. 间隙锁阻止其他事务对间隙数据的并发插入，这样可有有效的解决幻读问题(Phantom Problem)。正因为如此，并不是所有事务隔离级别都使用间隙锁，MySQL InnoDB引擎只有在Repeatable Read（默认）隔离级别才使用间隙锁。
  2. 间隙锁的作用只是用来阻止其他事务在间隙中插入数据，他不会阻止其他事务拥有同样的的间隙锁。这就意味着，除了insert语句，允许其他SQL语句可以对同样的行加间隙锁而不会被阻塞。
  3. 对于唯一索引的加锁行为，间隙锁就会失效，此时只有记录锁起作用。

2. 加锁语句

前面我们已经介绍了InnoDB的是在SQL语句的执行过程中通过扫描索引记录的方式来实现加锁行为的。那哪些些语句会加锁？加什么样的锁？接下来我们逐一描述：

  • select ... from语句：InnoDB引擎采用多版本并发控制（MVCC）的方式实现了非阻塞读，所以对于普通的select读语句，InnoDB并不会加锁【注1】。
  • select ... from lock in share mode语句：这条语句和普通select语句的区别就是后面加了lock in share mode，通过字面意思我们可以猜到这是一条加锁的读语句，并且锁类型为共享锁（读锁）。InnoDB会对搜索的所有索引记录加next-key锁，但是如果扫描的唯一索引的唯一行，next-key降级为索引记录锁。
  • select ... from for update语句：和上面的语句一样，这条语句加的是排他锁（写锁）。InnoDB会对搜索的所有索引记录加next-key锁，但是如果扫描唯一索引的唯一行，next-key降级为索引记录锁。
  • update ... where ...语句：。InnoDB会对搜索的所有索引记录加next-key锁，但是如果扫描唯一索引的唯一行，next-key降级为索引记录锁。【注2】
  • delete ... where ...语句：。InnoDB会对搜索的所有索引记录加next-key锁，但是如果扫描唯一索引的唯一行，next-key降级为索引记录锁。
  • insert语句：InnoDB只会在将要插入的那一行上设置一个排他的索引记录锁。

最后补充两点：

  1. 如果一个查询使用了辅助索引并且在索引记录加上了排他锁，InnoDB会在相对应的聚合索引记录上加锁。
  2. 如果你的SQL语句无法使用索引，这样MySQL必须扫描整个表以处理该语句，导致的结果就是表的每一行都会被锁定，并且阻止其他用户对该表的所有插入。

SQL语句验证

闲言少叙，接下来我们进入本文重点SQL语句验证部分。

1.测试环境

数据库：MySQL 5.6.35
事务隔离级别：Repeatable read
数据库访问终端：mysql client

2.验证场景

2.1 场景一

建表：

CREATE TABLE `user` (
 `id` int(11) NOT NULL,
 `name` varchar(8) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

首先我们执行SQL语句的模板：

步骤client 1client 2
1begin;--
2SELECT * FROM user where name='e' for update;--
3--begin;
4--INSERT INTO `user` (`id`, `name`) VALUES (10, #{name});
5rollback;--
6--rollback;

替换步骤5中name的值，观察结果：

name的值执行结果
a不阻塞
b不阻塞
d阻塞
e阻塞
f阻塞
h不阻塞
i不阻塞

观察结果，我们发现SQL语句
SELECT * FROM user where name='e' for update
一共锁住索引name中三行记录，(c,e]区间应该是next-key锁而(e,h)区间是索引记录e后面的间隙。

接下来我们确定next-key锁中哪部分是索引记录锁哪部分是间隙锁。

执行SQL语句的模板：

步骤client 1client 2
1begin;--
2SELECT * FROM user where name='e' for update;--
3--SELECT * FROM user where name=#{name} for update;
5rollback;--
6--rollback;

替换步骤5中name的值，观察结果：

name的值执行结果
d不阻塞
e阻塞
f不阻塞

因为间隙锁只会阻止insert语句，所以同样的索引数据，insert语句阻塞而select for update语句不阻塞的就是间隙锁，如果两条语句都阻塞就是索引记录锁。

观察执行结果可知，d和f为间隙锁，e为索引记录锁。

结论：通过两条SQL，我们确定了对于辅助索引name在查询条件为 where name='e' 时的加锁范围为（c,e],(e,g),其中：

  • 对SQL语句扫描的索引记录e加索引记录锁[e]。
  • 锁定了e前面的间隙，c到e之间的数据(c,e)加了间隙锁
  • 前两个构成了next-key锁(c,e]。
  • 值得注意的是还锁定了e后面的间隙(e,g)。

说的这里细心的读者可能已经发现我们的测试数据中没有间隙的边界数据c和g。接下来我们就对间隙边界值进行测试。

执行SQL语句的模板：

步骤client 1client 2
1begin;--
2SELECT * FROM user where name='e' for update;--
3--begin;
4--INSERT INTO `user` (`id`, `name`) VALUES (#{id}, #{name});
5rollback;--
6--rollback;

替换步骤5中id，name的值，观察结果：

id的值name=c执行结果id的值name=g执行结果
-------3g组塞
-------2g阻塞
-1c不阻塞-1g阻塞
1c不阻塞1g不阻塞
2c不阻塞2g阻塞
3c不阻塞3g不阻塞
4c阻塞4g阻塞
5c阻塞5g阻塞
6c阻塞6g阻塞
7c不阻塞7g不阻塞
8c阻塞8g不阻塞
9c不阻塞9g不阻塞
10c阻塞10g不阻塞
11c阻塞---
12c阻塞---

通过观察以上执行结果，我们发现，name等于c和e时insert语句的结果随着id值得不同一会儿锁定，一会儿不锁定。那一定是id列加了锁才会造成这样的结果。

如果先不看id=5这一行数据的结果，我们发现一个规律：

  • 当name=c时，name=c对应的id=3的id聚合索引数据记录之后的间隙(3,5)，(5,7)，(7,9)，(9,∞)都被加上了锁。
  • 当name=e时，name=e对应的id=7的id聚合索引数据记录之前的间隙(5,7)，(3,5)，(1,3)，(-∞,1)都被加上了锁。
  • 我们可用select * from user where id = x for update;语句判断出以上间隙上加的锁都为间隙锁。

接下来我们解释一下id=5的锁定情况

执行SQL语句的模板：

步骤client 1client 2
1begin;--
2SELECT * FROM user where name='e' for update;--
3--SELECT * FROM user where id=#{id} for update;
5rollback;--
6--rollback;

替换步骤5中id的值，观察结果：

id的值执行结果
3不阻塞
4不阻塞
5阻塞
6不阻塞
7 不阻塞

通过观察执行结果可知，id=5的聚合索引记录上添加了索引记录锁。根据MySQL官方文档描述，InnoDB引擎在对辅助索引加锁的时候，也会对辅助索引所在行所对应的聚合索引（主键）加锁。而主键是唯一索引，在对唯一索引加锁时，间隙锁失效，只使用索引记录锁。所以SELECT * FROM user where name='e' for update;不仅对辅助索引name=e列加上了next-key锁，还对对应的聚合索引id=5列加上了索引记录锁。

最终结论：
对于SELECT * FROM user where name='e' for update;一共有三种锁定行为:

  1. 对SQL语句扫描过的辅助索引记录行加上next-key锁（注意也锁住记录行之后的间隙）。
  2. 对辅助索引对应的聚合索引加上索引记录锁。
  3. 当辅助索引为间隙锁“最小”和“最大”值时，对聚合索引相应的行加间隙锁。“最小”锁定对应聚合索引之后的行间隙。“最大”值锁定对应聚合索引之前的行间隙。

上面我们将对辅助索引加锁的情况介绍完了，接下来我们测试一下对聚合索引和唯一索引加锁。

2.2 场景二

建表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(8) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意与场景一表user不同的是name列为唯一索引。

插入数据：

INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

首先我们执行SQL语句的模板：

步骤client 1client 2
1begin;--
2SELECT * FROM user where name='e' for update;
3--begin;
4--INSERT INTO `user` (`id`, `name`) VALUES (10, #{name});
5rollback;--
6--rollback;

替换步骤5中name的值，观察结果：

name的值执行结果
a不阻塞
b不阻塞
c不阻塞
d不阻塞
e阻塞
f不阻塞
g不阻塞
h不阻塞
i不阻塞

由测试结果可知，只有name='e'这行数据被锁定。

通过SQL语句我们验证了，对于唯一索引列加锁，间隙锁失效，

2.3 场景三

场景一和场景二都是在查询条件等于的情况下做出的范围判断，现在我们尝试一下其他查询条件，看看结论是否一致。

借用场景一的表和数据。

建表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(8) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

执行SQL语句的模板：

步骤client 1client 2
1begin;--
2SELECT * FROM user where name>'e' for update;--
3--begin;
4--INSERT INTO `user` (`id`, `name`) VALUES ('10', #{name});
5rollback;--
6--rollback;

替换步骤5中name的值，观察结果：

name的值执行结果
a阻塞
b阻塞
c阻塞
d阻塞
e阻塞
f阻塞
g阻塞
h阻塞
i阻塞

这个结果是不是和你想象的不太一样，这个结果表明where name>'e'这个查询条件并不是锁住'e'列之后的数据，而锁住了所有name列中所有数据和间隙。这是为什么呢？

我们执行以下的SQL语句执行计划：

explain select * from user where name>'e' for update;

执行结果：

+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | index_name    | index_name | 26      | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

如果你的结果与上面不同先执行一下OPTIMIZE TABLE user;再执行以上语句。

通过观察SQL语句的执行计划我们发现，语句使用了name列索引，且rows参数等于5，user表中一共也只有5行数据。SQL语句的执行过程中一共扫描了name索引记录5行数据且对这5行数据都加上了next-key锁，符合我们上面的执行结果。

接下来我们再制造一组数据。
建表：

CREATE TABLE `user` (
 `id` int(11) NOT NULL,
 `name` varchar(8) NOT NULL,
 `age` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `user` (`id`, `name`,`age`) VALUES ('1', 'a','15');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('3', 'c','20');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('5', 'e','16');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('7', 'g','19');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('9', 'i','34');

这张表和前表的区别是多了一列非索引列age。

我们再执行一下同样的SQL语句执行计划：

explain select * from user where name>'e' for update;

执行结果：

+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | index_name    | index_name | 26      | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

是不是和第一次执行结果不同了，rows参数等于2，说明扫描了两行记录，结合SQL语句select * from user where name>'e' for update;执行后返回结果我们判断这两行记录应该为g和i。

因为select * from user where name>'e' for update;语句扫描了两行索引记录分别是g和i，所以我们将g和i的锁定范围叠就可以得到where name>'e'的锁定范围：

  1. 索引记录g在name列锁定范围为(e,g],(g,i)。索引记录i的在name列锁定范围为(g,i],(i,+∞)。两者叠加后锁定范围为(e,g],(g,i],(i,+∞)。其中g,i为索引记录锁。
  2. g和i对应id列中的7和9加索引记录锁。
  3. 当name列的值为锁定范围上边界e时，还会在e所对应的id列值为5之后的所有值之间加上间隙锁，范围为(5,7),(7,9),(9,+∞)。下边界为+∞无需考虑。

接下来我们逐一测试：

首先测试验证了next-key锁范围，执行SQL语句的模板：

步骤client 1client 2
1begin;--
2SELECT * FROM user where name>'e' for update;--
3--begin;
4--INSERT INTO `user` (`id`, `name`, `age`) VALUES ('10', #{name},'18');
5rollback;--
6--rollback;

替换步骤5中name的值，观察结果：

name的值执行结果
a不阻塞
b不阻塞
c不阻塞
d不阻塞
f阻塞
g阻塞
h阻塞
i阻塞
j阻塞
k阻塞

下面验证next-key锁中哪部分是间隙锁，哪部分是索引记录锁，执行SQL语句的模板：

步骤client 1client 2
1begin;--
2SELECT * FROM user where name>'e' for update;--
3--SELECT * FROM user where name=#{name} for update;
5rollback;--
6--rollback;

替换步骤5中name的值，观察结果：

name的值执行结果
e不阻塞
f不阻塞
g阻塞
h不阻塞
i阻塞
j不阻塞

接下来验证对id列加索引记录锁，执行SQL语句的模板：

步骤client 1client 2
1begin;--
2SELECT * FROM user where name>'e' for update;--
3--SELECT * FROM user where id=#{id} for update;
5rollback;--
6--rollback;

替换步骤5中id的值，观察结果：

id的值执行结果
5不阻塞
6 不阻塞
7阻塞
8不阻塞
9阻塞
10不阻塞

最后我们验证name列的值为边界数据e时，id列间隙锁的范围，执行SQL语句的模板：

步骤client 1client 2
1begin;--
2SELECT * FROM user where name>'e' for update;--
3--begin;
4--INSERT INTO `user` (`id`, `name`,`age`) VALUES (#{id}, 'e','18');
5rollback;--
6--rollback;

替换步骤5中id的值，观察结果：

id的值执行结果
-1不阻塞
1不阻塞
2不阻塞
3不阻塞
4不阻塞
5不阻塞
6阻塞
7阻塞
8阻塞
9阻塞
10阻塞
11阻塞
12阻塞

注意7和9是索引记录锁记录锁。

观察上面的所有SQL语句执行结果，可以验证select * from user where name>'e' for update的锁定范围为此语句扫描name列索引记录g和i的锁定范围的叠加组合。

2.4 场景四

我们通过场景三验证了普通索引的范围查询语句加锁范围，现在我们来验证一下唯一索引的范围查询情况下的加锁范围。有了场景三的铺垫我们直接跳过扫描全部索引的情况，创建可以扫描范围记录的表结构并插入相应数据测试。

建表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(8) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `user` (`id`, `name`,`age`) VALUES ('1', 'a','15');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('3', 'c','20');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('5', 'e','16');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('7', 'g','19');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('9', 'i','34');

和场景三表唯一不同是name列为唯一索引。

SQL语句select * from user where name>'e'扫描name列两条索引记录g和i。如果需要只对g和i这两条记录加上记录锁无法避免幻读的发生，索引锁定范围应该还是两条数据next-key锁锁的组合：(e,g],(g,i],(i,+∞)。其中g,i为索引记录锁。

我们通过SQL验证我们的结论，执行SQL语句的模板：

步骤client 1client 2
1begin;--
2SELECT * FROM user where name>'e' for update;--
3--begin;
4--INSERT INTO `user` (`id`, `name`, `age`) VALUES ('10', #{name},'18');
5rollback;--
6--rollback;

替换步骤5中name的值，观察结果：

name的值执行结果
a不阻塞
b不阻塞
c不阻塞
d不阻塞
f阻塞
g阻塞
h阻塞
i阻塞
j阻塞
k阻塞

下面验证next-key锁中哪部分是间隙锁，哪部分是索引记录锁，执行SQL语句的模板：

步骤client 1client 2
1begin;--
2SELECT * FROM user where name>'e' for update;--
3--SELECT * FROM user where name=#{name} for update;
5rollback;--
6--rollback;

替换步骤5中name的值，观察结果：

name的值执行结果
e不阻塞
f不阻塞
g阻塞
h不阻塞
i阻塞
j不阻塞

通过上面两条SQL语句的验证结果，我们证明了我们的g和i的锁定范围趋势为两者next-key叠加组合。

接下来我们验证一下对辅助索引加锁后对聚合索引的锁转移，执行SQL语句的模板：

步骤client 1client 2
1begin;--
2SELECT * FROM user where name>'e' for update;--
3--SELECT * FROM user where id=#{id} for update;
5rollback;--
6--rollback;

替换步骤5中id的值，观察结果：

id的值执行结果
5不阻塞
6 不阻塞
7阻塞
8不阻塞
9阻塞
10不阻塞

由结果可知对辅助索引name中的g和i列对应的聚合索引id列中的7和9加上了索引记录锁。

到目前为止所有实验结果和场景三完全一样，这也很好理解，毕竟场景四和场景三只是辅助索引name的索引类型不同，一个是唯一索引，一个是普通索引。

最后验证意向，next-key锁边界数据e，看看结论时候和场景三相同。

执行SQL语句的模板：

步骤client 1client 2
1begin;--
2SELECT * FROM user where name>'e' for update;--
3--begin;
4--INSERT INTO `user` (`id`, `name`,`age`) VALUES (#{id}, 'e','18');
5rollback;--
6--rollback;

替换步骤5中id的值，观察结果：

id的值执行结果
-1不阻塞
1不阻塞
2不阻塞
3不阻塞
4不阻塞
5不阻塞
6不阻塞
7阻塞
8不阻塞
9阻塞
10不阻塞
11不阻塞
12不阻塞

注意7和9是索引记录锁记录锁。

通过结果可知，当name列为索引记录上边界e时，并没有对id有加锁行为，这点与场景三不同。

对于唯一索引的范围查询和普通索引的范围查询类似，唯一不同的是当辅助索引等于上下范围的边界值是不会对主键加上间隙锁。

唯一索引范围查询加锁范围：

  • 对于扫描的辅助索引记录的锁定范围就是多个索引记录next-key范围的叠加组合。
  • 对于聚合索引（主键）的锁定范围，会对多个辅助索引对应的聚合索引列加索引记录锁。

结论

InnoDB引擎会对他扫描过的索引记录加上相应的锁，通过“场景一”我们已经明确了扫描一条普通索引记录的锁定范围，通过“场景三”我们可以推断任意多个扫描普通索引索引记录的锁定范围。通过“场景二”我们确定了扫描一条唯一索引记录（或主键）的锁定范围。通过“场景四”我们可以推断任意多个扫描索唯一引记录（或主键）的锁定范围。在实际的应用可以灵活使用，判断两条SQL语句是否相互锁定。这里还需要注意的是对于索引的查询条件，不能想当然的理解，他往往不是我们理解的样子，需要结合执行计划判断索引最终扫描的记录数，否则会对加锁范围理解产生偏差。

备注

注1：在事务隔离级别为SERIALIZABLE时，普通的select语句也会对语句执行过程中扫描过的索引加上next-key锁。如果语句扫描的是唯一索引，那就将next-key锁降级为索引记录锁了。
注2：当更新语句修改聚合索引（主键）记录时，会对受影响的辅助索引执行隐性的加锁操作。当插入新的辅助索引记录之前执行重复检查扫描时和当插入新的辅助索引记录时，更新操作还对受影响的辅助索引记录添加共享锁。

参考：

https://dev.mysql.com/doc/ref...
https://dev.mysql.com/doc/ref...

狗蛋的窝

狗蛋的窝

相关推荐

MySQL中的undo日志

我们知道，MySQL中的redo日志记录了事务的行为，在服务器宕机的时候，可以通过重做事务来达到恢复数据的目的，然而，有的时候，事务还有回滚的需求，也就是说，我们需要知道某条在变成当前情况之前的样子，这种情况下，undo日志就派上用场了。也就是说，undo

spurity / 0评论 2020-11-10

12张图带你彻底理解分布式事务产生的场景和解决方案！！

作者个人研发的在高并发场景下，提供的简单、稳定、可扩展的延迟消息队列框架，具有精准的定时任务和延迟队列处理功能。自开源半年多以来，已成功为十几家中小型企业提供了精准定时调度方案，经受住了生产环境的考验。为使更多童鞋受益，现给出开源框架地址：。可以只在RM中

LeeLuffy / 0评论 2020-10-16

分布式事务、分布式锁、分布式缓存

分布式应用：一个服务需要对多个不同的数据库进行操作，保证同时成功或失败。

summerZBH / 0评论 2020-09-23

聊聊MySQL事务的特性和隔离级别

网上对于此类的文章已经十分饱和了，那还写的原因很简单――作为自己的理解笔记。此篇文章作为自己学习MySQL的一些个人理解，使用的引擎是InnoDb。首先先讲讲事务的概念，在《高性能MySQL》第三版中其对事务的描述是这样的：。在MySQL中隔离级别有四种，

DriveCar / 0评论 2020-09-07

几种分布式事务技术的比较

对业务侵入很强。已经提交的事务，不保证隔离性。不会脏读，性能介于seata和2阶段之间。seata和lcn大致的实现思路是一致的，但是回滚的机制不一样。

zjuwangleicn / 0评论 2020-09-04

简述MySql四种事务隔离级别

隔离性其实比想象的要复杂。在SQL标准中定义了四种隔离级别, 每一个事务中所做的修改,哪些在事务内和事务间是可见的,哪些是不可见的。较低级别的隔离通常可以执行更高的并发,系统的开销也更低。下面简单地介绍一下四种隔离级别。在 READ UNCOMMITTED

quniMdejiangyou / 0评论 2020-08-15

MySQL是如何保证数据的完整性

数据的一致性和完整性对于在线业务的重要性不言而喻，如何保证数据不丢呢？今天我们就探讨下关于数据的完整性和强一致性，MySQL做了哪些改进。在Oracle和MySQL这种关系型数据库中，讲究日志先行策略,只要日志持久化到磁盘，就能保证MySQL异常重启后，数

gamestart0 / 0评论 2020-08-15

聊聊分布式事务

现在摘抄一段wiki的解释，解释下什么是事务。数据库系统具有事务特性，这是其有别与文件系统重要特性。通常一个事务会有多个读写操作构成。事务具有四个基本特性，俗称ACID。数据库的状态从一种状态转变为另外一种状态，事务开始之前和是事务结束之后，数据库完整性约

loviezhang / 0评论 2020-08-08

JDBC事务机制

将此连接的自动提交模式设置为给定状态。autoCommit - true启用自动提交模式; false禁用它。撤消在当前事务中所做的所有更改，并释放此 Connection对象当前持有的任何数据库锁。/*程序到这儿证明没有异常，

gaozhennan / 0评论 2020-08-03

JDBC(3)-数据库事务

当一个连接对象被创建时，默认情况下是自动提交事务：每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。关闭数据库连接，数据就会自动的提交。如果多个操作，每个操作使用的是自己单独的连接，则无法保证事务。即同一个事务的多个操作必须在同

mcvsyy / 0评论 2020-08-02

数据库事务与特性

对于简单的增删改查场景，特别是学习阶段，不需要考虑诸如并发、锁等问题，但是我们要对事务的概念及特性有所了解，以便日后能够快速定位问题和给出优化方案。其中对于持久性的保障是通过数据库日志机制实现的，能够在数据库服务崩溃时进行数据的还原或恢复。

happinessaflower / 0评论 2020-07-29

数据库并发与并发异常

在使用数据库来支撑业务系统时，随着用户量的增大，经常会遇到同时读取相同数据的情况，在没有进行并发控制的情况下就会遇到各种各样的问题，对于可能出现的问题我们要有所了解。数据库中某条数据的money值为20，在处理过程中，事务B修改值为100，随后被事务A读取

花落花开春去秋来 / 0评论 2020-07-29

Spring的事务抽象

事务，一般是指要做的或所做的事情。在计算机术语中是指访问或者更新数据库中各项数据项的一个程序执行单元。事务通常由高级数据库操作语言或编程语言书写的用户程序的执行所引起，并用begin transaction和 end transaction语句来界定。事务

heniancheng / 0评论 2020-07-26

事务_简单转账业务模拟mybatis、Spring框架

`account_name` varchar CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACT

牧场SZShepherd / 0评论 2020-07-20

事务的特性ACID

数据库事务可以被定义为一个或者几个数据库允许的操作的集合。这个集合需要支持ACID特性。所以SQL的标准制定者对此作出妥协，提出隔离性的四个等级，其中最高级隔离等级才是串行执行。是指应用层系统从一种正确的状态，在事务成功后，达成另一种正确的状态。最低级别，

jakefei / 0评论 2020-07-19

声明式事务

-- id 属性用于在 aop:advisor 中引用事务通知 -->. 和REQUIRED的区别是就算现在已经有了已开启的事务， 也一定要开启自己的事务，避免和其他方法共用同一个事务。

牧场SZShepherd / 0评论 2020-07-19

日常小整理

　　1.READ UNCIMMITTED　　事务还没提交，而别的事务可以看到他其中修改的数据的后果，也就是脏读。　　2.READ COMMITTED（提交读）　　首先大多数数据库系统的默认隔离级别是READ COMMITTED，这种隔离级别就是一个事务的开

qingjiuquan / 0评论 2020-07-18

SQL Server数据库高级进阶之事务实战演练

SQL Server数据库事务举例在一个事务中，你写了2条sql语句，一条是修改订单表状态,一条是修改库存表库存-1 。

云之飞踢 / 0评论 2020-07-08

MySQL中事务隔离级别

最近在学习数据库的事务隔离级别。在这里整理一下。由于本人水平和写作能力有限，如文中有错误或者表达不清楚的地方，请多包涵。有任何意见或建议，欢迎留言。我们都知道关系型数据库事务有 ACID 的原则，他们分别代表原子性，一致性、隔离性和持久性。在查询数据时，同

zhanbuquan / 0评论 2020-07-04

暴风雨的礼物·MySQL06：事务和索引

整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。这种属性有时称为串行化，为了防

敏敏张 / 0评论 2020-06-27

事务的隔离级别

　　1、原子性：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。　　2、一致性：事务开始前和结束后，数据库的完整性约束没有被破坏 。　　 4、持久性：事务完成后

liaomingwu / 0评论 2020-06-25

MySQL 事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。在 MySQL 命令行的默认设置下

lpfvip00 / 0评论 2020-06-25

分布式事务的实现

在微服务架构中，随着服务的逐步拆分，数据库私有已经成为共识，这也导致所面临的分布式事务问题成为微服务落地过程中一个非常难以逾越的障碍，但是目前尚没有一个完整通用的解决方案。其实不仅仅是在微服务架构中，随着用户访问量的逐渐上涨，数据库甚至是服务的分片、分区、

粗茶淡饭 / 0评论 2020-06-25

JDBC事务管理

为了防止某些具有连带性的业务发生异常，导致数据库数据产生非正常的变化，需要对这些业务进行事务管理。在进行如银行转账的关联操作的业务上，解决了不必要的麻烦。这里以 mysql 例,在java中， mysql 默认开启事务自动提交，即没执行完一条完整的 mys

msmysql / 0评论 2020-06-21

一个非侵入的Go事务管理库——如何使用

在文章"清晰架构的Go微服务: 事物管理"中，我谈到了如何在清晰架构中实现非侵入的事务管理。它允许你把事务代码与业务逻辑代码分开，并且让你在编写业务逻辑时不必考虑事务。首先，它是整个清晰框架的一部分，所以你不能抛开框架单独使用它。其次，

zbcaicai / 0评论 2020-06-21

CRUD工程师——数据库事物ACID特性及隔离级别

所谓事务是用户自定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。例如，在关系数据库中，一个事务可以是一条sql语句，一组sql语句或整个程序。事务和程序是两个概念，一般来讲，一个程序中包含多个事务。事务的开始与结束可以由用

zhanbuquan / 0评论 2020-06-21

SQL Server 开启DTC分布式事务

MSDTC，协调跨多个数据库、消du息队列、文件系统等zhi资源管理器的事务。该服务的dao进程名为Msdtc.exe。

花落花开春去秋来 / 0评论 2020-06-20

事务----四大特性

据说是面试中容易被考的一个概念。有时需要结合例子去向面试官说清楚自己对事务的四大特性的理解。一个事务中可能包含很多的 “操作” 。这些 “操作” 产生的结果 有2种 ：要么 全部执行成功，要么 全部执行失败。一个事务 执行的结果 必须是使 数据库 从一个

daillo / 0评论 2020-06-17

JDBC & JDBC控制事务

* JDBC本质：其实是官方定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库驱动jar包。我们可以使用这套接口编程，真正执行的代码是驱动jar包中的实现类。String sql = "update accou

bluetears / 0评论 2020-06-17

Redis 事务

批量操作在发送 EXEC 命令前被放入队列缓存。收到 EXEC 命令后进入事务执行，事务中任意命令执行失败，其余的命令依然被执行。在事务执行过程，其他客户端提交的命令请求不会插入到事务执行命令序列中。$redis -> connect;// 连接re

博了个客 / 0评论 2020-06-16

MYSQL中binlog优化的一些思考汇总

WAL是实现事务持久性的一个常用技术，基本原理是将事务的修改记录redo log。redo log顺序追加写入。事务提交时，只需要保证事务的redo log落盘即可，通过redo log的顺序写代替页面的随机写提升数据库系统的性能。但是，该方案必须要求每个

smartleizi / 0评论 2020-06-14

MySQL事务中的redo与undo

举个例子，用户将test表中a的值从原来的5改为新的值10，那么undo日志会保存修改前a的值5，即将旧数据恢复。redo日志就是为了解决这个问题。

csdnlytPractice / 0评论 2020-06-11

数据库事务

事务是由一个或多个SQL语句组成的单独单元，在这个单元中，每个MySQL语句是相互依赖的。如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。有受到影响的数据将返回到事物开始以前的状态；事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个

sunysh00 / 0评论 2020-06-11

MySQL 事务四大隔离级别

串行化利用加锁的方式，在这个事务没有被提交之前其他的线程，只能等到当前操作完成之后，才能进行操作，这样会非常耗时，而且，影响数据库的性能，通常情况下，不会使用这种隔离级别。

vincen / 0评论 2020-06-10

InnoDB存储引擎的事务

　　当多个用户访问同一份数据，一个用户更改该事物时可能会有其他用户同时发起更改请求，为保证数据库记录的更新从一个一致性状态转为另外一个一致性状态，使用事务是非常有必要的。原子性是指事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。UND

heniancheng / 0评论 2020-06-10

Spring基础-11-事务细节

假设现在有两个事务：Transaction01和Transaction02并发执行。[1]Transaction01将某条记录的AGE值从20修改为30。[3]Transaction01回滚，AGE值恢复到了20。[1]Transaction01读取了ST

langyue / 0评论 2020-06-09

分布式事务方案

事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。如果参与者无法及时接收到来自协调者的doCommit或者rebort请求时，会在等待超时之后，会继续进行事务的提交。

wenjieyatou / 0评论 2020-06-09

mysql

最左前缀联合索引的多个字段中，只有当查询条件为联合索引的一个字段时，查询才能使用该索引。覆盖索引覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键上去取数据。一致性事务前后数据的完整性必须保持一致。这里的版本号并不是实际的时间值，而是系统版本号。

DriveCar / 0评论 2020-06-09

微服务架构中分布式事务实现方案怎样何取舍

提起微服务架构，不可避免的两个话题就是服务治理和分布式事务。数据库和业务模块的垂直拆分为我们带来了系统性能、稳定性和开发效率的提升的同时也引入了一些更复杂的问题，例如在数据一致性问题上，我们不再能够依赖数据库的本地事务，对于一系列的跨库写入操作，如何保证其

middleware0 / 0评论 2020-06-09

事务，这次还有不清楚的吗，一次实战坑

以前对事务其实也有一定了解，事务最重要的应该就是。但是仅仅是皮毛。。。从别人的博客直接复制一下吧。哈哈哈，一搜都能搜到的。定义放在后面，前面主要说一下遇到的问题与解决。于是就查阅了一下相关资料，第一个方法如果没有受事务管理： 则线程内的connection

airfling / 0评论 2020-06-08

Mysql常见面试题

count运算上的区别：MyISAM有缓存，因此在做COUNT(*)时不需要消耗多少资源的。而对于InnoDB来说，则没有这种缓存。是否支持事务和崩溃后的安全恢复： MyISAM 性能高，执行速度比InnoDB更快，但是不提供事务支持。InnoDB 支持事

IBMRational / 0评论 2020-06-07

四种分布式事务粗略理解

以web系统来说,一个聚合服务 对服务发送分别的请求方法,开启begin事务,但是不进行提交,返回结果。首先对商品减1,将减1操作和操作该事务的行为写入,通过mq让下一个事务进行消费,消费如果异常在一定范围之内重试,异常则进行回滚并通过mq让上一个事务进行

韩学敏 / 0评论 2020-06-08

分布式场景之刚性事务-2PC详解

分布式场景下，多个服务同时对服务一个流程，比如电商下单场景，需要支付服务进行支付、库存服务扣减库存、订单服务进行订单生成、物流服务更新物流信息等。如果某一个服务执行失败，或者网络不通引起的请求丢失，那么整个系统可能出现数据不一致的原因。分布式一致性问题的解

CharlesYooSky / 0评论 2020-06-06

事务的传播机制

propagation_never：以非事务方式执行操作，如果当前事务存在则抛出异常。mysql默认的事务隔离级别为Repeatable-Read， Innodb引擎支持事务，MyIsam引擎不支持事务。mysql实现可重复读的方式为第一次读取数据后保存一

sofast / 0评论 2020-06-03

【SpringCloud】Spring Cloud Alibaba 之 Seata 分布式事务中间件（三十五）

什么是分布式事务问题？　　单体应用中，一个业务操作需要调用三个模块完成，此时数据的一致性由本地事务来保证。简单来说，一次业务操作需要操作多个数据源或需要进行远程调用，就会产生分布式事务问题。　　Seata 是一款开源的分布式事务解决方案，致力于提供高性能和

isHooky / 0评论 2020-05-30

事务并发问题及解决

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted?，这四个级别可以逐个解决脏读?、幻读这几类问题。最低的隔离级别。一个事务可以读取另一个事务并未提交的更新结果。大部分数据库采用的默认隔离级别。注意这会导致并发度下降，性能最差。在多

weikaixxxxxx / 0评论 2020-05-30

MySQL中的日志

　　7：中继日志。其中重做日志和回滚日志与事务操作息息相关，二进制日志也与事务操作有一定的关系，这三种日志，对理解MySQL中的事务操作有着重要的意义。　　确保事务的持久性。redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新

阿亮 / 0评论 2020-05-30

Spring 中的事务

之前总结了事务以及数据库中事务相关的知识点，Spring 对于事务做了相应的封装，便于业务开发中使用事务。项目中使用Spring中的事务首先时基于Mysql数据库中InnoDB 引擎的，如果数据库中就是使用MyISAM 引擎那么就不支持事务了。实际中很少使

neweastsun / 0评论 2020-05-27

【Oracle】PL/SQL块中使用事务

事务会把表示工作逻辑单元的SQL语句集成起来。commit语句的语法如下：COMMIT [WORK];使用savepoint命令，可以实现只有事务的部分工作被撤销。但是，SAVEPOINT仍然是活跃的，直到执行完COMMIT或者是ROLLBACK,在需要的

chenjiazhu / 0评论 2020-05-26

MySQL常见6个考题在实际工作中的运用

InnoDB是目前MySQL主流版本默认的存储引擎，支持事务、外键、行级锁，对于并发条件下要求数据的一致性，适用于对数据准确性要求高的场景。MyISAM只支持表级锁、数据排列是按照插入顺序，没有做规则排序。适合应用以查询和插入为主，只有很少量的更新和删除操

sunysh00 / 0评论 2020-05-25