文章目录
- 1 摘要
- 2 MySQL 事务隔离级别划分
- 3 MySQL 事务隔离级别的查询与设置
- 4 事务隔离演示准备
- 4.1 数据准备
- 4.2 MySQL 事务开启与回滚命令
- 5 不同事务隔离级别下异常演示
- 5.1 事务隔离级别为未提交读(read uncommitted)
- 5.2 事务隔离级别为提交读(read committed)
- 5.3 事务隔离级别为可重复读(repeatable read)
- 5.4 事务隔离级别为序列化(serializable)
- 6 参考资料推荐
1 摘要
关于 MySQL 的事务隔离级别，是面试中经常问到的问题(虽然大多数项目中都用不到)，作者在这里整理了一些笔记，仅供参考。
2 MySQL 事务隔离级别划分
数据库事务隔离级别分为四个等级,分别为:未提交读( read-uncommitted)、提交读(read committed)、可重复读(repeatable read)、串行序列化(serializable)
事务等级与可能出现的异常:
事务等级 | 脏读 | 不可重复读 | 幻读 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
从上到下，事务的隔离级别逐渐增高；事务的隔离级别越高，程序执行效率越低。
MySQL 的默认事物隔离级别为 repeadable read
名词解释:
脏读: 在一个事务中读取到了另一个未提交事务的数据，即为脏读
不可重读读: 在一个事务处理过程中，另一个事物插入进来，并更新了数据，原先的事务前后两次相同的语句查询，结果不一样，即为不可重复读
幻读: 在一个事物处理过程中，另一个事务插入进来，并更新了数据，此时原来的事务也插入数据，则实际上会受到后一个事务数据的影响，即为幻读。
3 MySQL 事务隔离级别的查询与设置
-- 查询 mysql 版本
SELECT VERSION();
-- 查询 mysql 事务隔离级别(5.7 及以下)
SELECT @@tx_isolation;
-- 查询 mysql 事务隔离级别(5.7+)
SELECT @@transaction_isolation;设置 MySQL 事务隔离级别:
SET {SESSION | GLOBAL} TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};SESSION: 为当前会话
GLOBAL: 为本次连接所有会话
示例:
-- 设置当前会话的事务隔离级别为提交读(`READ COMMITTED`)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;无论是 SESSION 还是 GLOBAL 级别，断开 MySQL 连接,下次再连接的时候,还是原来的事务隔离级别,因此彻底更新MySQL 事务隔离级别，需要修改 MySQL 配置文件
Linux 系统下 MySQL 配置文件位置为: /etc/my.conf
在 [mysqld] 标签下配置(在其他标签下设置无效,如果没有该标签则手动添加):
[mysqld]
transaction-isolation = READ-COMMITTED在配置文件中设置的事务隔离级别为: READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALEZABLE
配置文件修改之后需要重启 MySQL 服务
4 事务隔离演示准备
4.1 数据准备
数据库表
-- 用户信息表
DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id 主键',
`user_name` varchar(30) DEFAULT '' COMMENT '用户名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';创建测试数据
-- 批量插入用户信息
INSERT INTO `user_info`(`user_name`) VALUES ('张三'),
('李四'),
('王五');4.2 MySQL 事务开启与回滚命令
-- 开启事务
start transaction;
-- do sometring
-- 提交事务
commit;-- 开启事务
start transaction;
-- do sometring
-- 回滚事务
rollback;5 不同事务隔离级别下异常演示
5.1 事务隔离级别为未提交读(read uncommitted)
会话1 | 会话2 |
设置当前会话事务隔离级别:
| |
查询当前会话事务隔离级别:
| |
设置当前会话事务隔离级别:
| |
查询当前会话事务隔离级别:
| |
开启事务:
| |
查询用户信息表所有数据:
| |
开启事务:
| |
向用户信息表插入一条数据:
| |
查询用户信息表所有数据:
|
此时，会话1中查出了会话2未提交的数据，即为脏读
5.2 事务隔离级别为提交读(read committed)
会话1 | 会话2 |
设置当前会话事务隔离级别:
| |
查询当前会话事务隔离级别:
| |
设置当前会话事务隔离级别:
| |
查询当前会话事务隔离级别:
| |
开启事务:
| |
查询用户信息表所有数据:
| |
开启事务:
| |
向用户信息表插入一条数据:
| |
查询用户信息表所有数据:
|
此时会话1中没有查询到会话2中未提交的数据，即没有出现脏读。
继续试验
会话1 | 会话2 |
提交事务:
| |
查询用户信息表所有数据:
|
此时，会话1中查询到了会话2中提交的数据，会话1同一个事务中前后进行了两次相同的查询，但是结果却不一致，即为不可重复读。
5.3 事务隔离级别为可重复读(repeatable read)
会话1 | 会话2 |
设置当前会话事务隔离级别:
| |
查询当前会话事务隔离级别:
| |
设置当前会话事务隔离级别:
| |
查询当前会话事务隔离级别:
| |
开启事务:
| |
查询用户信息表所有数据:
| |
开启事务:
| |
向用户信息表插入一条数据:
| |
查询用户信息表所有数据:
|
此时会话1中没有查询到会话2中未提交的数据，即没有出现脏读。
继续试验
会话1 | 会话2 |
提交事务:
| |
查询用户信息表所有数据:
|
此时，会话1中还是没有查询到会话2的事务提交的数据，即没有出现不可重复读的问题。
继续试验
会话1 | 会话2 |
向用户信息表插入一条数据:
提示错误:
|
会话1中没有查询到 id 为 6 的数据，但是插入一条 id 为 6 的数据，却提示插入失败，是因为在会话 2 中已经插入了一条 id 为 6 的数据，这种现象即为幻读。
repeadable read 事务隔离级别虽然解决了可重复读的问题，但是其他事务已经插入的数据仍然会对当前事务造成影响，即会出现幻读问题。
5.4 事务隔离级别为序列化(serializable)
会话1 | 会话2 |
设置当前会话事务隔离级别:
| |
查询当前会话事务隔离级别:
| |
设置当前会话事务隔离级别:
| |
查询当前会话事务隔离级别:
| |
开启事务:
| |
查询用户信息表所有数据:
| |
开启事务:
| |
向用户信息表插入一条数据:
结果为: 线程阻塞,等待提交 等待一段时间后提示:
| |
提交事务:
| |
向用户信息表插入一条数据:
提示插入成功 | |
提交事务:
|
从示例中可以看出，当数据库事务隔离级别设置为序列化(serializable)时，可以同时开启多个事务，但是每次只能由一个事务进行提交，而且事务提交顺序遵循先进先出原则(谁先开启的事务，谁就能先提交，后开启的时候必须等待前边的事务提交之后才能提交)，如果后开启的事务等待超时，会导致提交失败，并重新开启事务
