MySQL事务隔离

原文《MySQL实战45讲》

前言

​ 注意：在 MySQL 中，事务支持是在引擎层实现的，而 MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务。本文以 InnoDB 为例，剖析 MySQL 在事务支持方面的特定实现。

事务特性

（ACID）原子性/一致性/隔离性/持久性

事务并发提交引发的问题

1. 脏读（dirty read）：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据；
2. 不可重复读（non-repeatable read）：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两 次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
3. 幻读（phantom read）： 是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。 同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。

事务隔离级别

1. 读未提交：一个失误还没提交时，它做的变更就能被别的事务看到。
2. 读已提交：一个事务提交之后，它做的变更才会被其他事务看到。
3. 可重复读：一个事务执行过程中看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
4. 串行化：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

事务隔离的配置方式

​ MySQL的数据库的默认隔离级别是“读已提交”，配置方式是：将启动参数 transaction-isolation 的值设置为 READ-COMMITTED 。可以用 show variables 来查看当前的值。

# 5.7版本后使用 transaction_isolation, 5.8版本后删除了tx_isolation
show variables like 'transaction_isolation';
# 5.6版本及以前使用 tx_isolation
show variables like 'tx_isolation';

​ 注意： Oracle 数据库的默认隔离级别其实就是”读已提交“，因此对于一些从 Oracle 迁移到 MySQL 的应用，为保证数据库的隔离级别一致，要记得修改 MySQL 的默认隔离级别配置。

MySQL 中 InnoDB 存储引擎事务隔离的实现

1. 在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。

   1. 在"可重复读"隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。
   2. 在“读已提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。
   3. 在“读未提交”隔离级别下，直接返回记录上的最新值，没有视图概念。
   4. 在“串行化”隔离级别下，直接用枷锁的方式来避免并行访问。
   5. 对于”可重复读“，在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上吗的最新之，通过回滚操作，都可以得到前一个状态的值。对于同一条记录，在不同时刻启动的事务会有不同的 read-view 。如下图所示，在同一个事务里面，由于对于这条记录有多个更新操作，这条数据也会被保存在同个事务里面的多个 read-view 中。同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。
   ​ img

2. 回滚日志的处理

   1. 回滚日志什么时候会删除呢？当系统没有事务再需要用到这些回滚日志了，回滚日志就会被删除。什么时候才不需要呢？就是当系统里没有比这个回滚日志更早的 read-view 的时候，也就是事务提交后。

   2. 为什么尽量不要使用长事务？长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交这钱，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

      # 在 information_schema 库的 innodb_trx 这个表中可以查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务。
      select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;
      

   3. 在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。

事务启动方式

1. 显式启动事务语句，begin或者start transaction,提交commit，回滚rollback。

2. set autocommit=0，该命令会把这个线程的自动提交关掉。这样只要执行一个select语句，事务就启动，并不会自动提交，直到主动执行commit或rollback或断开连接。

   建议：使用 set autocommit=1, 通过显式语句的方式来启动事务。如果纠结由于显示是启动事务，每次交互都需要多执行一次“begin”，那可以使用 commit work and chain 语法。在 autocommit = 1 时，用 begin 显示启动事务，如果执行 commit 则提交事务，如果执行 commit work and chain ，则是提交事务并自动启动下一个事务。这样，减少了交互次数，同时从程序开发的角度可以明确知道每个语句是否处于事务中。