基础 MySQL:简介 Ubuntu 安装 MySQL MySQL:官方文档地址 MySQL:DDL、DQL、DML、DCL的含义 MySQL:Mac 中启动 关闭 MySQL 服务 MySQL:有哪些好用的管理工具? MySQL:命令行工具 mycli MySQL:CHAR类型 MySQL:VARCHAR类型 MySQL:整型数字 MySQL:datetime 类型 MySQL:时间戳 MySQL:创建和删除数据库 MySQL:切换和查看数据库 MySQL:创建和删除表 MySQL:在表中增加、删除、修改列 MySQL:创建和删除主键 MySQL:使用 rename 修改表名 MySQL:修改自增主键id的类型 MySQL:如何创建一个相同的表 MySQL:修改表的字符编码 MySQL:增删查改 MySQL:插入数据 MySQL:插入多行数据 MySQL:使用 insert set 插入数据 MySQL:大小写和反引号 MySQL:字符串类型值的大小写 MySQL:SQL注释 MySQL:不要使用utf8 MySQL:NULL的判等 MySQL:InnoDB存储引擎的限制 MySQL:if和case的使用 MySQL:使用 load data 快速导入数据 MySQL:使用 select into outfile 导出数据 MySQL:查询和设置 sql_mode MySQL:严格模式 MySQL:NOT NULL 字段不插入数据,会发生什么? MySQL:无符号整数列插入负数会发生什么? MySQL:关于 null 的那些事 MySQL:大表行数查询 MySQL:自动生成创建时间、更新时间;自动更新更新时间 MySQL:insert ignore MySQL:字符集排序规则 MySQL:如果连续更新一个字段两次,结果是? MySQL:字符串转数字 MySQL:尾部空格 MySQL:添加和删除索引 MySQL:唯一索引与NULL MySQL:唯一索引的单列长度限制 MySQL:InnoDB 索引 MySQL:字符集排序规则对唯一索引的影响 MySQL:唯一索引冲突消耗主键 ID MySQL 使用 index hint 指定索引:ignore index、force index、use index MySQL:查看客户端连接信息 MySQL:查看表的状态 show table status MySQL:如何治理连接数 ? MySQL:如何监控和处理慢查询与长事务 ? MySQL:自定义函数 MySQL:now() 函数 MySQL:unix_timestamp() 函数 MySQL:from_unixtime() 函数 MySQL:version() 函数 MySQL:current_timestamp() 函数 MySQL:cast 函数 MySQL:convert 函数 MySQL:使用 greatest、least 函数获取行最大值、最小值 MySQL:使用 group_concat 函数连接多行数据为一个字符串 MySQL:获取版本号 MySQL:Java 类型映射 MySQL下创建只能有一行记录的table 关于MySQL的字符集 理解数据库中的undo日志、redo日志、检查点 ubuntu下源码安装MySQL MySQL:JOIN解惑 如何快速更新数据库中的百万条数据

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 成功


( 本文完 )