70 mysql 中事务的隔离级别

前言

mysql 隔离级别有四种 未提交读, 已提交读, 可重复度, 序列化执行

然后不同的隔离级别存在不同的问题 

未提交读存在 脏读, 不可重复度, 幻觉读 等问题

已提交读存在 不可重复度, 幻觉读 等问题

可重复读存在 幻觉读 等问题

序列化执行 没有以上问题

然后 我们这里 来调试一下 以上各个事务隔离级别中存在的对应的问题

 

 

未提交读 READ_UNCOMMITTED

脏读

执行序列如下 

tx1 : begin;
tx2 : begin;
tx1 : select * from tz_test_02 where id = 5;
tx2 : update tz_test_02 set field1 = 'field1_updated', field2 = 'field2_updated' where id = 5;
tx1 : select * from tz_test_02 where id = 5;
tx1 : commit;
tx2 : commit;

 

然后 我们这里核心关注的是 第三条语句, 和 第五条语句的执行结果

第三条语句的执行, 根据主键定位到对应的记录的位置, 获取记录 判断是否匹配条件

 

这个 row_search_mvcc 依次会遍历两次, 第一次定位到的是 id=5 的记录 

第二次是 继续向下迭代, 找到的下一条记录 id=10, 接着跳出 

 

第四条语句的执行, 标记删除原有的记录, 新增新的记录如下 

 

第五条语句的执行, 可以看到这里的 rec 即为上面 update 操作之后新增的 rec 

因此这个可以理解为一个 “无锁”状态的隔离级别

 

 

不可重复度

未提交读 存在脏读问题, 那么就一定存在 可重复度的问题

这里不再赘述, 调试这一过程 

事务1开始事务
事务1 查询 id 为 5 的记录, 发现记录为 field1 字段为 ”field1” 
事务2开始事务
事务2插入 id 为 5 的记录
事务2提交事务 
事务1 查询 id 为 5 的记录, 发现记录为 field1 字段为 ”field1_updated” 
事务1提交

 

 

幻觉读

未提交读 存在不可重复读问题, 那么就存在 幻觉读的问题

这里不再赘述, 调试这一过程 

事务1开始事务
事务1 查询 id 为 20 的记录, 发现不存在 
事务2开始事务
事务2插入 id 为 20 的记录
事务2提交事务 
事务1 查询 id 为 20 的记录, 发现存在 
事务1提交

 

 

已提交读 READ_COMMITTED

脏读

已提交读, 无脏读问题, 主要是基于 MVVC 来解决脏读的问题的

执行序列如下, 我们这里更加关心的是 id=5 的记录的更新, 更关注的是 第六七八条sql的执行, 按照常理来推断 tx1 会比 tx2 的事务号 小1, 在 INNODB_TRX 数据表中可以看到各个事务的详细信息 

tx1 : begin;
tx1 : update tz_test_02 set field1 = 'field1_dummy', field2 = 'field2_dummy' where id = 10;
tx1 : select * from tz_test_02 where id = 5;
tx2 : begin;
tx2 : select * from tz_test_02 where id = 5;
tx2 : update tz_test_02 set field1 = 'field1_updated', field2 = 'field2_updated' where id = 5;
tx1 : select * from tz_test_02 where id = 5;
tx2 : select * from tz_test_02 where id = 5;
tx2 : commit ;
tx1 : commit ;

 

第三条查询, rec 数据如下 

 

rec 中数据拆解如下 

 

第六条更新的处理如下, 新增记录为 insert_buf

 

第七条查询处理如下, 根据 主键的 id 这边定位到的记录为 上面 新增的更新之后的 记录信息

 

更新操作新增的记录信息如下, 从这里的事务号可以做一个大致的推导 

更新记录的事务是 tx2, 这里的事务编号为 44104, 因此 tx1 的事务编号为 44103

 

 

判断当前记录是否对目标 ReadView 可见的方式如下, 具体的各个字段的逻辑意义可以参考 MVCC::view_open

m_low_limit_id, m_low_limit_no 表示的是当前创建 ReadView 的时候事务系统最大的事务编号 

m_up_limit_id 表示的是创建 ReadView 的时候, 除去当前事务的写视图列表 中最小的事务编号 

m_ids 表示的是创建 ReadView 的时候, 事务系统中 除去当前事务的写视图列表 

m_creator_trx_id 表示的是当前 事务id

一个关键的地方就是, 如果当前事务未产生写操作, m_creator_trx_id 为 0, 并且 INNODB_TRX 中的 trx_id 的字段数据也不准确 

所以这里的判断标准为, 如果是小于活跃的最小写事务 或者 当前事务的更新, 是直接判断为可读取的 

如果是大于 创建 ReadView 的时候的事务系统的最大的事务编号, 不可读取 

如果 没有其他的活跃的写事务列表 则可以读取目标记录

如果 事务编号 是 其他的活跃写事务 更新的, 不可读 

 

对于我们这里的场景, 更新之后的记录 对于 事务1 来说, 不可读, 因为 该记录的更新事务存在于 m_ids 列表中 

对于事务2来说, 可读, 因为该记录的更新事务 就是当前事务

 

然后回到问题现场, 我们看一下 这里的两个事务的读写的情况, 首先看一下 tx1

m_low_limit_id, m_low_limit_no 为当前事务系统中最大的事务编号, 为 44105, 

m_up_limit_id 为 除去当前事务之外的其他写事务的最小的编号 44104

m_creator_trx_id, prebuilt->trx->id 表示当前事务的事务编号

m_ids 表示的是除去当前事务之外的其他的写事务, 这里仅仅有 tx2 编号为 44104

然后这里 rec 最新是被 tx2 更新的, 因此记录中的 trxId 为 44104, 根据上面 ReadView::changes_visiables 的相关约束, 可以知道这里 tx1 是读取不到这里最新的 rec 的 

因此这里 row_sel_build_prev_vers_for_mysql 是根据 undo log 向前回溯更老版本的记录信息

 

回溯之后的记录如下, 为下面的 old_vers, 其内容和更新之前的 rec 的记录内容一样 

然后 下面的时候更新 rec, prev_rec, 然后走 row_search_mvcc 之后的流程 

然后是 根据主键迭代下一条记录, id=10, 然后 在 row_search_mvcc 的流程中 匹配不上查询条件, 返回 DB_RECORD_NOT_FOUND 跳出循环 

 

old_vers 的内容剖析如下, trx_id 为 44101, 这个对于当前事务 来说是可见的 如果是小于活跃的最小写事务 或者 当前事务的更新, 是直接判断为可读取的 

 

然后回到问题现场, 我们看一下 这里的两个事务的读写的情况, 首先看一下 tx2

m_low_limit_id, m_low_limit_no 为当前事务系统中最大的事务编号, 为 44105, 

m_up_limit_id 为 除去当前事务之外的其他写事务的最小的编号 44103

m_creator_trx_id, prebuilt->trx->id 表示当前事务的事务编号

m_ids 表示的是除去当前事务之外的其他的写事务, 这里仅仅有 tx2 编号为 44103

然后这里 rec 最新是被 tx2 更新的, 因此记录中的 trxId 为 44104, 根据上面 ReadView::changes_visiables 的相关约束, 可以知道这里 tx2 可以读取到这里最新的 rec 的 

 

最新的记录内容拆解如下, trxId 为 44104 就是当前事务, 当前事务可读目标记录 

 

 

不可重复读

已提交读, 无脏读问题, 但是还是存在重复读的问题, 意思就是在 同一个事务中, 未更新某记录 但是多次查询该记录 得到的状态不相同

执行序列如下, 我们这里更加关心的是 id=5 的记录的更新, 更关注的是 第六八九条sql的执行, 按照常理来推断 tx1 会比 tx2 的事务号 小1, 在 INNODB_TRX 数据表中可以看到各个事务的详细信息 

tx1 : begin;
tx1 : update tz_test_02 set field1 = 'field1_dummy', field2 = 'field2_dummy' where id = 10;
tx1 : select * from tz_test_02 where id = 5;
tx2 : begin;
tx2 : select * from tz_test_02 where id = 5;
tx2 : update tz_test_02 set field1 = 'field1_updated', field2 = 'field2_updated' where id = 5;
tx2 : commit ;
tx1 : select * from tz_test_02 where id = 5;
tx2 : select * from tz_test_02 where id = 5;
tx1 : commit ;

 

我们这里着重关注 第八条 这里的查询, 因为前面的处理 和上面脏读的 case 都是一样的

m_low_limit_id, m_low_limit_no 为当前事务系统中最大的事务编号, 为 44106, 

m_up_limit_id 为 除去当前事务之外的其他写事务的最小的编号 NULL, 默认取最大的事务编号

m_creator_trx_id, prebuilt->trx->id 表示当前事务的事务编号

m_ids 表示的是除去当前事务之外的其他的写事务, 这里为空, 因为 tx2 已经提交了

然后这里 rec 最新是被 tx2 更新的, 因此记录中的 trxId 为 44104, 根据上面 ReadView::changes_visiables 的相关约束, 可以知道这里 tx2 可以读取到这里最新的 rec 的 

 

 

幻觉读

已提交读 存在不可重复读问题, 那么就存在 幻觉读的问题

这里不再赘述, 调试这一过程 

事务1开始事务

事务1 查询 id 为 20 的记录, 发现不存在 

事务2开始事务

事务2插入 id 为 20 的记录

事务2提交事务

事务1 查询 id 为 20 的记录, 发现存在 

事务1提交

 

 

可重复读 REPEATABLE_READ

脏读

可重复读, 无脏读问题, 主要是基于 MVVC 来解决脏读的问题的

执行序列如下, 和上面已提交读解决脏读问题一样, 这里不再赘述

tx1 : begin;
tx1 : update tz_test_02 set field1 = 'field1_dummy', field2 = 'field2_dummy' where id = 10;
tx1 : select * from tz_test_02 where id = 5;
tx2 : begin;
tx2 : select * from tz_test_02 where id = 5;
tx2 : update tz_test_02 set field1 = 'field1_updated', field2 = 'field2_updated' where id = 5;
tx1 : select * from tz_test_02 where id = 5;
tx2 : select * from tz_test_02 where id = 5;
tx2 : commit ;
tx1 : commit ;

 

 

不可重复读

这个主要也是基于 MVVC 的处理, 和上面 已提交读 的差异主要是在于 ReadView 的生命周期 

已提交读 中 ReadView 这边每一次查询, 都会重新创建 ReadView, 每一次 MVCC::view_open 的时候 会从 trx_sys 中获取最新的 m_low_limit_no, m_low_limit_id, m_ids, m_up_limit_id

然后 只要有 写事务tx2 提交了, 然后 tx1 这边下一次查询的时候创建最新的 ReadView, 获取到的就是 m_low_limit_no, m_low_limit_id, m_up_limit_id 为 trx_sys->max_trx_id, 然后 m_ids 列表为空 

因此当 tx2 这边提交了事务之后, tx1 就可以读取到 tx2 的改动了 

 

然后 可重复读这边 ReadView 这边的生命周期相对较长, 是延迟到了 事务结束

因此 在该事务生命周期能够看到的数据是固定的, 一方面是创建事务的时候的快照, 另一方面是当前事务的相关调整 

 

已提交读这边每一次执行对于 ReadView 的关闭的处理, 可以看到的是 未提交读 和 已提交读 这边每一次 optimize 完成之后会关闭 ReadView

 

 

然后 我们来看拿一下 可重复读 这边对于 可重复读 的问题的处理, 就是保存了一个 ReadView 的快照 

sql 执行序列如下 

tx1 : begin;
tx1 : update tz_test_02 set field1 = 'field1_dummy', field2 = 'field2_dummy' where id = 10;
tx1 : select * from tz_test_02 where id = 5;
tx2 : begin;
tx2 : select * from tz_test_02 where id = 5;
tx2 : update tz_test_02 set field1 = 'field1_updated', field2 = 'field2_updated' where id = 5;
tx2 : commit ;
tx1 : select * from tz_test_02 where id = 5;
tx2 : select * from tz_test_02 where id = 5;
tx1 : commit ;

 

我们这里关注的是 第三个查询, 第六个更新, 第八个查询 

这里第三个查询的时候, 创建的 ReadView 的快照 

m_low_limit_id, m_low_limit_no 为当前事务系统中最大的事务编号, 为 44107

m_up_limit_id 为 除去当前事务之外的其他写事务的最小的编号 NULL, 默认取最大的事务编号 44107

m_creator_trx_id, prebuilt->trx->id 表示当前事务的事务编号

m_ids 表示的是除去当前事务之外的其他的写事务, 这里为空, 因为 tx2 尚未开始

 

第六个更新的使用, 新增的记录信息如下, insert_buf 则是新的记录的数据 

然后 rec 中是更新之后的数据, 从上下文 或者 上面的 tx1 的事务号 可以推导出 tx2 的事务号为 47107

 

这里可以看出新的记录事务编号为 47107, 这就是当前事务的事务编号 

 

第八个查询如下, tx1 的 ReadView 如下, 可以看到的是 和创建的时候一样

然后当前 记录的 trxId 为 47017, 然后明显是对于当前事务不可见的, 然后 需要 查看之前的版本

至此就解释了 可重复读 这边的一个具体的实现, 在事务开始的查询的地方创建的 ReadView, 而后 事务结束进行 close 

 

然后事务 commit 的时候, 关闭当前事务关联的 ReadView

 

 

幻觉读

在可重复读中构造 幻觉读的场景主要是基于了 mysql, select for update, select lock in share mode, insert, update, delete 是基于当前读的, 普通的无锁 select 是基于快照读 

执行序列如下

tx1 : begin;
tx1 : select * from tz_test_02;
tx2 : begin;
tx2 : INSERT INTO `test_02`.`tz_test_02`(`id`, `field1`, `field2`) VALUES (25, 'field25', '25');
tx2 : commit ;
tx1 : update tz_test_02 set field1 = 'field1_phantom', field2 = 'phantom' where id = 25;
tx1 : select * from tz_test_02;
tx1 : commit ;

 

我们这里核心关注的是 第四行的 insert, 和 第六行的 update 和 第七行的 select 

update 需要在 commit 之后, 因为 tx2 插入记录的时候在记录上面有一个 插入意向锁, tx1 update 的时候, mysql 这边会将插入意向锁升级为 行排他锁, 然后 tx2 获取 行排他锁 失败, 需要阻塞等待 

新增元素的时候, 会在元素上面增加一个 隐式锁, 但是 添加的地方 我这边没有找到

 

第六行的 update, 将目标记录的 trxId 更新为 tx1 的事务编号 

 

更新之后的记录信息如下, 事务编号为 47277

 

row_search_mvcc 的时候查询到目标记录, trxId 为 42477, 然后就是当前事务的更新

当前 ReadView 可读, 然后将数据 响应回去

 

响应结果如下 

 

 

串行化 SERIALIZED

所有的读操作增加读锁, 所有的写操作增加写锁, 从而保证每一条语句的执行都在一个安全的上下文 

执行序列如下, 比如 tx1 这边执行了 “select * from tz_test_02;” 之后, 会在查询的所有行上面增加 行共享锁 

然后 tx2 上面执行 “INSERT INTO” 会尝试获取 supremum 记录的行排他锁, 获取失败, 然后 阻塞等待 tx1 释放持有的锁 

tx1 : begin;
tx1 : select * from tz_test_02;
tx2 : begin;
tx2 : INSERT INTO `test_02`.`tz_test_02`(`id`, `field1`, `field2`) VALUES (25, 'field25', '25');
tx2 : commit ;
tx1 : update tz_test_02 set field1 = 'field1_phantom', field2 = 'phantom' where id = 25;
tx1 : select * from tz_test_02;
tx1 : commit ;

 

 

事务2 增加隐式锁, 事务1获取锁升级的流程

在上面 可重复读 的 幻觉读 的示例中, 我们将 tx1 的更新提前到 tx2 提交之前, 此时 tx1 的更新会阻塞住, 我们这里看一下 这个流程

执行序列如下

tx1 : begin;
tx1 : select * from tz_test_02;
tx2 : begin;
tx2 : INSERT INTO `test_02`.`tz_test_02`(`id`, `field1`, `field2`) VALUES (25, 'field25', '25');
tx1 : update tz_test_02 set field1 = 'field1_phantom', field2 = 'phantom' where id = 25;
tx2 : commit ;
tx1 : select * from tz_test_02;
tx1 : commit ;

 

第四句 sql 插入记录, 新增元素的时候, 会在元素上面增加一个 隐式锁, 但是 添加的地方 我这边没有找到

 

第五句 update, 导致的 隐式锁 升级为 显式锁

从上下文可以知道, 这里是升级成为了一个 行排他锁

 

这里当前 update 操作, 也是申请 该记录的行排他锁, 该行排他锁已经被 tx2 持有, 会被 tx2 阻塞 

 

 

完