MySQL:可重复读(repeatable read)隔离级别下的锁


#MySQL 事务与锁


本文中的锁,限定为 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:1id:10,aaa:10,bbb:10
start transaction start transaction 开启事务
select * from test_unique select * from test_unique 都是读出2条数据:id:1,aaa:1,bbb:1id: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 成功

( 本文完 )