知识点整理（十二）——mysql可重复读级别下的锁

背景

之前了解了mysql中的事务隔离级别，也了解了mysql中mvcc的原理。

{cat_insidepost id="17"}

现在对不同事务隔离级别下数据库是如何加锁的比较好奇。所以从理论知识和实践两方面进行学习。

准备

工具和命令

• 首先准备好mysql，这里用的是8.0.21版本。

• 接着准备表结构，一共4个字段：一个主键、一个唯一索引、一个普通索引和一个普通字段。

CREATE TABLE `lock_test` (
  `id` bigint NOT NULL COMMENT '数据库主键',
  `f_uq` int NOT NULL COMMENT '唯一索引字段',
  `f_index` int DEFAULT NULL COMMENT '索引字段',
  `f_num` int NOT NULL COMMENT '测试表',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_qu` (`f_uq`),
  KEY `idx_index` (`f_index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

• 接着自然是追加一些测试数据进去。

INSERT INTO `test`.`lock_test` (`id`, `f_uq`, `f_index`, `f_num`) VALUES (0, 0, 0, 0);
INSERT INTO `test`.`lock_test` (`id`, `f_uq`, `f_index`, `f_num`) VALUES (10, 10, 10, 10);
INSERT INTO `test`.`lock_test` (`id`, `f_uq`, `f_index`, `f_num`) VALUES (20, 20, 20, 20);
INSERT INTO `test`.`lock_test` (`id`, `f_uq`, `f_index`, `f_num`) VALUES (30, 30, 30, 30);
INSERT INTO `test`.`lock_test` (`id`, `f_uq`, `f_index`, `f_num`) VALUES (40, 40, 40, 40);
INSERT INTO `test`.`lock_test` (`id`, `f_uq`, `f_index`, `f_num`) VALUES (50, 50, 50, 50);
INSERT INTO `test`.`lock_test` (`id`, `f_uq`, `f_index`, `f_num`) VALUES (60, 60, 60, 60);
INSERT INTO `test`.`lock_test` (`id`, `f_uq`, `f_index`, `f_num`) VALUES (70, 70, 70, 70);
INSERT INTO `test`.`lock_test` (`id`, `f_uq`, `f_index`, `f_num`) VALUES (80, 80, 80, 80);
INSERT INTO `test`.`lock_test` (`id`, `f_uq`, `f_index`, `f_num`) VALUES (90, 90, 90, 90);
INSERT INTO `test`.`lock_test` (`id`, `f_uq`, `f_index`, `f_num`) VALUES (100, 100, 100, 100);

插入这样从1到100，一共100条数据用于测试。

然后需要准备需要使用的一些sql命令

• 查看数据库事务隔离级别的命令

show variables like '%isolation%';

可以看到mysql在没做任何设置的时候，InnoDB引擎的事务隔离级别是RR。

接着是设置事务隔离级别的命令，这里只要设置当前连接的事务隔离级别就可以了。

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

另外需要开3个连接，连接1和2用来模拟2个并行的事务，连接3用来查询锁。

• 查看mysql锁的命令

select ENGINE_LOCK_ID,ENGINE_TRANSACTION_ID,LOCK_MODE,LOCK_TYPE,INDEX_NAME,OBJECT_SCHEMA,OBJECT_NAME,LOCK_DATA,LOCK_STATUS,THREAD_ID from performance_schema.data_locks;

理论知识

Innodb的锁按照级别来分有表锁和行锁，按照类型来分有共享锁和排它锁。

表锁

共享锁用于并发读，排他锁用于写和写并发、读和写并发。那意向锁（IS、IX）是做什么用的呢？

当我们准备给一张表加上表锁的时候，我们首先要去 判断有没其他的事务锁定了其中了某些行？如果有的话，肯定不能加上表锁。那么这个时候我们就要去扫描整张表才能确定能不能成功加上一个表锁，如果数据量特别大，比如有上千万的数据的时候，加表锁的效率会很低。当我们在使用共享行锁时，Innodb 会自动给我们加上IS，使用排他行锁时自动加上IX ，用来表示改表中已经存在那些锁

所以，实际上意向锁（IS、IX）互相之间并不会独占，是兼容的。

自增锁比较特殊，用于表内为自增列分配值的时候使用，分配前加自增锁，insert结束后就释放。

行锁

行锁只有共享锁和排它锁，没有意向锁。

但行锁分为记录锁（Record Locks）、间隙锁（Gap Locks）、

记录锁（Record Locks）：单个行记录上加的锁。

间隙锁（Gap Locks）：锁定一段范围内的索引记录。当更新一条不存在的记录时，mysql会使用间隙锁。间隙锁会锁住一个左开右开区间。

间隙锁只在RR隔离级别下，是为了解决幻读问题，防止其他事务往索引间隙中插入数据，所以间隙锁之间是不冲突的。

临键锁（Next-Key Locks）：当我们使用范围更新，命中了记录，同时还包含范围内的间隙。mysql就会用临键锁。

临键锁会锁住一个左开右闭的区间

插入意向锁（Inseert Intention Locks）：当需要插入一条数据时，先判断是否存在间隙锁，如果存在则产生一个插入意向锁，等待间隙锁释放。

多个事务插入同一个间隙的不同位置，从而产生多个意向锁之间不会互相阻塞

锁模式

实验

主键

更新同一个主键的数据

第一个事务执行更新操作

update lock_test set f_num = 0 where id = 10;

然后检查锁，可以看到有一个IX、一个X,REC_NOT_GAP

很好理解，由于要写一条数据，所以加了表的意向排它锁，由于找到了某条记录，所以加了记录锁。

我们接着在第二个事务执行相同的语句，这个时候会发生阻塞，再看一下锁的情况。

可以看到有2个事务号，第二个事务也产生了2个锁，一个IX（因为要写数据），一个X,REC_NOT_GAP，并且这个记录锁在等待状态，这也是事务阻塞的原因。

这也验证了IX之间是互相兼容的

更新不同主键的数据

第一个事务依旧更新id为10的记录，第二个事务更新id为20的记录，这时事务并不会阻塞。

查看锁可以发现，2个记录锁锁住的记录是不一样的，之间不会等待。

更新不存在主键的数据

第一个事务中我们执行这条sql

update lock_test set f_num = 0 where id = 15;

然后检查一下锁的情况，发现这次加了一个X,GAP（间隙锁），间隙锁锁住的是左开右开的范围，从LOCK_DATA可以发现，它锁住的范围是(10,20)

第二个事务中，我们执行这样的sql会发生什么呢？

update lock_test set f_num = -1 where id = 15;

会发现事务并没有阻塞，检查锁的信息发现第二个事务加了相同的间隙锁。

这验证了间隙锁之间并不会冲突，也是兼容的

按照主键范围更新

第一个事务执行这条sql

update lock_test set f_num = 0 where id >=5 and id <=15;

第二个事务执行这条sql

update lock_test set f_num = -1 where id >=5 and id <=15;

可以看到第一个事务加了意向排它锁、间隙锁、临键锁。其中间隙锁锁住的范围依旧是（10,20），临键锁锁住的范围是（0,10] 。第二个事务成功的加了意向排它锁，但在加临键锁的时候被阻塞了，因为这个临键锁的范围也是（0,10] 。

那如果我们将第二个事务的sql修改成

update lock_test set f_num = -1 where id =10;

会发生什么呢？差别不是很大，原本第二个事务的临键锁被换成了记录锁，锁的范围是10，这个第一个事务的临键锁（0,10] 范围有重叠。

这验证了临键锁和临键锁之间、临键锁和记录锁之间都是冲突的

唯一索引

更新同一个唯一索引

第一个事务执行sql：

update lock_test set f_num = 0 where f_uq = 10;

第二个事务执行sql：

update lock_test set f_num = -1 where f_uq = 10;

查看锁的情况：

第一个事务加了意向排它锁、和2个记录锁，其中一个在主键上，另外一个在唯一索引上。第二个事务加了意向排它锁，然后在唯一索引上加记录锁时被阻塞了。

看上去，加锁的顺序是表意向锁、唯一索引上的锁、主键上的锁

更新不存在唯一索引的数据

第一个事务执行sql：

update lock_test set f_num = 0 where f_uq = 15;

第二个事务执行sql：

update lock_test set f_num = -1 where f_uq = 15;

查看锁的情况：

由于没有命中记录，所以2个事务都只加了意向排它锁和间隙锁，间隙锁之间并不冲突，没有事务阻塞。

按照范围更新唯一索引的数据

第一个事务执行sql：

update lock_test set f_num = 0 where f_uq >= 5 and f_uq<=15;

第二个事务执行sql：

update lock_test set f_num = -1 where f_uq >= 5 and f_uq<=15;

查看锁的情况：

第一个事务加了意向排它锁、2个主键上的记录锁，分别是10和20，2个唯一索引的临键锁范围分别是(0,10] 和 (10,20] 。第二个事务在加了唯一索引上的临键锁时，由于范围重叠，导致阻塞。

但是为什么要在主键20上加一个记录锁呢？临键锁的范围为什么会锁住20？

我们修改一下第二个事务的sql，让其直接更新20会发生什么？

update lock_test set f_num = -1 where f_uq = 20;

第二个事务依旧阻塞了，原因是第二个事务在唯一索引上加了记录锁，范围是20 。但和第一个事务唯一索引上的临键锁范围重叠，被阻塞。

这次我们修改第一个事务的sql，使其不命中任何数据。

update lock_test set f_num = 0 where f_uq >= 5 and f_uq<=9;

发现唯一索引上依旧被加了临键锁，主键上加了记录锁。

参照主键范围更新进行对比
主键：未命中加的是间隙锁，命中时对命中区间加临键锁，对未命中区间加间隙锁。
唯一索引：未命中加的是临键锁，命中时对整个区间加临键锁。

普通索引

更新同一个普通索引

第一个事务执行sql：

update lock_test set f_num = 0 where f_index = 10 ;

第二个事务执行sql：

update lock_test set f_num = -1 where f_index = 10 ;

查看锁的情况：

可以看到第一个事务加了意向排它锁、普通索引上的临键锁，范围是(0,10] ， 主键加了10的记录锁，最后又在普通索引上加间隙锁范围是(10,20)

第二个事务由于加了范围重叠的临键锁，所以阻塞了。

比较奇怪的是，第一个事务最后加了间隙锁，锁了(10,20) 。如果我们修改第二个事务的sql，使其更新不同的存在记录。

update lock_test set f_num = -1 where f_index = 20 ;

看到第二个事务加锁范围和第一个事务并没有重叠，所以没有阻塞。

更新不存在的普通索引数据

我们保持第一个事务sql不变，再修改第二个事务的sql，使其更新一个不存在的普通索引记录。

update lock_test set f_num = -1 where f_index = 19;

发现第二个事务加了意向排它锁、间隙锁(10,20) ， 由于间隙锁之间并不冲突，所以第二个事务也不会阻塞。

按照范围更新普通索引数据

第一个事务执行sql：

update lock_test set f_num = 0 where f_index >= 5  and f_index <=15;

第二个事务执行sql：

update lock_test set f_num = -1 where f_index >= 5  and f_index <=15;

查看锁的情况：

第一个事务加了意向排它锁、2个临键锁范围是(0,10](10,20] ，2个主键记录锁，分别锁住了10和20 。

第二个事务在加第一个临键锁的时候由于范围重叠，导致阻塞。

那如果没有命中记录会怎么样呢？我们修改第一个事务sql

update lock_test set f_num = 0 where f_index >= 5  and f_index <=9;

事务加了一个意向排它锁、一个普通索引临键锁（0,10] 和 一个主键记录锁，显然这样我们是无法在其他事务中更新10这条记录的。

参照主键范围更新、唯一索引范围更新进行对比
主键：未命中加的是间隙锁，命中时对命中区间加临键锁，对未命中区间加间隙锁。
唯一索引：未命中加的是临键锁，命中时对整个区间加临键锁。
普通索引：未命中加的是临键锁，命中时对整个区间加临键锁。

无索引

更无索引的一条记录

第一个事务执行sql：

update lock_test set f_num = 0 where f_num = 10;

第二个事务执行sql：

update lock_test set f_num = -1 where f_num = 10;

查看锁的情况：

第一个事务给表里的所有主键加了记录锁。第二个事务同样，阻塞在了第一个主键的临键锁上。

我们修改第一个事务，使其不命中任何记录，会发生什么？

update lock_test set f_num = 0 where f_num = 5;

同样给表里的所有主键加了临键锁。

这个时候，我们insert 一条记录会发生什么呢？

INSERT INTO `test`.`lock_test` (`id`, `f_uq`, `f_index`, `f_num`) VALUES (11, 11, 11, 11);

插入记录的时候，加了意向排它锁，然后在主键上加了插入意向锁(10,20) ，它需要等待临键锁的释放。

按照范围更新普通索引的数据

update lock_test set f_num = 0 where f_num >5 and f_num < 15;
update lock_test set f_num = 0 where f_num >5 and f_num < 7;

结果和之前一样，给表里所有主键加了临键锁。

总结

我们再更新记录是应该遵循以下优先级，尽量使用加锁力度小的sql来更新。