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: 事务隔离级别的概念、查看和修改


隔离级别是事务中的概念。

隔离级别

名称 说明
read uncommitted 读未提交
read committed 读已提交
repeatable read 可重复读。MySQL 默认隔离级别。
serializable 串行化

隔离级别与脏读、不可重复读、幻读

隔离级别 是否会脏读 是否会不可重复读 是否会幻读
读未提交
读已提交
可重复读
串行化

上表中,隔离级别依次增高。隔离级别越高,越不会出现奇怪的*读问题。下表示关于脏读不可重复读幻读的解释:

概念 解释
脏读 当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据
不可重复读 在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的,因此称为是不可重复读。
幻读 例如第一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行,就好象发生了幻觉一样。

一般的说法是隔离级别越高,性能越差。首先串行化的性能的确很差(好像是通过锁表来解决隔离问题,待确认),事务中一个纯粹的一个select操作就会把数据锁住。但是我看到网上一些对比,可重复读的性能并不比读已提交差。另外,MySQL的可重复读,从某个角度而言,没有幻读问题。

查看当前会话的隔离级别

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+

查看全局隔离级别

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+

设置当前会话隔离级别

比如设置为读已提交:

set session transaction isolation level read committed;

比如设置为可重复读:

set session transaction isolation level repeatable read;

注意,若当前处于事务中,不能设置隔离级别。

相关MySQL文档: https://dev.mysql.com/doc/refman/5.7/en/set-transaction.html

设置全局隔离级别

比如设置为读已提交:

set global transaction isolation level read committed;

比如设置为可重复读:

set global transaction isolation level repeatable read;

设置全局隔离级别后,已经存在的会话的隔离级别是不变的。只有新会话,才会用新设置的全局隔离级别。

隔离级别示例

读已提交

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;
会话1 会话2 说明
set session transaction isolation level read committed; set session transaction isolation level read committed; 设置隔离级别
start transaction; start transaction; 开启事务
select * from test_unique; select * from test_unique; 结果为空
insert into test_unique(aaa, bbb) values(1, 1); --
select * from test_unique; -- 读出一条数据id:1,aaa:1,bbb:1
-- select * from test_unique; 结果为空
commit; -- 提交事务
-- select * from test_unique; 读出一条数据id:1,aaa:1,bbb:1
-- commit; 会话2提交事务

可重复读

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;
会话1 会话2 说明
set session transaction isolation level repeatable read; set session transaction isolation level repeatable read; 设置隔离级别
start transaction; start transaction; 开启事务
select * from test_unique; select * from test_unique; 结果为空
insert into test_unique(aaa, bbb) values(1, 1); --
select * from test_unique; -- 会话1读出一条数据id:1,aaa:1,bbb:1
-- select * from test_unique; 结果为空
commit; -- 会话1提交事务
-- select * from test_unique; 结果为空
-- commit; 会话2提交事务

串行化

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;
会话1 会话2 说明
set session transaction isolation level serializable; set session transaction isolation level serializable; 设置隔离级别
start transaction; start transaction; 开启事务
select * from test_unique; select * from test_unique; 结果为空
insert into test_unique(aaa, bbb) values(1, 1); -- 锁超时错误
-- rollback; 会话2通过回滚结束事务
insert into test_unique(aaa, bbb) values(1, 1); -- 插入成功
commit; -- 会话1提交事务

( 本文完 )

文章目录