理解 Mysql 事务隔离级别

多个事务并发的情况下,如果没有任何限制,一个事务的操作可能会影响另一个事务的执行结果,从而造成脏读、不可重复读、幻读等并发一致性问题。

并发一致性问题

脏读

脏读是指一个事务读取到另一个事务未提交的记录。比如:

  1. 事务 A 对数据库的一条记录进行了修改,但是 还未提交

  2. 此时事务 B 从数据库查询该条记录,查到了 事务 A 修改后的数据

  3. 然后事务 A 因为某种原因回滚,导致修改未成功提交,该条记录还是原来的值

上述情况下,第 2 步事务 B 就存在脏读

不可重复读

不可重复读是指一个事务执行过程中,存在另外一个事务对数据进行了修改并提交,结果导致前一事务中两次读取到的值不一样。例如单条记录的情况:

  1. 事务 A 读取了一条记录的值是 before

  2. 事务 B 修改了该记录的值为 after,并且提交成功

  3. 事务 A 再次读该记录,得到的值是 after

上述情况下,事务 A 在同一次事务中两次读取同一记录的值不一样,这就是不可重复读

幻读

幻读是指在一个事务的执行过程中,存在另外一个事务对数据集进行了修改(新增或删除了记录),结果导致这两个事务查询到的数据集不一致。比如说

  1. 事务 A 根据主键 ID 统计 1 至 15 之间的记录数量,得到一个结果 15

  2. 事务 B 成功删除了主键 ID 为 13 的记录,统计 1 至 15 之间的记录数量,结果为14

  3. 此时事务 A 再次进行统计,结果还是 15

上述情况下,事务 A 第二次统计的结果不正确,在此基础上进行后续操作可能会出错

事务隔离

事务隔离用于解决事务的并发一致性问题,Mysql 提供了 4 种隔离级别,用于解决上文提到的问题。不同的隔离级别与一致性问题的关系如下表所示:

隔离级别脏读不可重复读幻影读
未提交读(read uncommitted)
提交读(read committed)
可重复读(repeatable read)
可串行化(serializable)

从上图可见级别从低到高为:未提交读 -> 提交读 -> 可重复读 -> 可串行化

未提交读(read uncommitted)

未提交读是最低的隔离级别,存在脏读情况,可以通过一个简单的例子验证下。先准备下测试用的数据

准备工作

当前使用的 mysql 版本如下

1
2
➜  ~ mysql --version
mysql Ver 8.0.12 for osx10.14 on x86_64 (Homebrew)

首先创建一个测试用表 USER,存储引擎选择 InnoDB,注意不要使用 MyISAM 引擎,因为它不支持事务,测了也白测。

1
2
3
4
5
6
CREATE TABLE `USER` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`USERNAME` varchar(20) DEFAULT NULL,
`USERID` int(11) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

新增两条测试数据,下面就操作这些数据验证并发问题

1
2
3
4
INSERT INTO `USER` (`ID`, `USERNAME`, `USERID`)
VALUES
(1, 'admin', 1),
(2, 'test', 2);

测试过程

按如下步骤操作,测试 read uncommitted 隔离级别下脏读问题。为方便说明问题,这里使用了表格,可按照序号往下看,主要关注事务1和事务2的执行命令

事务1事务2
序号执行命令输出结果说明执行命令输出结果说明
1set session transaction isolation level read uncommitted;Query OK, 0 rows affected (0.00 sec)设置session事务隔离级别
2start transaction;Query OK, 0 rows affected (0.00 sec)开启事务
3select * from user;1 admin 1 2 test 2查看当前数据
4update user set userid = 3 where id = 2;Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0更新id为2的数据
5set session transaction isolation level read uncommitted;Query OK, 0 rows affected (0.00 sec)设置session事务隔离级别
6start transaction;Query OK, 0 rows affected (0.00 sec)开启事务
7select * from user where id = 2;2 test 3可以看到事务1尚未提交的数据
8rollback;Query OK, 0 rows affected (0.12 sec)事务1回滚
9select * from user where id = 2;2 test 2看到的是原数据

第 4 步 事务1 修改了 id = 2 的记录,尚未提交事务。

第 7 步 事务2 可以查询到 事务1 尚未提交的修改。第 8 步 事务1 回滚,此时 事务2 再次查询看到的是修改前的结果。

提交读(read committed)

脏读问题

read committed 事务隔离级别解决了脏读的问题,也就是不会读到其他事务尚未提交的对单条记录的修改。同样是上面的操作顺序,区别是隔离级别不同,下面看下执行结果

事务1事务2
序号执行命令输出结果说明执行命令输出结果说明
1set session transaction isolation level read committed;Query OK, 0 rows affected (0.00 sec)设置session事务隔离级别为提交读
2start transaction;Query OK, 0 rows affected (0.00 sec)开启事务
3select * from user;1 admin 1 2 test 2查看当前数据
4update user set userid = 3 where id = 2;Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0更新id为2的数据
5set session transaction isolation level read committed;Query OK, 0 rows affected (0.00 sec)设置session事务隔离级别为提交读
6start transaction;Query OK, 0 rows affected (0.00 sec)开启事务
7select * from user where id = 2;2 test 2无法看到事务1尚未提交的数据
8commit;Query OK, 0 rows affected (0.12 sec)事务1提交
9select * from user where id = 2;2 test 3看到的是修改后的数据

注意第 7 步,这次 事务2 不能看到 事务1 未提交的修改了。因为事务1 还未提交,所以 事务2 查询到的还是修改前的数据

不可重复读问题

read committed 事务隔离级别下还存在不可重复读问题。

下面在 事务1 中两次读取同一记录,在这之间通过 事务2 对该记录进行修改并提交

事务1事务2
序号执行命令输出结果说明执行命令输出结果说明
1set session transaction isolation level read committed;Query OK, 0 rows affected (0.00 sec)设置session事务隔离级别为提交读
2start transaction;Query OK, 0 rows affected (0.00 sec)开启事务
3select * from user where id = 2;2 test 3查看当前数据
4set session transaction isolation level read committed;Query OK, 0 rows affected (0.00 sec)设置session事务隔离级别为提交读
5start transaction;Query OK, 0 rows affected (0.00 sec)开启事务
6select * from user where id = 2;2 test 3查看当前数据
7update user set userid = 4 where id = 2;Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0查看当前数据
8commit;Query OK, 0 rows affected (0.12 sec)事务2提交
9select * from user where id = 2;2 test 4查看当前数据,与上次查询结果不一致

事务1 在第 3 步 和第 9 步分别查询了 id = 2 的记录,在这中间 事务2 修改了该条记录并提交成功,导致前者在一次事务中两次查询同一记录的结果不一致。

可重复读(repeatable read)

不可重复读问题

将事务隔离级别修改为 repeatable read,重复上次操作,再看下 事务1 的两次查询结果

事务1事务2
序号执行命令输出结果说明执行命令输出结果说明
1set session transaction isolation level repeatable read;Query OK, 0 rows affected (0.00 sec)设置session事务隔离级别为提交读
2start transaction;Query OK, 0 rows affected (0.00 sec)开启事务
3select * from user where id = 2;2 test 4查看当前数据
4set session transaction isolation level repeatable read;Query OK, 0 rows affected (0.00 sec)设置session事务隔离级别为提交读
5start transaction;Query OK, 0 rows affected (0.00 sec)开启事务
6select * from user where id = 2;2 test 4查看当前数据
7update user set userid = 5 where id = 2;Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0查看当前数据
8commit;Query OK, 0 rows affected (0.12 sec)事务2提交
9select * from user where id = 2;2 test 4查看当前数据,与上次查询结果一致

注意第 3 步和第 9 步查询结果一致,这说明已经不存在「不可重复读」问题了

幻读问题

事务1 根据查询结果新增数据,在查询前 事务2 新增了一条记录

事务1事务2
序号执行命令输出结果说明执行命令输出结果说明
1set session transaction isolation level repeatable read;Query OK, 0 rows affected (0.00 sec)设置session事务隔离级别为提交读
2start transaction;Query OK, 0 rows affected (0.00 sec)开启事务
3set session transaction isolation level repeatable read;Query OK, 0 rows affected (0.00 sec)设置session事务隔离级别为提交读
4start transaction;Query OK, 0 rows affected (0.00 sec)开启事务
5select * from user;1 admin 1 2 test 5查看当前所有数据
6insert into user value(3, “aaa”, 3);Query OK, 1 row affected (0.02 sec)新增id为3的记录
7commit;Query OK, 0 rows affected (0.12 sec)事务2提交
8select * from user;1 admin 1 2 test 5查看当前所有数据
9insert into user value(3, “bbb”, 3);ERROR 1062 (23000): Duplicate entry ‘3’ for key ‘PRIMARY’ mysql> select * from user;见鬼了,明明没有id为3的记录

事务1 执行查询发现不存在 id = 3 的记录,然后执行新增操作,结果却失败了。

可串行化(serializable)

可串行化(serializable)级别下不存在幻读的问题。对于上面的情况,其结果是 事务2 在执行了第6步后进入等待,需 事务1 执行完后才能继续,也就是串行执行。

mysql命令

  1. 查看系统变量

    1
    show global variables \G

    找到事务隔离级别变量,当前隔离级别为可重复读

    1
    2
    3
    *************************** 518. row ***************************
    Variable_name: transaction_isolation
    Value: REPEATABLE-READ
  2. 查看全局事务隔离级别

    1
    select @@global.transaction_isolation;

    输出结果

    1
    2
    3
    4
    5
    6
    +--------------------------------+
    | @@global.transaction_isolation |
    +--------------------------------+
    | REPEATABLE-READ |
    +--------------------------------+
    1 row in set (0.02 sec)
  3. 查看 session 事务隔离级别

    1
    select @@session.transaction_isolation;

    输出结果

    1
    2
    3
    4
    5
    6
    +---------------------------------+
    | @@session.transaction_isolation |
    +---------------------------------+
    | READ-UNCOMMITTED |
    +---------------------------------+
    1 row in set (0.00 sec)
  4. 设置事务隔离级别

    1
    set session transaction isolation level read uncommitted;

参考

https://segmentfault.com/a/1190000016566788

BaronScbwartz, PeterZaitsev, VadimTkacbenko, 等. 高性能 MySQL

0%