今天聊聊老生长谈的mysql事务隔离级别,相信大家应该听到这个名词不会陌生。我们都知道事务具有ACID特性(原子性,一致性,持久性,隔离性),今天聊的主题肯定是跟隔离性有关的,以下都基于mysql innodb引擎
名词概念说明
- 脏读
读取到其他事务还没有提交的数据例:事务A在执行过程中,事务B插入or修改一条数据此刻尚未提交。A此刻查询出了B插入的记录,若B事务回滚。则A查询出了不正确的记录,这就是脏读现象
- 不可重复读
同一事务内查询,由于别的事务将数据修改or删除,导致执行两次查询结果可能不一样。
例:事务A执行查询年龄小于28岁的员工,用户id 为 1 ,3,5,10.此刻事务B修改了id为3的年龄为30岁,A再次执行查询少了id为3的记录。两次查询结果集不一样,这就是不可重复读现象
- 幻读
通常针对插入操作,与不可重复读的区别是不可重复读,是由于数据变更导致数据变少。幻读是因为后一次insert操作导致数据增加,先前没有读取到。
例:事务A将数据库性别为男的年龄都update成28,此刻A事务没提交事务B插入了一个性别男30岁的记录。A事务查询记录发现还有一条没有修改,就会感觉产生了幻觉一样。事务隔离级别
- 读未提交(Read Uncommited)
- 读已提交(Read Commited)
- 可重复读(Repeatable Read)
- 串行化(Serializable)
上述隔离级别隔离强度逐渐增强,性能逐渐下降。可重复读为mysql默认隔离级别。以下表格列出相应隔离级别是否会出现上述问题
隔离级别 | 脏读 | 不可重复读 | 幻读 |
读未提交(Read Uncommited) | 可能 | 可能 | 可能 |
读已提交(Read Commited) | 不可能 | 可能 | 可能 |
可重复读(Repeatable Read) | 不可能 | 不可能 | 其他引擎可能(innodb 利用间隙锁) |
串行化(Serializable) | 不可能 | 不可能 | 不可能 |
纸上得来终觉浅,实操一把。
测试表结构
CREATE TABLE `user_info` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` char(4) COLLATE utf8mb4_bin NOT NULL,
`sex` char(1) COLLATE utf8mb4_bin DEFAULT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
-- 初始化数据
insert into `user_info`(`id`,`name`,`sex`,`age`) values (1,'One','男',29),(2,'貂蝉','女',27),(3,'王昭君','女',18),(4,'吕布','男',30);
-- 查看mysql版本 我的是5.7.33
SELECT VERSION();
-- 查看当前数据库默认隔离级别
SHOW VARIABLES LIKE '%transaction_isolation%'
-- 设置手动提交事务
SET autocommit = 0;
SHOW VARIABLES LIKE 'autocommit';
读未提交测试
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SHOW VARIABLES LIKE '%transaction_isolation%'
-- 设置之后,只对新session生效。需要重新连接mysql。
-- 设置手动提交事务
SET autocommit = 0;
SHOW VARIABLES LIKE 'autocommit';
-- 会话1 执行修改id1的年龄,并未手动commit
UPDATE user_info SET age=30 WHERE id =1;
-- 在会话2 执行查询,已经查询到了修改后的数据
SELECT * FROM user_info WHERE id =1;
读已提交测试
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SHOW VARIABLES LIKE '%transaction_isolation%'
-- 设置之后,只对新session生效。需要重新连接mysql。
-- 设置手动提交事务
SET autocommit = 0;
SHOW VARIABLES LIKE 'autocommit';
此刻表当前数据为
-- 会话1修改年龄,未commit
UPDATE user_info SET age=30 WHERE id =1;
SELECT * FROM user_info
-- 会话2是查询不到更新记录的
SELECT * FROM user_info
执行commit后
可重复读测试
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SHOW VARIABLES LIKE '%transaction_isolation%'
-- 设置之后,只对新session生效。需要重新连接mysql。
-- 设置手动提交事务
SET autocommit = 0;
SHOW VARIABLES LIKE 'autocommit';
当前数据
-- 会话1 开启事务读取数据
START TRANSACTION;
SELECT * FROM user_info;
-- 会话2 修改数据
START TRANSACTION;
UPDATE user_info SET age=29 WHERE id =1;
COMMIT;
-- 会话1 执行查询,数据跟第一次查询一致
SELECT * FROM user_info;
实现方式
- 读未提交(Read Uncommited)
性能最好,没有任何加锁操作,没有隔离效果
- 读已提交(Read Commited)
mvcc readview 版本链
- 可重复读(Repeatable Read)
InnoDB的MVCC(多版本并发控制)解决了可重复读问题,每一行实际都可能存在多个版本记录,数据记录有一个trx_id 代表事务id。可重复读是在事务开始的时候生成一个当前事务全局性的快照,而读提交则是每次执行语句的时候都重新生成一次快照。当前读解决幻读是借助于行锁跟间隙锁结合,这个锁称为Next-Key锁。
- 串行化(Serializable)
单线程执行,读的时候加共享锁允许并发读。写的时候使用排它锁,其他事务不能并发写也不能并发读。