MySQL-理解事务隔离级别

发表于 | 分类于

在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见，较低的隔离级别通常可以执行更高的并发，系统的开销也更低

事务的ACID

  • 原子性（atomicity）
    一个事务必须视为一个不可分割的最小工作单元，整个事务的所有操作要么全部提交成功，要么全部回滚

  • 一致性（consistency）
    数据库总是从一个一致性状态转换到另一个一致性状态。

  • 隔离性（isolation）
    通常来说，一个事务所作的修改在最终提交以前，对其他事务是不可见的，在讨论隔离级别的时候，会理解为什么说是 “通常来说” 是不可见的

  • 持久性（durability）
    一旦事务提交，则所做的修改就会永久的保存到数据库中

    隔离级别

    1.READ UNCOMMITTED （未提交读）
  • 即在该级别，事务中的对数据的修改，即使没有提交，对其他事务也是可见的。这种情况被称为脏读（Dirty Read），这个级别会导致很多问题，而且性能相比其他级别不会好太多，实际很少使用。
2.READ COMMITTED （提交读）
  • 大多数据库的默认隔离级别（但MySQL不是），该级别解决了脏读的问题。但是事务中会读到其他事务已提交的数据，无法保证读一致性，会造成不可重复读的问题。如下事务的两次读取是不一致的

3.REPEATABLE READ （可重复读）
  • 该隔离级别，解决了脏读，不可重复读。InnoDB 使用 MVCC（多版本并发控制下文会讲到） 保证了事务中的读一致性。但还是会出现一个幻读的情况
  • 如下图（左为事务A，右为事务B）
    事务执行这样的逻辑：查找是否有某条记录，如果不存在则新增。
  1. 事务A 查询是否存在 id="1"这条数据，发现不存在，准备执行insert
  2. 此时事务B insert了这条数据，并提交了事务。
  3. 事务A 执行insert 发生主键冲突，再次执行了 select * from where id = "1"，发现依旧不存在当前结果集

REPEATABLE READ

  • RR 级别下如何防止幻读
    1
    2
    # 用 X锁
    SELECT i` FROM `users` WHERE `id` = "1" FOR UPDATE;

如果 id = 1 的记录存在则会被加行（X）锁，如果不存在，则会加 next-lock key / gap 锁（范围行锁），即记录存在与否，mysql 都会对记录应该对应的索引加锁，其他事务是无法再获得做操作的。

REPEATABLE READ 解决幻读

4.SERIALIZABLE （串行化）

在该隔离级别下，读取的每一行都会被添加锁（个人理解是读锁和gap锁），导致大量的超时和锁争用问题，实际应用中很少使用，只有在非常需要确保数据一致性且可以接受没有并发的情况下，才考虑该级别。

  • 在 SERIALIZABLE 级别下再运行一下 上述的demo
  • 可以看到步骤2的 insert 被阻塞了，上述“幻读”的情况

SERIALIZABLE

MVCC

MVCC 是什么
  • MVVC (Multi-Version Concurrency Control, 多版本并发控制)，在InnoDB引擎下，MVCC是为了实现事务的隔离性，通过版本号，避免同一数据在不同事务间的竞争，你可以把它当成基于多版本号的一种乐观锁，读不加锁，读写不冲突
    MVCC 实现机制

  • InnoDB在每行数据都增加两个隐藏字段，一个记录创建的版本号，一个记录删除的版本号。

  • 在MVVC 中，为了保证数据操作在多线程过程中，保证事务隔离的机制，降低锁竞争的压力，保证较高的并发量。在每开启一个事务时，会生成一个事务的版本号，被操作的数据会生成一条新的数据行（临时），但是在提交前对其他事务是不可见的，对于数据的更新（包括增删改）操作成功，会将这个版本号更新到数据的行中，事务提交成功，将新的版本号更新到此数据行中，这样保证了每个事务操作的数据，都是互不影响的，也不存在锁的问题。

MVVC下的CRUD （REPEATABLE READ下）

  • SELECT：
    　　InnoDB 查询的每行数据必须满足以下两点
    　　1、InnoDB必须找到一个行的版本，它至少要和事务的版本一样老(即它的版本号不大于事务的版本号)。这保证了不管是事务开始之前，或者事务创建时，或者修改了这行数据的时候，这行数据是存在的。
    　　2、这行数据的删除版本必须是未定义的或者比事务版本要大。这可以保证在事务开始之前这行数据没有被删除。
    符合这两个条件的行可能会被当作查询结果而返回。

  • INSERT：
    　　InnoDB为这个新行记录当前的系统版本号。

  • DELETE：
    　　InnoDB将当前的系统版本号设置为这一行的删除ID。
  • UPDATE：
    　　InnoDB会写一个这行数据的新拷贝，这个拷贝的版本为当前的系统版本号。它同时也会将这个版本号写到旧行的删除版本里。

参考