Mysql之MVCC

站长

2023年04月01日 17:53 · 阅读数 34

前言

上篇文章《Mysql之mvcc各场景理解》有通过实验去初步了解了下MVCC，但是不够体系。下面我们还是从场景出发，仅一步：

认识MVCC
了解开发中需要注意什么

mysql版本：

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.29    |
+-----------+
1 row in set (0.01 sec)

隔离级别：

mysql> show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)

新建表：

mysql> CREATE TABLE `user` (
    ->   `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    ->   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   `is_delete` tinyint NOT NULL DEFAULT '0' COMMENT '0:1:',
    ->   `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   `user_name` char(32) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.04 sec)

插入第一条数据：

mysql> insert into user(user_name) values('hhh');
Query OK, 1 row affected (0.01 sec)

总体脑图

Mysql之MVCC

场景

事务的一致读什么时候确定的？

事务A：begin;select * from user where id=1 for update;
事务B：insert into user(user_name) values('hhh');
事务A：select * from user;

第一步执行后查看下information_schema.innodb_trx：

mysql> select * from information_schema.innodb_trx;
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
| trx_id | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking | trx_schedule_weight |
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
|   5923 | RUNNING   | 2022-07-24 10:48:53 | NULL                  | NULL             |          2 |                1215 | NULL      | NULL                |                 0 |                 1 |                2 |                  1128 |               1 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |                NULL |
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
1 row in set (0.00 sec)

可以看到trx_id已经分配。

第三步：

mysql> select * from user;
+----+---------------------+-----------+---------------------+-----------+
| id | create_time         | is_delete | update_time         | user_name |
+----+---------------------+-----------+---------------------+-----------+
|  1 | 2022-07-24 10:47:16 |         0 | 2022-07-24 10:47:16 | hhh       |
|  2 | 2022-07-24 10:50:53 |         0 | 2022-07-24 10:50:53 | hhh       |
+----+---------------------+-----------+---------------------+-----------+
2 rows in set (0.00 sec)

读到了事务B插入的数据，这是为啥呢？

如果去掉for update会出现什么？

事务A：begin;select * from user where id=1;
事务B：insert into user(user_name) values('ddd');
事务A：select * from user;
事务A：insert into user(user_name) values('ddd');

第一步：

mysql> select * from information_schema.innodb_trx;
+-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
| trx_id          | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking | trx_schedule_weight |
+-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
| 562948313501696 | RUNNING   | 2022-07-24 11:04:37 | NULL                  | NULL             |          0 |                1215 | NULL      | NULL                |                 0 |                 0 |                0 |                  1128 |               0 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |                NULL |
+-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
1 row in set (0.00 sec)

为啥trx_id分配的这么大？

第三步：

mysql> select * from user;
+----+---------------------+-----------+---------------------+-----------+
| id | create_time         | is_delete | update_time         | user_name |
+----+---------------------+-----------+---------------------+-----------+
|  1 | 2022-07-24 10:47:16 |         0 | 2022-07-24 10:47:16 | hhh       |
|  2 | 2022-07-24 10:50:53 |         0 | 2022-07-24 10:50:53 | hhh       |
+----+---------------------+-----------+---------------------+-----------+
2 rows in set (0.01 sec)

第三步没读到事务B插入的。（这个笔者还不知道，笔者发现selectlock in share mode也很大，知道的小伙伴欢迎留言）

第四步：

mysql> select * from information_schema.innodb_trx;
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
| trx_id | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking | trx_schedule_weight |
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
|   5954 | RUNNING   | 2022-07-24 11:04:37 | NULL                  | NULL             |          2 |                1215 | NULL      | NULL                |                 0 |                 1 |                1 |                  1128 |               0 |                 1 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |                NULL |
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
1 row in set (0.00 sec)

RR下事务会出现幻读吗？

事务A：select * from user where id=1;
事务B：insert into user(user_name) values('ddd');
事务A：select * from user;
事务A：update user set user_name='hhh' where id=5;（id为刚刚事务B插入的）
事务A：select * from user;

第三步：

mysql> select * from user;
+----+---------------------+-----------+---------------------+-----------+
| id | create_time         | is_delete | update_time         | user_name |
+----+---------------------+-----------+---------------------+-----------+
|  1 | 2022-07-24 10:47:16 |         0 | 2022-07-24 10:47:16 | hhh       |
|  2 | 2022-07-24 10:50:53 |         0 | 2022-07-24 10:50:53 | hhh       |
|  3 | 2022-07-24 11:06:03 |         0 | 2022-07-24 11:06:03 | ddd       |
+----+---------------------+-----------+---------------------+-----------+
3 rows in set (0.00 sec)

第四步：

mysql> update user set user_name='hhh' where id=5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

更新成功。

第五步：

mysql> select * from user;
+----+---------------------+-----------+---------------------+-----------+
| id | create_time         | is_delete | update_time         | user_name |
+----+---------------------+-----------+---------------------+-----------+
|  1 | 2022-07-24 10:47:16 |         0 | 2022-07-24 10:47:16 | hhh       |
|  2 | 2022-07-24 10:50:53 |         0 | 2022-07-24 10:50:53 | hhh       |
|  3 | 2022-07-24 11:06:03 |         0 | 2022-07-24 11:06:03 | ddd       |
|  5 | 2022-07-24 11:23:18 |         0 | 2022-07-24 11:25:34 | hhh       |
+----+---------------------+-----------+---------------------+-----------+
4 rows in set (0.00 sec)

可以发现产生了“幻读”，读到了别的事务产生的数据。

MVCC

InnoDB Mutile-Versioning

InnoDB使用undo log以支持并发和回滚等事务功能，每个行记录的DB_TRX_ID记录最新的事务标识，配合DB_ROLL_PTR字段进行回滚和一致读（读当前需要等待最新的事务完成，直接读行）。

undo log分为更新和插入undo log(删除在内部被当作更新)。插入的undo log事务提交了就可以丢弃。更新的undo log需要等待没有一致读才能丢弃，也就是说如果一直存在一致读undo log无法释放，undo表空间就会填满。

在内部，InnoDB为存储在数据库中每行添加三个字段：

DB_TRX_ID：6个字节用于表示插入或者更新行的最新事务标识。此外，删除在内部被当成一个更新，使用行中特殊的位去标记为已删除。

DB_ROLL_PRT：7个字段被称为回滚指针。回滚指针指向回滚段的undo log记录。如果行已更新，undo log记录包含必要的信息去构建被行修改前的内容。

DB_ROW_ID：6个字节包含row ID，该row ID随着新行的插入自增。如果InnoDB自动生成聚簇索引，则索引包含row ID值。否则DB_ROW_ID列不出现在任何索引中。

Undo logs

undo log分两大类：INSERT undo log和UPDATE（包含DELETE）undo log，存在于undo tablespaces和global temporary tablespace。undo tablespaces和global temporary tablespace默认最多支持128个回滚段，每个回滚段支持的undo log槽依赖于InnoDB page size /16 默认为256（InnoDB page size默认为4k）。

MySql 8.0默认有2个innodb_undo_tablesaces，按照一个事务中既有INSERT又有UPDATE(DELETE)操作来算，默认并发读写的事务数量为256/2*128*2=32768。已经很大了，一般不用创建undo tablesaces。

readview

顾名思义就是视图，使用一套规则实现多版本的一致读，结果需要结合最新的行数据和undo log来获取。（undo log记录的是回退所需要的信息）

RC和RR的区别在于RC每次操作都会更新readview，RR会在第一次快照读的时候就固定住。readview需要借助以下几个字段：

m_ids：表示活跃事务列表
min_trx_id：活跃事务中的最小事务id
max_trx_id：生成readview时系统中应该分配给下一个事务的id
creator_trx_id：当前的事务id

具体规则：

被访问的trx_id与readview中的creator_trx_id相同，表示当前事务在访问自己修改的记录，可见，返回；
被访问的trx_id小于min_trx_id，表明该版本已提交，可见，返回；
被访问的trx_id大于等于max_trx_id，表明该版本在生成readview时，还未开启，不可见，返回。
被访问trx_id在min_trx_id和max_trx_id之间，判断是否在m_id中，如果在，则说明生成readview时，该版本事务未提交，该版本不可见；如果不在，则说明生成readview时，该版本事务已提交可见，返回。

同一行会出现一个undo log版本链，就算该行中的事务都已经提交，但是可能存在一个事务还在进行中需要一致读老版本的数据。

同一行记录不存在同时出现多个进行中的版本，修改行回持有排他锁。

疑问回答

一致读什么时候确定的？换句话说，readview什么时候产生的？上面已经回答，事务中的第一个快照读。事务中的快照读不会触发trx_id的产生，看到的只是一个临时标记的，只有当前读才会产生
为啥更新后能读到另一个事务插入的数据了？

因为更新是当前读所以能读到，读到了之后该行的最新版本trx_id为creator_trx_id所以可见。

开发中需要注意什么？

readview是在事务中第一次快照读才会生成，不是begin时就产生，如果不理解在排查问题的时候会很迷惑。
虽然上面的例子不是很贴切（因为无法直接知道其他事务插入的id），但是也暴露了问题，单纯的依靠RR下的MVCC是无法防止幻读的，如果想防止幻读，需要借助锁，最好是使用索引使用next-key避免锁全表。那就需要在使用select中使用锁，可以使用lock in share mode，但是一般不会在select中“上锁”，可以使用分布式锁，虽然效果一样，但是在高并发时，因为数据库能支撑的并发数一般是千级别并且数据库资源比较金贵（通过数据库控制不仅导致阻塞产生在数据库端，占用数据库连接，同时真正阻塞前还要经过反序列化mysql的请求信息，还要经过词法、语法分析、优化器分析等都需要消耗CPU资源），使用分布式锁去实现比较合适。

参考

转载自:https://juejin.cn/post/7126101790033969160