MySQL阅读笔记——16.事务隔离级别

理论上实现事务 隔离性 需要在一个是事务提交后才能执行其他事务，多个事务需要排队执行，但是在MySQL中为了性能考虑，允许事务的并发执行，舍弃了一定的 隔离性

允许事务并行执行后可能会出现以下问题：

1. 脏写：一个事务 修改 了另一个事务未提交的数据并且成功提交（InnoDB通过事务行锁解决）

2. 脏读：一个事务 读取 到了另一个未提交事务修改过的数据（InnoDB通过MVCC，在一个事务每次select都会获取ReadView）

3. 不可重复读：一个事务两次 读取 同一个数据中间，另一个事务对该数据进行了 修改 ，则同一事务两次读取的到数据不一致（InnoDB通过事务MVCC，只在一个事务第一次select获取ReadView）

4. 幻读：一个事务根据条件范围查询一些记录，之后另一个事务在结果集的 间隙 补充了一些记录，则原事务再次会把这些 补充 的记录也查询到，造成同一事物相同查询条件，两次查询结果不一致（InnoDB可以修改最高隔离级别，将并行事务改成串行事务，但是性能较低一般不用；而是采取了间隙锁 应对幻读问题，在读取范围内数据时候对范围内的 间隙 加上）

隔离级别	含义	脏写	脏读	不可重复读	幻读
READ UNCOMMITTED	一个事务可以读取另一个未提交事务修改内容		✔	✔	✔
READ COMMITTED	一个事务只能读取另外一事务已提交事务（当前事务未结束，另一事务可以对当前事务读取数据修改并提交，造成当前事务多次读取数据不一致）			✔	✔
REPEATABLE READ（InnoDB默认）	一个事务中多次读取相同相同记录返回结果相同（即使事务未结束，另一事物对当前事务读取的数据修改并提交，但是另一事务的改动对当前事务不可见，即MVCC）				✔
SERIALIZABLE	一个事务执行过程中完全串行化，对读取和写入操作均加锁，其他事物在相同数据上进行操作只能阻塞排队

-- 查看当前事务隔离级别
show variables like 'transaction_isolation'
-- 设置事务隔离级别
set [global|session] transaction isolation level [REPEATABLE READ|READ COMMITTED|READ UNCOMMITTED|SERIALIZABLE]

16.1 MVCC原理（多版本并发控制）

MVCC（Multi-Version Concurrency Control），即多版本并发控制。具体是指在 READ UNCOMMITTED 和 REPEATABLE READ 隔离级别下对 版本链 的过程，对应生成 ReadView 时机不同，从而确定当前事务读取数据的可见性，事务开启时候可以通过start transaction with consistent snapshot直接生成ReadView（只能在 可重复读 隔离级别下使用，在 读已提交 下使用无效）

聚集索引包含 row_id 、trx_id、roll_point 三个隐藏列，其中只有 row_id 不是必须的

• trx_id：每次一个事务对聚集索引记录修改时候，都会把事务id赋值给该属性

• roll_point：每次对聚集索引记录修改时，都会把旧值写入undo日志，该隐藏列指向undo日志链

  记录每次更新都会将旧记录放入undo日志，算是记录的旧版本，随着更新次数增多，所有版本通过 roll_point 属性连接成一个链表称之为 版本链，其中头节点就是版本最新值，每个版本还包含生成版本的事务id，即：trx_id

可重复读 隔离级别下，在一个事务中更新列值都是先读后写 （当前读），即：其他事务更改列值，虽然在当前事务中不可见（一致性读），但是更新时候会读取最新值（当前读），如果另一事物为提交则当前事务阻塞等待，如果另一事务提交，则当前事务的更新操作读取最新值（当前读）

对于 READ UNCOMMITTED 隔离级别，可以读取另一未提交事务更新的记录，所以可以直接读取记录最新版本即可；而 SERIALIZABLE 隔离级别使用加锁方式访问记录，READ COMMITTED 和 REPEATABLE READ 都要保证读取到提交了事务的修改记录，区别就是能否一个事务中能否读取到另外一个事务修改记录并且提交后的最新版本，因此InnoDB设计者提出了 ReadView 概念：

1. m_ids：生成ReadView时当前系统中活跃事务id列表

2. min_trx_id：生成ReadView时当前系统中活跃事务中最小的事务id

3. max_trx_id：生成ReadView时系统应该分配给下一个事务的id

4. creator_trx_id：生成该ReadView的事务id

根据ReadView判断记录在该事务中是否可见规则

1. 如果访问记录的 trx_id 与ReadView中的 creator_trx_id 相同，则代表访问 自己修改过 的记录，对当前事务可见

2. 如果访问记录的 trx_id 小于ReadView中的 min_trx_id ，表明该记录在之前的版本中修改并且已经提交，该版本记录对当前事务可见

3. 如果访问记录的 trx_id 大于等于 ReadView中的 max_trx_id 相同，表明生成该版本的记录在当前事务生成ReadView之后才开启，所以该版本对当前事务不可见（即：对当前事务来说该版本属于未来版本）

4. 如果访问记录的 trx_id 在 ReadView中的 min_trx_id 和 max_trx_id 之间，表明是生成ReadView时候存在其他事务，需要再判断一下 trx_id 是不是存在 m_ids 列表中，如果在，说明创建ReadView时生成该版本的其他事务还是活跃的，则该版本不可以对当前事务访问；如果不存在，则说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问

READ COMMITTED 隔离级别下，事务中每一次select都会获取一次ReadView，而 REPEATABLE READ 隔离级别下，只会在事务的第一次select去获取ReadView，保证当前事务中的操作都是在开启事务时的“快照”

只有在事务第一次修改记录时候才会生成事务id，并且事务id是递增的

insert undo事务提交后就可以释放，update undo需要支持MVCC不能立即删除，随着系统运行最早生成的ReadView的事务不会再访问的某些undo日志以及被打了删除标记的记录，后台运行purge线程将它们删除

# 查找持续时间超过 60s 的事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

16.2 事务最佳实践

避免长事务影响：

开发者端：

1. 确认是否使用了 set autocommit=0（测试端通general_log确认）

2. 确认框架中是否有不必要的只读事务

3. 通过SET MAX_EXECUTION_TIME控制每个语句连接的最长时间

服务器端:

1. 测试阶段开启general_log，分析日志提前发现问题

2. 监控information_schema.Innodb_trx表，设置长事务阈值，超过则kill（Percona 的 pt-kill工具）

---