Mysql事务的四种隔离级别- 醉丶春风的Blog

事务的基本要素(ACID)

数据库事务(Transanction)正确执行的四个基本要素：

原子性（Atomicity）: 事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
一致性（Consistency）：指事务将数据库从一种状态转变为另一种一致的的状态。事务开始前和结束后，数据库的完整性约束没有被破坏。例如工号带有唯一属性，如果经过一个修改工号的事务后，工号变的非唯一了，则表明一致性遭到了破坏。
隔离性（Isolation）：要求每个读写事务的对象对其他事务的操作对象能互相分离，即该事务提交前对其他事务不可见。也可以理解为多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。这指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。例如一个用户在更新自己的个人信息的同时，是不能看到系统管理员也在更新该用户的个人信息（此时更新事务还未提交）。
持久性（Durability）：事务一旦提交，则其结果就是永久性的。即使发生宕机的故障，数据库也能将数据恢复，也就是说事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

事务的隔离级别

在MySQL中隔离性有4种级别，分别是：

READ UNCOMMITTED（读未提交）: 该隔离级别的事务会读到其它未提交事务的数据，此现象也称之为脏读。
READ COMMITTED（读已提交）: 一个事务可以读取另一个已提交的事务，多次读取会造成不一样的结果，此现象称为不可重复读问题，Oracle 和 SQL Server 的默认隔离级别。
REPEATABLE READ（可重复读）: 该隔离级别是 MySQL 默认的隔离级别，在同一个事务里，select 的结果是事务开始时时间点的状态，因此，同样的 select 操作读到的结果会是一致的，但是，会有幻读现象
SERIALIZABLE（串行化）: 在该隔离级别下事务都是串行顺序执行的，MySQL 数据库的 InnoDB 引擎会给读操作隐式加一把读共享锁，从而避免了脏读、不可重读复读和幻读问题。

四种隔离级别间的区别如下:

事务隔离级别	脏读	不可重复读	幻读
读未提交（read uncommitted）	是	是	是
读已提交（read committed）	否	是	是
可重复读（repeatable read）	否	否	是
串行化（serializable）	否	否	否

演示

下面我们就由低到高的来演示一下这4种隔离级别

准备一张表, 并插入两条数据

-- 创建一张表
CREATE TABLE `tbl_user1` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) DEFAULT '',
  `password` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- 插入两条数据
INSERT INTO `test`.`tbl_user`(`id`, `email`, `password`) VALUES (1, '111@qq.com', '123456');
INSERT INTO `test`.`tbl_user`(`id`, `email`, `password`) VALUES (2, '222@qq.com', '123456');

修改命令提示符

由于在命令行打开mysql, 前面的提示符永远都是mysql>, 不利于我们进行区分, 因此可以修改该提示符, 下面给出两种临时方法

登录时设置, mysql -uroot -p --prompt 提示符
登录后设置, prompt 提示符 , 注意该提示符不用加分号, 输入什么显示的就是什么

这里我们修改两个客户端名字分别为 mysql-client-1>, mysql-client-2>

关闭自动提交

关闭了自动提交, 需要自己主动调用 commit; rollback; 才会生效

打开两个mysql命令窗口, 并且关闭mysql的自动提交功能, 在两个窗口都执行 set autocommit = off;

mysql-client-2> set autocommit = off;
Query OK, 0 rows affected (0.00 sec)

-- 可以看到, 自动提交已经关闭
mysql-client-2> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

读未提交(READ UNCOMMITTED)

将mysql-client-1设置事务隔离级别为 read uncommitted

set session transaction isolation level read uncommitted;

先在mysql-client-1查询一次数据: ↓↓↓

mysql-client-1> select * from tbl_user;
+----+------------+----------+
| id | email      | password |
+----+------------+----------+
|  1 | 111@qq.com | 123456   |
|  2 | 222@qq.com | 123456   |
+----+------------+----------+
2 rows in set (0.00 sec)

此时数据是正常的, 下面我们在mysql-client-2中插入一条数据, 并且不提交事务 ↓↓↓

mysql-client-2> insert into tbl_user (id, email, password) values (3, 333, 123);
Query OK, 1 row affected (0.02 sec)

插入成功, 现在使用mysql-client-1查询一次数据 ↓↓↓

mysql-client-1> select * from tbl_user;
+----+------------+----------+
| id | email      | password |
+----+------------+----------+
|  1 | 111@qq.com | 123456   |
|  2 | 222@qq.com | 123456   |
|  3 | 333        | 123      |
+----+------------+----------+
3 rows in set (0.00 sec)

可以看到, mysql-client-1将 mysql-client-2中未提交的数据读取到了, 假如现在我在mysql-client-2中回滚数据, 然后再查询 mysql-client-1, 又会出现什么情况呢, 请看下面 ↓↓↓

-- 先回滚
mysql-client-2> rollback;
Query OK, 0 rows affected (0.00 sec)

-- 查询 mysql-client-1
mysql-client-1> select * from tbl_user;
+----+------------+----------+
| id | email      | password |
+----+------------+----------+
|  1 | 111@qq.com | 123456   |
|  2 | 222@qq.com | 123456   |
+----+------------+----------+
2 rows in set (0.00 sec)

随着mysql-client-2的回滚, mysql-client-1的结果也随之变化

这种现象就叫做读未提交, 即一个事务读取了另一个事务未提交的数据, 会产生脏读

读已提交(READ COMMITTED)

在两个窗口分别执行 commit 防止影响

设置mysql-client-1的事务隔离级别为 read committed

set session transaction isolation level read committed;

先查看数据 ↓↓↓

mysql-client-1> select * from tbl_user;
+----+------------+----------+
| id | email      | password |
+----+------------+----------+
|  1 | 111@qq.com | 123456   |
|  2 | 222@qq.com | 123456   |
+----+------------+----------+
2 rows in set (0.01 sec)

下面在mysql-client-2中插入一条数据且不提交, 并在mysql-client-1中再次查询 ↓↓↓

-- 先插入一条数据
mysql-client-2> insert into tbl_user (id, email, password) values (4, 444, 1234);
Query OK, 1 row affected (0.00 sec)

-- 再次查询
mysql-client-1> select * from tbl_user;
+----+------------+----------+
| id | email      | password |
+----+------------+----------+
|  1 | 111@qq.com | 123456   |
|  2 | 222@qq.com | 123456   |
+----+------------+----------+
2 rows in set (0.00 sec)

此时我们会发现, mysql-client-1中并没有查询到 mysql-client-2中未提交的数据, 下面将数据提交一下再次查询↓↓↓

mysql-client-2> commit;
Query OK, 0 rows affected (0.08 sec)


mysql-client-1> select * from tbl_user;
+----+------------+----------+
| id | email      | password |
+----+------------+----------+
|  1 | 111@qq.com | 123456   |
|  2 | 222@qq.com | 123456   |
|  4 | 444        | 1234     |
+----+------------+----------+
3 rows in set (0.00 sec)

脏读的问题解决了, 但是又带来了一个新的问题, 那就是不可重复读

不可重复读

一个事务可以读取另一个已提交的事务，多次读取会造成不一样的结果，此现象称为不可重复读问题，Oracle 和 SQL Server 的默认隔离级别。

可重复读 (REPEATABLE READ)

该级别为mysql默认的事务隔离级别

在两个窗口分别执行 commit 防止影响, 一定要执行, 不然设置的隔离级别不生效

设置mysql-client-1的事务隔离级别为 repeatable read

set session transaction isolation level repeatable read;

先查询下数据

mysql-client-1> select * from tbl_user;
+----+------------+----------+
| id | email      | password |
+----+------------+----------+
|  1 | 111@qq.com | 123456   |
|  2 | 222@qq.com | 123456   |
|  4 | 444        | 1234     |
+----+------------+----------+
3 rows in set (0.00 sec)

在 mysql-client-2中插入一条数据, 并再次查询mysql-client-1 ↓↓↓

mysql-client-2> insert into tbl_user (id, email, password) values (5, 555, 12345);
Query OK, 1 row affected (0.01 sec)


mysql-client-1> select * from tbl_user;
+----+------------+----------+
| id | email      | password |
+----+------------+----------+
|  1 | 111@qq.com | 123456   |
|  2 | 222@qq.com | 123456   |
|  4 | 444        | 1234     |
+----+------------+----------+
3 rows in set (0.00 sec)

将mysql-client-2的事务提交, 并再次查询mysql-client-1 ↓↓↓

mysql-client-2> commit;
Query OK, 0 rows affected (0.02 sec)
-- 再同一个事务中, 再次查询, 结果总是一致
mysql-client-1> select * from tbl_user;
+----+------------+----------+
| id | email      | password |
+----+------------+----------+
|  1 | 111@qq.com | 123456   |
|  2 | 222@qq.com | 123456   |
|  4 | 444        | 1234     |
+----+------------+----------+
3 rows in set (0.00 sec)

此时可以发现, 即使另一个事务提交了, 但是也不会影响当前的事务, 同一个事务中, 读取多次, 结果总是一致的, 这就是可重复读

此时, 如果我们要在mysql-client-1中也插入一条id为5的数据会怎么样??? , 下面就来实验一下↓↓↓

mysql-client-1> insert into tbl_user (id, email, password) values (5, 55, 555);
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

结果就是会报错, 明明没有id为5的数据, 为什么不能插入呢, 像产生了幻觉一样, 这种情况叫做幻读, 幻读和可重复读很像, 关于他们的具体区别, 可以查看下这篇文章

https://segmentfault.com/a/1190000012669504

串行化（SERIALIZABLE）

在该隔离级别下事务都是串行顺序执行的，MySQL 数据库的 InnoDB 引擎会给读操作隐式加一把读共享锁，从而避免了脏读、不可重读复读和幻读问题。

在两个窗口分别执行 commit 防止影响, 一定要执行, 不然设置的隔离级别不生效

设置mysql-client-1和mysql-client-2的事务隔离级别为 repeatable read

set session transaction isolation level serializable;

先在mysql-client-2中插入一条数据, 并且不提交, 然后再切换到 mysql-client-1中查询数据

mysql-client-2> insert into tbl_user (id, email, password) values (7, 777, 7777);
Query OK, 1 row affected (0.01 sec)

mysql-client-1> select * from tbl_user;
此时mysql-client-1会卡住

这时会发现, mysql-client-1卡住了, 如果这个时候切换到mysql-client-2中提交数据, mysql-client-1中就会马上出现数据

mysql-client-2> commit;
Query OK, 0 rows affected (0.04 sec)

-- mysql-client-1
+----+------------+----------+
| id | email      | password |
+----+------------+----------+
|  1 | 111@qq.com | 123456   |
|  2 | 222@qq.com | 123456   |
|  4 | 444        | 1234     |
|  5 | 555        | 12345    |
|  7 | 777        | 7777     |
+----+------------+----------+
5 rows in set (44.57 sec)

一旦事务提交，mysql-client-1 会立马返回数据，否则会一直卡住，直到超时，其中超时参数是由 innodb_lock_wait_timeout 控制。由于每条 select 语句都会加锁，所以该隔离级别的数据库并发能力最弱，

醉丶春风的Blog

千里之行, 始于足下