Mysql是我们程序员日常工作中接触最为频繁的数据库,深入掌握Mysql的基本使用和进阶知识,对我们只有好处没有坏处。
今天给大家带来的是Mysql的事务隔离级别讲解,我会附带我的所有操作截图。
先贴一下我今天用到的命令:
-- 读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 串行化
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 查询事务隔离级别
SELECT @@transaction_isolation;
-- 查看自动提交开关
SHOW VARIABLES LIKE 'autocommit';
-- 关闭自动提交
SET autocommit = OFF;
-- 开启自动提交
SET autocommit = ON;
-- 开启事务
START TRANSACTION;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
首先我们要了解一下数据库常见的问题:脏读、不可重复度、幻读,它们分别表示什么意思呢?
脏读:一个事务读到了另一个事务没有提交的数据。
不可重复读:事务A读取了一条数据,读取完毕之后事务B将此数据修改并提交,这个时候事务A又来读取数据,读到了事务B修改后的数据,即事务A在自己的事务里两次读取到了不同的数据。
幻读:事务A通过一定条件统计到了M条数据,此时事务B往表里插入或者删除了一条数据,事务A在此后再次统计数据,发现数据成了M+1或者M-1条数据,即事务A在自己的事务里两次统计的数据条数不一致。
为了解决上述问题,Mysql为我们提供了四种隔离级别,分别是读未提交(READ UNCOMMITTED)、读已提交(READ COMMITTED)、可重复读(REPEATABLE READ)、串行化(SERIALIZABLE)。
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
读未提交(read-uncommitted) | 可能 | 可能 | 可能 |
读已提交(read-committed) | 不可能 | 可能 | 可能 |
可重复读(repeatable-read) | 不可能 | 不可能 | 可能 |
串行化(serializable) | 不可能 | 不可能 | 不可能 |
下面,我们通过实际操作来证实一下上述表格中的结论.
附上我测试用到的表结构:
CREATE DATABASE transaction_demo;
USE transaction_demo;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` BIGINT NOT NULL COMMENT '主键',
`name` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '姓名',
`age` INT COMMENT '年龄',
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
我们开启了2个会话窗口连上我们的测试数据库
关闭自动提交:
-- 关闭自动提交
SET autocommit = OFF;
-- 查看自动提交开关
SHOW VARIABLES LIKE 'autocommit';
我主要会在session_1中进行数据修改,在session_2中同步查看
一、读未提交(READ UNCOMMITTED)
先设置2个session的数据库隔离级别为读未提交.
--读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
在2个session里同时开启事务:START TRANSACTION;
先查询一下2个session的数据
在session_1中修改数据
在session_2中查询:
session2读到了session1没有提交的数据。
证明了读未提交没有解决脏读问题。
在session_1中将年龄信息修改成65并提交事务:
在session_2中查询
session再2次查询过程中读到了不同的数据。
证明了读未提交没有解决不可重复读问题。
在session_2统计表数据量:
在session_1新增一条数据并提交
在session_2中再次查询统计
session2两次统计的数据量不一致。
证明读未提交没有解决幻读问题.
二、读已提交(READ COMMITTED)
-- 读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
session_2重新开启一个事务并查询数据
在session_1里修改年龄为70但不提交
在session_2中再次查询
session2查出来的值没有变化,没有读到session1未提交的数据,证明读已提交解决了脏读问题。
接下来我们将session_1中的事务提交
在session_2中再次查询
查到了session_1提交事务后的数据,session_2在自己事务内的两次查询查到了不同的数据,证明读已提交没有解决不可重复读的问题。
接下来在session_1中再插入一条数据
在session_2中再次统计查询;
数据条数变成了3,session_2两次统计的数据不一致,证明读已提交没有解决幻读问题。
三、可重复读(REPEATABLE READ)
-- 可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
在session_1中将id为1的年龄改为75
在session_2中查询开启事务并查询
session_2并没有查询到session_1修改后但未提交的数据,证明可重复读解决了脏读的问题。
在session_1中提交事务
在session_2中再次查询
session_2依然没有查到提交后的数据,session_2在自己的事务内两次查询的数据都是一致的,证明可重复读解决了不可重复读的问题。
在session_1中再次插入一条数据并提交。
在session_2中查询统计
什么情况???连幻读问题也解决了???我们最后来解释这个问题
四、串行化(SERIALIZABLE)
-- 串行化
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
由于串行化在实践的过程中发生事务等待的状态,导致不能很好的进行,但是我们知道串行化是将所有的请求串行执行,这个是一定可以解决脏读、不可重复读和幻读的问题,并发转同步导致的问题是性能极差,所以一般情况下我们不会选择此隔离级别。
总结:
一般的Mysql,我们大多数时候是将数据库的隔离级别设置为可重复读。
遗留了上面的一个问题,为什么可重复读可以解决Mysql的幻读问题呢。答案是:间歇锁(Gap锁)。