解析MySQL事务隔离级别

事务隔离是分布式系统对一致性保证的重要机制,是保证ACID的重要基础设施。MySQL InnoDB事务隔离级别官方说明中对MySQL的事务隔离机制有详细介绍。

ANSI SQL STANDARD不同隔离级别对应问题

结合示例,本文对事务隔离级别相关术语进行解析。

MySQL四类事务隔离级别

事务隔离级别从高到低为:

  • READ UNCOMMITTED:未提交读
    • 读取未提交内容,所有事务可看到其他未提交事务的结果,很少实际使用
    • 读取未提交的数据称为脏读(Dirty Read)
  • READ COMMITTED:提交读
    • 多数数据库的默认隔离级别(MySQL默认不是,默认为REPEATABLE-READ)
    • 满足隔离的简单定义:一个事务只能看到已提交事务所做的改变
    • 这种隔离级别,支持所谓的不可重读(Non-repeatable Read),同一事务的其他实例在该实例过程中可能有新commit,所以同一个select可能返回不同结果(同一个事务如何做到其他实例?
  • REPEATABLE READ:重复读
    • 可重复读(MySQL默认事务隔离),但可能出现幻读(Phantom Read)
    • 幻读(Phantom Read):当用户读取某范围数据行时,另一事务在此范围内插入新行,当用户再次读取此范围数据行时,读取到新的幻影行
    • InnoDB通过多版本并发控制MVCC机制解决该问题
  • SERIALIZABLE:串行化
    • 最高隔离级别,强制事务排序(串行化),不会互相冲突
    • 每个读数据航增加共享锁
    • 此级别,可能导致大量超时现象和锁竞争

按事务隔离级别来说,级别越低数据一致性保障效果越差,而并发能力则越强。(一致性VS并发性是天然矛盾体)

脏读、不可重复读、幻读

上述四种隔离级别采用不同的锁实现,对应级别下可能发生问题:

  • 脏读(Dirty Read):某事务已更新一份数据,而另一个事务此时读取了同一份数据,某些原因前一个更新做了回滚Rollback操作,则后一个事务数据是不正确的(读到了脏的数据)
  • 不可重复读(Non-repeatable read):在一个事务的两次查询中数据不一致,可能是两次查询过程中另一个事务更新了数据。
  • 幻读(Phantom Read):一个事务的两次查询中数据不一致。例如一个事务查询数据,而另一个事务却插入新的数据,先前事务的查询中,发现一些数据是之前查询中没有的

注意:根据ANSI SQL Standard在可重复读级别下允许出现幻读,MySQL实现满足标准,这不是Bug。但PostgreSQL在可重复读时不会出现幻读问题,这是不同引擎实现机制上的差异。

隔离级别 脏读 不可重读 幻读
读未提交(Read Uncommitted) yes yes yes
读已提交(Read Committed) no yes yes
可重复读(Repeatable Read) no no yes
可串行化(Searializable) no no no

示例1:脏读

1
2
3
4
5
6
7
8
9
10
11
-- 会话1
SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted; -- 设置会话隔离级别为 未提交读
start transaction;
select * from xxx; -- 此时为空,事务未提交

-- 会话2
start transaction;
insert into xxx values(1); -- 未提交事务中,插入数据

-- 会话1
select * from xx; -- 读取到会话2中未提交数据,这被称为 脏读

脏读(Dirty Read)是对一致性有要求的情况下无法接受的,所有未提交读在实际应用场景中几乎很少使用。

示例2:不可重复读

1
2
3
4
5
6
7
8
9
10
11
12
-- 会话1中操作:
start transaction;
select * from xxx where id=1; -- 此时数据状态为a
-- 注意此会话中开启事务,未提交

-- 切换至会话2操作
update xxx set xxx=newValue where id=1; -- 更新数据至新的状态b

-- 再次切换至会话1操作
select * from xxx where id=1;
-- 此时查询出的数据状态就有两种选择:新状态b、老状态a
-- 所谓的连续读:同一个事务中的两次读操作,数据状态保持一致

结论:

  • read committed事务隔离级别下,切换会话1后读取到新的状态b,因为会话2中事务已提交。
  • repeatable read事务隔离级别下,切换会话1后仍读取原始状态a,这就是所谓的【可重复读】
操作顺序 会话1 会话2
1 start transaction;select * from xxx where id=1;
2 update xxx set xxx=newValue where id=1;
3 select * from xxx where id=1;
结果:read committed级别 newValue新状态数据(此时为不可重复读问题)
结果:repeatable read级别 原状态数据,满足重复读要求

示例3:幻读

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 会话1
start transaction;
select * from xxx;
-- 此时查询表为空,且事务未提交

-- 会话2
start transaction;
insert into xxx values(1); -- 新增一条记录
commit;

-- 会话1
select * from xxx;
-- 此时查询表仍为空,表示满足[可重复读]特性

update xxx set age=99 where id=1; -- 更新会话2中插入记录(此时会话1并不可见)
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
-- 更新1条记录,隐约感觉不安
select * from xxx;
-- 再次读取时,竟然读取到内容(前一次读取时为空,2次读取时读取到内容),出现`幻读`
commit;
操作顺序 会话1 会话2
1 start transaction;select * from xxx where id=1;–空表
2 insert into xxx values(1);
3 select * from xxx; – 新插入元素对会话1中查询不可见,满足可重复读
4 update xxx set age=99 where id=1;select * from xxx; – 新插入元素在会话1竟然可以被成功更新,再次读时读取到新内容,复现幻读问题
结果:repeatable read级别时 一个事务中,两次读操作,第二次读时发现了首次读时不存在的内容,这被称为幻读问题

附录:事务相关SQL命令

  • 设置自动提交

    1
    2
    3
    -- 取消autocommit
    set aucommit=0
    show variables like "%autocommit%";
  • 查看隔离级别

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    -- 查看隔离级别

    SELECT @@global.tx_isolation;
    SELECT @@session.tx_isolation;
    SELECT @@tx_isolation;
    -- 三个角度的隔离:全局、会话、事务隔离

    show VARIABLES like "%iso%";

    +---------------+-----------------+
    | Variable_name | Value           |
    +---------------+-----------------+
    | tx_isolation  | REPEATABLE-READ |
    +---------------+-----------------+


    show global variables like '%iso%';
    +---------------+-----------------+
    | Variable_name | Value           |
    +---------------+-----------------+
    | tx_isolation  | REPEATABLE-READ |
    +---------------+-----------------+
  • 设置事务隔离级别

    1
    2
    3
    4
    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;
  • 事务操作

1
2
3
4
5
6
7
8
9
10
11
12
-- 事务中一次读操作
start transaction;
SELECT * FROM text.tx;
commit;
 
-- 事务中回滚操作
start transaction;
SELECT * FROM text.tx;
update text.tx set num =10 where id = 1;
insert into text.tx(id,num) values(9,9);
rollback;
commit;

参考文献