本文通过一系列实验详细分析了MySQL在读提交和可重复读隔离级别下，对非索引列和索引列加锁的行为。实验表明，即使没有索引，锁也会作用在主键索引上，导致全表扫描并可能引发阻塞。在读提交级别，update会先判断条件再上锁，而在可重复读级别，update会先上锁再判断条件，可能导致更多阻塞。此外，锁定索引列时，可重复读级别会添加间隙锁以防止幻读。
锁是作用在索引上这句话可能不太好理解，并且对于在可重复读和读提交两个隔离级别下，关于是否命中二级索引的锁之间的阻塞也不太清晰。
这句话读着可能有点拗口，没事，我来给你看几个实验，对这一切就异常清晰了。
实验的 MySQL 版本为：5.7.26。
实验一：隔离级别为读提交，锁定非索引列的实验
先建个非常简单的表，只有主键索引，没有二级索引。
CREATE TABLE `yes` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`address` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
隔离级别如下：
关闭自动提交事务：
已经准备好的数据：
此时，发起事务 A，执行如下语句，且事务未提交：
接着，再发起事务 B，执行如下语句：
你可能以为事务 B 不会被阻塞，因为事务 B 锁的是name=xx和事务A锁name=yes讲道理相互之间没有冲突，但是从结果来看，事务 B 被阻塞了，调用select * from innodb_lock_waits;看下谁等谁
可以看到，事务6517(B)在等待事务6516(A)。
此时，调用 SELECT * FROM innodb_locks; 查看相关锁的信息
锁的类型就是行级锁，此时的锁为 X 锁，锁的索引就是主键索引，这个结果表明的意思是事务 B（6517）想要 id 为 1 的记录锁，但是这个记录此时被事务A（6516）占有。
是的，这里的 1 其实不是指第一个记录的意思，是 id 为 1 的记录。
可能有人疑惑， 为啥 lock_data 为 1 ？
（我没看过源码，个人推断如下：）执行 select ... for update ，由于 name 字段没有索引，索引事务 A、B 只能加锁到主键索引上，此时需要搜索 name 为 yes 的记录，但是又没有索引，只能全表扫描，恰巧扫描第一条记录就符合要求了，于是上锁，然后接着往后扫描，后面不符合条件所以没有上锁。此时事务 B 加锁，过程和事务 A 一样需要从第一条记录开始扫描上锁，但此时第一条记录已经被事务 A 锁了，所以第一条记录就冲突了，而第一条记录的 id 就是为 1，因此 lock_data 为 1。
现在，我把事务 A 提交，则事务 B 里面能立马得到结果。
从上面这个实验可以得知，如果查询条件上锁，但是没有对应的二级索引可以命中，那么锁就会锁到主键(聚簇)索引上。
而聚簇索引的非叶子节点只有主键的信息，没有 name 的信息，所以只能按顺序的全表扫描，加锁符合条件的记录，但是在扫描过程中遇到已经被加锁的记录就会被阻塞，即使这个记录不是目标记录。
看下面这个实验，你就清晰了。
这个实验其实就是把事务 A、B的语句执行的顺序换了一下。
此时，新起一个事务 C，先执行如下语句，锁的是id为2的记录：
然后，再起一个事务 D，执行：
最低0.47元/天 解锁文章
扫一扫
977
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
