MySQL 可重复读（repeatable read）隔离级别中的锁

本文中的锁，限定为 InnoDB 存储引擎下的锁。

设置读已提交：

-- 设置当前会话
set session transaction isolation level repeatable read;
-- 设置全局
set global transaction isolation level repeatable read;

查看当前会话的事务隔离级别：

select @@session.tx_isolation;

示例

下面的每个示例中都会重新执行下面的SQL：

use test;
drop table if exists test_unique;
CREATE TABLE test_unique (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `aaa` int NOT NULL,
  `bbb` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_aaa_bbb` (`aaa`, `bbb`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

示例01

会话1	会话2	说明
start transaction	start transaction	开启事务
select * from test_unique	select * from test_unique	空
insert into test_unique(id, aaa, bbb) values(1, 1, 1);	--	成功
--	insert into test_unique(id, aaa, bbb) values(1, 2, 2);	锁超时错误。如果在等待锁的期间会话1执行了 commit 。会话2会报错Duplicate entry '1' for key 'PRIMARY'。

示例02

会话1	会话2	说明
start transaction	start transaction	开启事务
select * from test_unique	select * from test_unique	查询数据为空
insert into test_unique(id, aaa, bbb) values(1, 1, 1);	--	成功
--	insert into test_unique(id, aaa, bbb) values(2, 2, 2);	成功
commit	commit	成功

示例03

会话1	会话2	说明
start transaction	start transaction	开启事务
select * from test_unique	select * from test_unique	查询数据为空
select * from test_unique where id=1 for update	--	查询数据为空
--	insert into test_unique(id, aaa, bbb) values(1, 1, 1);	锁超时错误
select * from test_unique where id=1 for update	--	查询数据为空

示例04

会话1	会话2	说明
start transaction	start transaction	开启事务
select * from test_unique	select * from test_unique	查询数据为空
select * from test_unique where id=1 for update	--	查询数据为空
--	select * from test_unique where id=1 for update	查询数据为空
insert into test_unique(id, aaa, bbb) values(1, 1, 1);	--	锁超时错误。为什么呢？因为会话1和会话2都拥有相同范围的间隙锁，这个锁是读锁。所以这里不能写了。show engine innodb status 的结果是该插入语句尝试获取插入意向锁。
select * from test_unique where id=1 for update	--	查询数据为空。

注意，读提交级别下，select for update 结果为空，不会加读锁。而可重复读级别下加读锁的原因是，要保证可重复读。

示例05

会话1	会话2	说明
start transaction	start transaction	开启事务
select * from test_unique	select * from test_unique	查询数据为空
select * from test_unique where id=10 for update	--	查询数据为空，注意因为没有数据，间隙锁的效果相当于锁表
--	insert into test_unique(id, aaa, bbb) values(2, 2, 2);	锁超时错误
select * from test_unique where id=10 for update	--	查询数据为空

示例06

会话1	会话2	说明
insert into test_unique(id, aaa, bbb) values(1, 1, 1);	--	插入数据成功
insert into test_unique(id, aaa, bbb) values(10, 10, 10);	--	插入数据成功
select * from test_unique;	select * from test_unique;	都是读出2条数据：id:1,aaa:1,bbb:1、id:10,aaa:10,bbb:10
start transaction	start transaction	开启事务
select * from test_unique	select * from test_unique	都是读出2条数据：id:1,aaa:1,bbb:1、id:10,aaa:10,bbb:10
select * from test_unique where aaa=4 and bbb=4 for update	--	未查到数据。注意这里会加间隙锁
--	insert into test_unique(id, aaa, bbb) values(4, 4, 4);	锁超时错误
select * from test_unique where id=4 for update	--	未查到数据
--	insert into test_unique(id, aaa, bbb) values(12, 12, 12);	插入成功。因为这条数据不在间隙锁的范围。
select * from test_unique where aaa=12 and bbb=12 for update	--	锁超时错误
select * from test_unique where aaa=6 and bbb=6 for update	--	查询结果为空
select * from test_unique where aaa=60 and bbb=60 for update	--	查询结果为空
commit	--	成功
--	commit	成功