MySQL 学习笔记

MySQL是一个开源的关系型数据库,非常流行。

如无特殊说明,本专题文章的 SQL 均在 5.6 版本中验证。

👉文章列表
MySQL 学习笔记 ⏬ 创建和删除数据库 切换和查看数据库 创建和删除表 在表中增加、删除、修改列 添加和删除索引 增删查改 创建和删除主键 大小写和反引号 字符串类型值的大小写 SQL注释 CHAR类型 VARCHAR类型 整型数字 时间戳 不要使用utf8 唯一索引与NULL 唯一索引的单列长度限制 NULL的判等 DDL、DML、DCL的含义 查看表的状态 show table status InnoDB存储引擎的限制 if和case的使用 自定义函数 InnoDB 索引 事务 事务隔离级别的概念、查看和修改 读已提交(read committed)隔离级别下的锁 可重复读(repeatable read)隔离级别下的锁 修改自增主键id的类型 使用 load data 快速导入数据 使用 select into outfile 导出数据

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

( 本文完 )

文章目录