01 mysql可重复读的几种情况的调试

编程入门行业动态更新时间:2024-10-12 16:21:14

01 mysql可重复读的<a href=https://www.elefans.com/category/jswz/34/1769370.html style= 几种情况的调试"/>

01 mysql可重复读的几种情况的调试

前言

呵呵最近突然想起了数据库相关的问题似乎是在一些场景下面问的比较多, 特别是事务的隔离级别, sql 的优化呀, 这些东西

于是最近就需要补一下这方面的知识了, 本文便是其中之一

不同的事务可重复读的处理, mysql 里面的是很经典的解决方案, 那么我们便来看一下吧

阅读本文之前, 最好可以先看一下参考的相关文章, 这样会更加轻松一些

看下文章 MySQL多版本并发控制机制(MVCC)-源码浅析是收货颇多的

环境说明

gif来自于(mac上面的软件录制gif太大了)

root@server:~# cat /proc/version
Linux version 4.4.0-138-generic (buildd@lcy01-amd64-006) (gcc version 5.4.0 20160609 (Ubuntu 5.4.0-6ubuntu1~16.04.10) ) #164-Ubuntu SMP Tue Oct 2 17:16:02 UTC 2018mysql> select version();
+-----------------------------+
| version()                   |
+-----------------------------+
| 5.7.24-0ubuntu0.16.04.1-log |
+-----------------------------+
1 row in set (0.00 sec)

测试的表结构

CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`age` int(11) DEFAULT 0,`name` varchar(64),PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 ;

表结构如上

-- 重新构造测试数据
delete from user;
insert into user values ('1', '27', 'jerry');

重置测试数据的 sql 如上

下文需要用到的相关代码如下

readOread.read_view_open_now_low : 创建 consistent view

readOread.read_view_sees_tx_id : 判断当前 view 是否可读给定的事务对应的记录

case01 tx1, 新增, tx2 查询, tx1 commit, tx2 查询

    # step 0 : 查询 select * from user where id = '1';事务版本号为 : 3849rec(id = 1) 的 trx_id 为 2823默认情况下(没有手动开启事务), 每一次查询都会创建 consistent read view# step 1 : 开启事务 1, 事务 2, 并且事务 1, 事务 2, 执行查询 select * from user where id = '1';事务 1 执行查询 : 创建 consistent read view, trx_id 为 3850view = {read_view_t * | 0x7f94b5f8b998} 0x00007f94b5f8b998type = {ulint} 1undo_no = {undo_no_t} 0low_limit_no = {trx_id_t} 3851low_limit_id = {trx_id_t} 3851up_limit_id = {trx_id_t} 3851n_trx_ids = {ulint} 0trx_ids = {trx_id_t * | 0x7f94b5f8b9e8} 0x00007f94b5f8b9e8creator_trx_id = {trx_id_t} 3850view_list = {ut_list_node<read_view_t>}prev = {read_view_t * | 0x0} NULLnext = {read_view_t * | 0x0} NULLrec(id = 1) 的 trx_id 为 2823, 对当前事务可见, 返回结果# 事务 2 执行查询 : 创建 consistent read view, trx_id 为 3851view = {read_view_t * | 0x7f94b8c58818} 0x00007f94b8c58818type = {ulint} 1undo_no = {undo_no_t} 0low_limit_no = {trx_id_t} 3852low_limit_id = {trx_id_t} 3852up_limit_id = {trx_id_t} 3850n_trx_ids = {ulint} 1trx_ids = {trx_id_t * | 0x7f94b8c58868} 0x00007f94b8c58868*trx_ids = {trx_id_t} 3850creator_trx_id = {trx_id_t} 3851view_list = {ut_list_node<read_view_t>}prev = {read_view_t * | 0x0} NULLnext = {read_view_t * | 0x00007f94b5f8b998} 0x00007f94b5f8b998rec(id = 1) 的 trx_id 为 2823, 对当前事务可见, 返回结果# step 2, 事务 1 再一次执行 select * from user where id = '1';view = {read_view_t * | 0x7f94b5f8b998} 0x00007f94b5f8b998type = {ulint} 1undo_no = {undo_no_t} 0low_limit_no = {trx_id_t} 3851low_limit_id = {trx_id_t} 3851up_limit_id = {trx_id_t} 3851n_trx_ids = {ulint} 0trx_ids = {trx_id_t * | 0x7f94b5f8b9e8} 0x00007f94b5f8b9e8creator_trx_id = {trx_id_t} 3850view_list = {ut_list_node<read_view_t>}prev = {read_view_t * | 0x0} NULLnext = {read_view_t * | 0x0} NULL可以看到这个视图 和 第一次查询的时候 一模一样, 因为 REPEATABLE-READ 的一次事务只会创建一个 consistent read view# step 3, 事务 1 执行 insert into user values ('2', '28', 'lucy');# step 4, 事务 1 执行 select * from user where id = '2';rec(id = 1) 的 trx_id 为 3850, 对当前事务可见, 返回结果# step 5, 事务 2 执行 select * from user where id = '2';rec(id = 1) 的 trx_id 为 3850, 对当前事务不可见, 不返回结果# step 6, 事务1 执行 commit;# step 7, 事务2 执行 select * from user where id = '2';因为 view.trx_ids 中依然记录了 3850, 经过 read_view_sees_trx_id 的判断, 还是看不见

case02 tx1, 更新, tx2 查询

    # step 0 : 查询 select * from user where id = '1';事务版本号为 : 3861rec(id = 1) 的 trx_id 为 3860# step 1, 开启事务 1, 事务 2, 并且事务 1, 事务 2, 执行查询 select * from user where id = '1';事务 1 执行查询 : 创建 consistent read view, trx_id 为 3862view = {read_view_t * | 0x7f94b5f8b998} 0x00007f94b5f8b998type = {ulint} 1undo_no = {undo_no_t} 0low_limit_no = {trx_id_t} 3863low_limit_id = {trx_id_t} 3863up_limit_id = {trx_id_t} 3863n_trx_ids = {ulint} 0trx_ids = {trx_id_t * | 0x7f94b5f8b9e8} 0x00007f94b5f8b9e8*trx_ids = {trx_id_t} 0creator_trx_id = {trx_id_t} 3862view_list = {ut_list_node<read_view_t>}prev = {read_view_t * | 0x0} NULLnext = {read_view_t * | 0x0} NULL事务 2 执行查询 : 创建 consistent read view, trx_id 为 3863view = {read_view_t * | 0x7f94b8c58818} 0x00007f94b8c58818type = {ulint} 1undo_no = {undo_no_t} 0low_limit_no = {trx_id_t} 3864low_limit_id = {trx_id_t} 3864up_limit_id = {trx_id_t} 3862n_trx_ids = {ulint} 1trx_ids = {trx_id_t * | 0x7f94b8c58868} 0x00007f94b8c58868*trx_ids = {trx_id_t} 3862creator_trx_id = {trx_id_t} 3863view_list = {ut_list_node<read_view_t>}prev = {read_view_t * | 0x0} NULLnext = {read_view_t * | 0x7f94b5f8b998} 0x7f94b5f8b998# step 2, tx 1 执行 update user set name = 'lucy' where id = '1';# step 3, tx 1 执行 select * from user where id = '1';rec(id = 1) 的 trx_id 为 3862, 对当前事务可见, 返回结果, 结果为 step 2 更新之后的结果# step 4, tx 2 执行 select * from user where id = '1';rec(id = 1) 的 trx_id 为 3862, 经过 read_view_sees_trx_id 判断, 对当前事务不可见将 rec(id = 1) 回退到上一个版本, 3860, 经过 read_view_sees_trx_id 判断, 对当前事务可见, 返回这个版本的结果# step 5, 事务1 执行 commit;# step 6, tx 2 执行 select * from user where id = '1';rec(id = 1) 的 trx_id 为 3862, 经过 read_view_sees_trx_id 判断, 对当前事务不可见将 rec(id = 1) 回退到上一个版本, 3860, 经过 read_view_sees_trx_id 判断, 对当前事务可见, 返回这个版本的结果

case03 tx1, 删除, tx2 查询

    # step 1, 开启事务 1, 事务 2, 并且事务 1, 事务 2, 执行查询 select * from user where id = '1';事务 1 执行查询 : 创建 consistent read view, trx_id 为 3876view = {read_view_t * | 0x7f94b5f8b998} 0x00007f94b5f8b998type = {ulint} 1undo_no = {undo_no_t} 0low_limit_no = {trx_id_t} 3877low_limit_id = {trx_id_t} 3877up_limit_id = {trx_id_t} 3877n_trx_ids = {ulint} 0trx_ids = {trx_id_t * | 0x7f94b5f8b9e8} 0x00007f94b5f8b9e8*trx_ids = {trx_id_t} 3863creator_trx_id = {trx_id_t} 3876view_list = {ut_list_node<read_view_t>}prev = {read_view_t * | 0x0} NULLnext = {read_view_t * | 0x0} NULLrec(id = 1) 的 trx_id 为 3873事务 2 执行查询 : 创建 consistent read view, trx_id 为 3877view = {read_view_t * | 0x7f94b8c58818} 0x00007f94b8c58818type = {ulint} 1undo_no = {undo_no_t} 0low_limit_no = {trx_id_t} 3878low_limit_id = {trx_id_t} 3878up_limit_id = {trx_id_t} 3876n_trx_ids = {ulint} 1trx_ids = {trx_id_t * | 0x7f94b8c58868} 0x00007f94b8c58868*trx_ids = {trx_id_t} 3876creator_trx_id = {trx_id_t} 3877view_list = {ut_list_node<read_view_t>}prev = {read_view_t * | 0x0} NULLnext = {read_view_t * | 0x00007f94b5f8b998} 0x00007f94b5f8b998# step 2, 事务 1 执行, delete from user where id = '1';# step 3, 事务 1 执行, select * from user where id = '1';rec(id = 1) 的 trx_id 为 3876, 对于当前事务 可见, 返回的结果为 Empty set (2 min 5.65 sec)(标记删除)# step 4, 事务 2 执行, select * from user where id = '1';rec(id = 1) 的 trx_id 为 3876, 对于当前事务 不可见将 rec(id = 1) 回退到上一个版本, 3873, 经过 read_view_sees_trx_id 判断, 对当前事务可见, 返回这个版本的结果# step 5, 事务1 执行 commit;# step 6, tx 2 执行 select * from user where id = '1';rec(id = 1) 的 trx_id 为 3876, 对于当前事务 不可见将 rec(id = 1) 回退到上一个版本, 3873, 经过 read_view_sees_trx_id 判断, 对当前事务可见, 返回这个版本的结果

case04 tx1, 新增, tx2 更新(经典的幻读场景)

    # step 1, 开启事务 1, 事务 2, 并且事务 1, 事务 2, 执行查询 select * from user where id = '1';事务 1 执行查询 : 创建 consistent read view, trx_id 为 7522view = {read_view_t * | 0x7fcbef7b5ca8} 0x00007fcbef7b5ca8type = {ulint} 1undo_no = {undo_no_t} 0low_limit_no = {trx_id_t} 7523low_limit_id = {trx_id_t} 7523up_limit_id = {trx_id_t} 7523n_trx_ids = {ulint} 0trx_ids = {trx_id_t * | 0x7fcbef7b5cf8} 0x00007fcbef7b5cf8*trx_ids = {trx_id_t} 7497creator_trx_id = {trx_id_t} 7522view_list = {ut_list_node<read_view_t>}prev = {read_view_t * | 0x0} NULLnext = {read_view_t * | 0x0} NULLrec(id = 1) 的 trx_id 为 7515事务 2 执行查询 : 创建 consistent read view, trx_id 为 7523view = {read_view_t * | 0x7fcbef04ce48} 0x00007fcbef04ce48type = {ulint} 1undo_no = {undo_no_t} 0low_limit_no = {trx_id_t} 7524low_limit_id = {trx_id_t} 7524up_limit_id = {trx_id_t} 7522n_trx_ids = {ulint} 1trx_ids = {trx_id_t * | 0x7fcbef04ce98} 0x00007fcbef04ce98*trx_ids = {trx_id_t} 7522creator_trx_id = {trx_id_t} 7523view_list = {ut_list_node<read_view_t>}prev = {read_view_t * | 0x0} NULLnext = {read_view_t * | 0x7fcbef7b5ca8} 0x00007fcbef7b5ca8# step 2, tx 1 执行 insert into user values ('2', '28', 'lucy');# step 3, tx 1 执行 select * from user;rec(id = 1) 的 trx_id 为 7515, 对 tx1 可见rec(id = 2) 的 trx_id 为 7522, 对 tx1 可见# step 4, tx 2 执行 select * from user;rec(id = 1) 的 trx_id 为 7515, 对 tx2 可见rec(id = 2) 的 trx_id 为 7522, 对 tx2 不可见# step 5, tx 1 执行 commit ;(先执行 commit, 再执行 update, 不然会造成 Lock wait timeout exceeded;)# step 6, tx 2 执行 update user set name = 'memcpy';从日志中可以看到 更新了两条记录Query OK, 2 rows affected (3.87 sec)Rows matched: 2  Changed: 2  Warnings: 0# step7, tx2 执行 select * from user;rec(id = 1) 的 trx_id 为 7523, 对 tx2 可见rec(id = 2) 的 trx_id 为 7523, 对 tx2 可见结果为 更新之后的数据

一下内容引用自 MySQL多版本并发控制机制(MVCC)-源码浅析

MySQL是通过MVCC和二阶段锁(2PL)来兼顾性能和一致性的，但是由于MySQL仅仅在select时候才创建一致性视图，而在update等加锁操作的时候并不做如此操作，所以就会产生一些诡异的现象。如下图所示:

如果理解了update不走一致性视图(read_view)，而select走一致性视图(read_view)，就可以很好解释这个现象。如下图所示:

case05 tx1, 更新, tx2 新增(经典的幻读场景)

    # step 1, 开启事务 1, 事务 2, 并且事务 1, 事务 2, 执行查询 select * from user where id = '1';事务 1 执行查询 : 创建 consistent read view, trx_id 为 7549view = {read_view_t * | 0x7fcbef7b5ca8} 0x00007fcbef7b5ca8type = {ulint} 1undo_no = {undo_no_t} 0low_limit_no = {trx_id_t} 7550low_limit_id = {trx_id_t} 7550up_limit_id = {trx_id_t} 7550n_trx_ids = {ulint} 0trx_ids = {trx_id_t * | 0x7fcbef7b5cf8} 0x00007fcbef7b5cf8*trx_ids = {trx_id_t} 7497creator_trx_id = {trx_id_t} 7549view_list = {ut_list_node<read_view_t>}prev = {read_view_t * | 0x0} NULLnext = {read_view_t * | 0x0} NULLrec(id = 1) 的 trx_id 为 7542事务 2 执行查询 : 创建 consistent read view, trx_id 为 7550view = {read_view_t * | 0x7fcbef04ce48} 0x00007fcbef04ce48type = {ulint} 1undo_no = {undo_no_t} 0low_limit_no = {trx_id_t} 7551low_limit_id = {trx_id_t} 7551up_limit_id = {trx_id_t} 7549n_trx_ids = {ulint} 1trx_ids = {trx_id_t * | 0x7fcbef04ce98} 0x00007fcbef04ce98*trx_ids = {trx_id_t} 7549creator_trx_id = {trx_id_t} 7550view_list = {ut_list_node<read_view_t>}prev = {read_view_t * | 0x0} NULLnext = {read_view_t * | 0x7fcbef7b5ca8} 0x00007fcbef7b5ca8# step 2, 事务1 执行 update user set name = 'memcpy';Query OK, 1 row affected (8.84 sec)Rows matched: 1  Changed: 1  Warnings: 0# step 3, 事务1 执行 select * from user;rec(id = 1) 的 trx_id 为 7549, 对 tx1 可见# step 4, 事务2 执行 select * from user;rec(id = 1) 的 trx_id 为 7549, 对 tx2 不可见将 rec(id = 1) 回退到上一个版本, 7542, 经过 read_view_sees_trx_id 判断, 对当前事务可见, 返回这个版本的结果# step 5, 事务1 执行 commit (先执行 commit, 再执行 insert, 不然会造成 Lock wait timeout exceeded;)# step 6, 事务2 执行 insert into user values ('2', '28', 'lucy');rec(id = 1) 的 trx_id 为 7549, 对 tx2 不可见将 rec(id = 1) 回退到上一个版本, 7542, 经过 read_view_sees_trx_id 判断, 对当前事务可见, 返回这个版本的结果rec(id = 2) 的 trx_id 为 7550, 对 tx2 可见# step 7, 窗口1 执行 select * from user;新创建了一个 consistent read view, trx_id 为 7558rec(id = 1) 的 trx_id 为 7549, 对 当前事务 可见rec(id = 2) 的 trx_id 为 7550, 对 当前事务 可见mysql> select * from user;+----+------+--------+| id | age  | name   |+----+------+--------+|  1 |   27 | memcpy ||  2 |   28 | lucy   |+----+------+--------+

以上, 调试截取了一部分运行时数据来分析可重复读的几种经典的场景

完