首先设置数据库隔离级别为读已提交(READ COMMITTED):
- set global transaction isolation level READ COMMITTED ;
- set session transaction isolation level READ COMMITTED ;
[READ COMMITTED]能解决的问题
我们来看一下为什么 [READ COMMITTED] 如何解决脏读的问题:
事务 1:
- START TRANSACTION;
- UPDATE users SET state=1 WHERE id=1;
- SELECT sleep(10);
- ROLLBACK;
事务 2:
- START TRANSACTION;
- SELECT * FROM users WHERE id=1;
- COMMIT;
事务 1 先于事务 2 执行
事务 1 的执行信息:
[SQL 1]START TRANSACTION;
受影响的行: 0
时间: 0.001s
- [SQL 2]
- UPDATE users SET state=1 WHERE id=1;
受影响的行: 1
时间: 0.001s
- [SQL 3]
- SELECT sleep(10);
受影响的行: 0
时间: 10.000s
- [SQL 4]
- ROLLBACK;
受影响的行: 0
时间: 0.051s
事务 2 的执行信息:
[SQL 1]START TRANSACTION;
受影响的行: 0
时间: 0.001s
- [SQL 2]
- SELECT * FROM users WHERE id=1;
受影响的行: 0
时间: 0.005s
[SQL 3]
COMMIT;
受影响的行: 0
时间: 0.001s
最终结果:
结论:
读已提交 [READ COMMITTED] 隔离级别可以解决脏读的问题, 但是貌似不是按照二级封锁协议解决的脏读问题
分析:
因为读已提交 [READ COMMITTED] 隔离级别对应数据库的二级封锁协议二级封锁协议在修改数据之前对其加 X 锁, 直到事务结束释放 X 锁读数据之前必须加 S 锁, 读完即可释放 S 锁因为事务 1 先执行修改, 修改前申请持有 X 锁, 事务结束释放 X 锁持锁时间段为 [SQL 2] 开始前到 [SQL 4] 结束, 持锁时间大约为 10.056s 事务 2 在事务 1 之后进行读操作, 按照二级封锁协议所说, 事务 2 在读数据之前会申请持有 S 锁但是事务 1 持有此数据的 X 锁, 所以事务 2 必须等待事务 1 释放 X 锁, 这个过程大约在 10 秒左右但是我们通过事务 2 的执行信息可以看到执行查询的时间为 0.005s, 远远小于 10 秒所以我们可以大胆推断 Mysql 的 InnoDB 引擎在 [READ COMMITTED] 隔离级别下对读操作没有加锁但是 [READ COMMITTED] 隔离级别确实解决了脏读的问题, 那么 Mysql 是怎么解决的脏读问题呢?
MVCC(多版本并发控制)
答案是多版本并发控制(MVCC), 可以认为是行级锁的一个变种, 但是它在很多情况下都避免了加锁操作, 因此开销更低实现了非堵塞的读操作, 写操作也只需要锁定必要的行
如果我们理解了 MVCC 的工作机制, 也就可以理解 [READ COMMITTED] 隔离级别是如何解决脏读问题的
MVCC 具体是如下操作的:
SELECT
InnoDB 会根据以下两个条件检查记录:
InnoDB 只会查找版本早于当前事务版本的数据行(也就是, 行的版本号小于或是等于事务的系统版本 号), 这样可以确保数据读取的行, 要么是在事务开始前已经存在的, 要么是事务自身插入或修改过的
行的删除版本号要么未定义, 要么大于当前事务版本号这可以确保事务读取到的行, 在事务开始之前未被删除
只有符合上述两个条件的记录, 才能返回作为查询结果
INSERT
InnoDB 为新插入的每一行保存当前系统版本号作为行版本号
DELETE
InnoDB 为删除的每一行保存当前系统版本号作为行删除标识
UPDATE
InnoDB 为新插入的每一行保存当前系统版本号作为行版本号, 同时保存当前系统版本号到原来的行作为行删除标识
Innodb 为每行记录都实现了三个隐藏字段:
6 字节的事务 ID(DB_TRX_ID)
7 字节的回滚指针(DB_ROLL_PTR)
隐藏的 ID 6 字节的事物 ID 用来标识该行所述的事务
事务 1 会执行如下操作:
用排他锁锁定该行
记录 redo log
把该行修改前的值 Copy 到 undo log, 即上图中下面的行
修改当前行的值, 填写事务编号, 使回滚指针指向 undo log 中的修改前的行
如果事务 1 最后执行 COMMIT 操作, 则什么操作都不用做如果执行 ROLLBACK 操作, 则需要通过回滚指针从 undo log 中还原修改前的数据
read view 判断当前版本数据项是否可见
在 InnoDB 中, 创建一个新事务的时候, InnoDB 会将当前系统中的活跃事务列表 (trx_sys->trx_list) 创建一个副本(read view), 副本中保存的是系统当前不应该被本事务看到的其他事务 id 列表当用户在这个事务中要读取该行记录的时候, InnoDB 会将该行当前的版本号与该 read view 进行比较
具体的算法如下:
设该行的当前事务 id 为 trx_id,read view 中最早的事务 id 为 trx_id_min, 最迟的事务 id 为 trx_id_max
如果 trx_id<trx_id_min 的话, 那么表明该行记录所在的事务已经在本次新事务创建之前就提交了, 所以该行记录的当前值是可见的
如果 trx_id>trx_id_max 的话, 那么表明该行记录所在的事务在本次新事务创建之后才开启, 所以该行记录的当前值不可见
如果 trx_id_min <= trx_id <= trx_id_max, 那么表明该行记录所在事务在本次新事务创建的时候处于活动状态, 从 trx_id_min 到 trx_id_max 进行遍历, 如果 trx_id 等于他们之中的某个事务 id 的话, 那么不可见
从该行记录的 DB_ROLL_PTR 指针所指向的回滚段中取出最新的 undo-log 的版本号的数据, 将该可见行的值返回
需要注意的是, 新建事务 (当前事务) 与正在内存中 commit 的事务不在活跃事务链表中
对应源代码如下:
函数: read_view_sees_trx_id
read_view 中保存了当前全局的事务的范围:
low_limit_id, up_limit_id
1. 当行记录的事务 ID 小于当前系统的最小活动 id, 就是可见的
- if (trx_id <view->up_limit_id) {
- return(TRUE);
- }
2. 当行记录的事务 ID 大于当前系统的最大活动 id(也就是尚未分配的下一个事务的 id), 就是不可见的
- if (trx_id>= view->low_limit_id) {
- return(FALSE);
- }
3. 当行记录的事务 ID 在活动范围之中时, 判断是否在活动链表中, 如果在就不可见, 如果不在就是可见的
- for (i = 0; i <n_ids; i++) {
- trx_id_t view_trx_id
- = read_view_get_nth_trx_id(view, n_ids - i - 1);
- if (trx_id <= view_trx_id) {
- return(trx_id != view_trx_id);
- }
- }
事务 2 会执行如下操作:
理想状态下, 事务 1 的事务 id=1, 事务 2 的事务 id=2 因为事务 2 执行时查询时, 事务 1 正处于等待状态所以 read view 为{1}, 事务 2 读取的数据行 trx_id=1,read view 中最早的事务 id 为 trx_id_min=1, 最迟的事务 id 为 trx_id_max=1 因为 trx_id_min <= trx_id <= trx_id_max, 并且 trx_id_min = trx_id = trx_id_max, 说明该行记录所在事务在本次新事务创建的时候处于活动状态, 不可见所以从该行记录的 DB_ROLL_PTR 指针所指向的回滚段中取出最新的 undo-log 的版本号的数据, 将该可见行的值返回所以不会出现脏读的现象
[READ COMMITTED]不能解决的问题
[READ COMMITTED]隔离级别解决不了不可重复读的问题, 一个事务中两次读取可能会出现不同的结果
我们来模拟一下:
事务 1:
- START TRANSACTION;
- SELECT sleep(5);
- UPDATE users SET state=1 WHERE id=1;
- COMMIT;
事务 2:
- START TRANSACTION;
- SELECT * FROM users WHERE id=1;
- SELECT sleep(10);
- SELECT * FROM users WHERE id=1;
- COMMIT;
事务 1 先于事务 2 执行
执行结果:
结论:
读已提交 [READ COMMITTED] 隔离级别不能解决不可重复读的问题, 但是如果按照上面所说, Mysql 的 InnoDB 引擎是通过 read view 来判断当前版本数据项是否可见的那么读已提交 [READ COMMITTED] 隔离级别下应该也不会出现不可重复读的问题, 但是现实并不是
分析:
读已提交 [READ COMMITTED] 隔离级别下出现不可重复读是由于 read view 的生成机制造成的在 [READ COMMITTED] 级别下, 只要当前语句执行前已经提交的数据都是可见的在每次语句执行的过程中, 都关闭 read view, 重新创建当前的一份 read view 这样就可以根据当前的全局事务链表创建 read view 的事务区间
那么在我们模拟的事务中, 事务 1 的事务 id trx_id1=1, 事务 2 的事务 id trx_id2=2 假设事务 2 第一次读取数据前的此行数据的事务 id=0 事务 2 中语句执行前生成的 read view 为{1},trx_id_min=1,trx_id_max=1 因为 trx_id(0)< trx_id_max(1), 此行数据对本次事务可见, 将该可见行的值 state=0 返回语句执行后等待 10 秒, 第 5 秒时事务 1 对数据加 X 锁进行修改操作 0->1, 然后提交事务释放锁语句执行前生成的 read view 为{null}, 说明当前系统中的不存在其他的活跃事务, 也就不存在不应该被本事务看到的其他事务, 因此该行记录的当前值 state=1 可见就出现两次读取数据不一致的问题, 也就是不可重复读
不可重复读的问题在 Mysql 默认的隔离级别 [REPEATABLE READ] 中得到了解决至于是如何解决的, 先卖个关子可以给个小提示, 也是和 read view 的生成机制有关预知后事如何
来源: https://www.cnblogs.com/songwenjie/p/8644646.html