一文入门MySQL8.0 事务、隔离级别

最新推荐文章于 2024-07-26 03:43:27 发布

抓不到兔子的小灰狼

最新推荐文章于 2024-07-26 03:43:27 发布

阅读量273

点赞数 1

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/u011189762/article/details/120351619

版权

mysql 专栏收录该内容

2 篇文章 1 订阅

订阅专栏

文章目录

前言
一、事务的ACID特性
二、Mysql支持的隔离级别
三、实战加深理解

前言

通过一个简单的例子，简单理解隔离级别、MVCC和ReadView

一、事务的ACID特性

原子性(Atomicity)：语句要么全执行，要么全不执行，是事务最核心的特性，事务本身就是以原子性来定义的；实现主要基于undo log
持久性(Durability)：保证事务提交后不会因为宕机等原因导致数据丢失；实现主要基于redo log
隔离性(Isolation)：保证事务执行尽可能不受其他事务影响；重点理解
一致性(Consistency)：事务追求的最终目标，一致性的实现既需要数据库层面的保障，也需要应用层面的保障

二、Mysql支持的隔离级别

注意：事务需要隔离的前提是，两个事务的执行时间有“交集”。如果有两个事务A和B，事务B是在A提交后才开始执行的，那么他们本身就是串行的。

在了解隔离级别前先了解下，脏读、不可重复读和幻读的问题

脏读：读到其他事务未提交的数据
不可重复读：在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。
幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了

MySQL支持的4种隔离级别如下：

读未提交：Read Uncommitted, 脏读、不可重复读和幻读都有可能发生，
读已提交：Read Committed, 不可重复读和幻读可能发生，
可重复读：Repeatable Read，简称RR, 幻读可能发生。重点理解
可串行化：Serializable, 都不会发生

Repeatable Read 对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT/DELETE操作,可能行数会变，而不是UPDATE操作

在实践中：读未提交(Read Uncommitted)在并发时会导致很多问题，而性能相对于其他隔离级别提高却很有限，因此使用较少。可串行化强制事务串行，并发效率很低，只有当对数据一致性要求极高且可以接受没有并发时使用，因此使用也较少。因此在大多数数据库系统中，默认的隔离级别是读已提交(如Oracle)或可重复读（后文简称RR）。

MySQL默认的存储引擎是InnoDB，本文在没有特殊说明下，默认都是指InnoDB。InnoDB默认的隔离级别是RR，但需要注意的是InnoDB实现的RR避免了幻读问题。

2.1 InnoDB RR的实现机制

2.1.1 锁机制（包含next-key lock）

作用：隔离(一个事务)写操作对(另一个事务)写操作的影响。
介绍：关于MySQL锁的介绍详见：MySQL8.0锁介绍

2.1.2 MVCC（包括数据的隐藏列、基于undo log的版本链、ReadView）

1) MVCC (Multi Version Concurrency Control)

定义：多版本的并发控制协议。即：在同一时刻，不同的事务读取到的数据可能是不同的(多版本）
作用：隔离(一个事务)写操作对(另一个事务)读操作的影响

关于隐藏列
InnoDB给每行添加了三个字段

DB_TRX_ID： 6 byte, trx_id, 指示插入或更新行的最后一个事务的事务标识符。此外，删除在内部被视为更新，单独的bit位标记删除；
DB_ROLL_PTR： 7 byte, roll pointer, 指向一个undo日志record。如果row被更新， undo日志记录包含了更新前的内容，来进行必要的rebuild.
DB_ROW_ID: 6 byte, 行 ID，随着插入新行而单调增加。如果 InnoDB自动生成聚集索引，则该索引包含行 ID 值。否则，该DB_ROW_ID列不会出现在任何索引中。（可以理解为默认的主键索引，主键索引是聚集索引，其他的索引是二级索引）

Mysql原文如下：
Internally, InnoDB adds three fields to each row stored in the database:
A 6-byte DB_TRX_ID field indicates the transaction identifier for the last transaction that inserted or updated the row. Also, a deletion is treated internally as an update where a special bit in the row is set to mark it as deleted.
A 7-byte DB_ROLL_PTR field called the roll pointer. The roll pointer points to an undo log record written to the rollback segment. If the row was updated, the undo log record contains the information necessary to rebuild the content of the row before it was updated.
A 6-byte DB_ROW_ID field contains a row ID that increases monotonically as new rows are inserted. If InnoDB generates a clustered index automatically, the index contains row ID values. Otherwise, the DB_ROW_ID column does not appear in any index.

2）关于undo log版本链

回滚段中的undo log分为insert和update undo log。insert undo logs仅在事务回滚时需要，并且可以在事务提交后立即丢弃。update undo log是update或delete操作中产生的undo log，也用于一致性读取，但只有在不存在 InnoDB 为其分配快照的事务后才能丢弃它们（purge线程进行最后的删除操作），在一致性读取中可能需要update undo log中的信息来恢复数据。
每次对记录进行改动，都会生成一条 undo日志，每条undo日志中的roll pointer（ INSERT 操作对应的 undo日志没有该属性，因为该记录并没有更早的版本），可以将这些 undo日志都连起来，串成一个链表，即版本链。
在这里插入图片描述
图片来自：https://blog.csdn.net/weixin_39997438/article/details/107753648

3）关于ReadView

ReadView是一个概念，类似于快照。包含生成ReadView时，活跃的事务ID集合，即：所有未提交的事务ID，我们可将集合记为utrx_ids，当前事务记作事务A。对于版本链中数据的undo log segment的trx_id，如果小于utrx_ids，表示此事务已提交，事务A可以读取。反之，则不可以。

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本，如果最后一个版本也不可见的话，那么就意味着该条记录对该事务不可见，查询结果就不包含该记录。

对于RR隔离级别的事务，在第一次SELECT后会生成一个ReadView，这个ReadView会贯穿到事务提交，中间不会再生成任何ReadView。其中，对表A的SELECT生成的ReadView，也会对表B也会有效。

2.2 参考命令

2.2.1 查看MySQL隔离级别

注意：tx_isolation在MySQL 5.7.20后被弃用，所以8.0使用的是transaction_isolation

//全局的
select @@global.transaction_isolation;
//当前的
select @@transaction_isolation; 
show variables like '%transaction_isolation%' ;

2.2.2 查看mysql版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)

三、实战加深理解

1.新建测试表

CREATE TABLE `test`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `value` varchar(64),
  PRIMARY KEY (`id`)
);
//构建数据
insert into test(value)  values("v1");

2.测试

两个事务A和B，A事务begin后，第二步骤是事务B begin。

case1：脏读、不可重复读测试

事务A，先更新不提交

//事务A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set value='v2' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test where id=1;
+----+-------+
| id | value |
+----+-------+
|  1 | v2    |
+----+-------+
1 row in set (0.00 sec)

事务B，查询，生成ReadView，因为A没提交所以读不到修改，无脏读

//事务B
mysql> select * from test where id=1;
+----+-------+
| id | value |
+----+-------+
|  1 | v1    |
+----+-------+
1 row in set (0.00 sec)

事务A，提交

//事务A
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

事务B，再次查询，前后两次查询结果一致，可以重复读

//事务B
mysql> select * from test where id=1;
+----+-------+
| id | value |
+----+-------+
|  1 | v1    |
+----+-------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

case2：幻读测试

事务B，先查询下总数

//事务B
mysql> select count(1) from test;
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

事务A，插入下数据

//事务A
mysql> insert into test(value)  values("n1");
Query OK, 1 row affected (0.00 sec)

mysql> select count(1) from test;
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

事务B，再次查询下总数不变

//事务B
mysql> select count(1) from test;
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

事务A，提交

略

事务B，再次查询下总数不变

//事务B
mysql> select count(1) from test;
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

case3：两个事务同时更新

事务A 更新不提交

//事务A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 | v5    |
|  2 | n1    |
+----+-------+
2 rows in set (0.00 sec)

mysql> update test set value='v6' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 | v6    |
|  2 | n1    |
+----+-------+
2 rows in set (0.01 sec)

事务B会阻塞

//事务B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql>  update test set value='v7' where id=1;

然后事务A回滚

//事务A
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

事务B获得锁，更新成功

//事务B
mysql>  update test set value='v7' where id=1;
Query OK, 1 row affected (7.19 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 | v7    |
|  2 | n1    |
+----+-------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

case4：两个事务同时更新2: 更新同样的值

如果两个事务同时更新同一记录，且事务B与事务A的执行的内容一致。即：模拟重复的事务。
事务A，更新不提交

//事务A
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test where id=1;
+----+-------+
| id | value |
+----+-------+
|  1 | v7    |
+----+-------+
1 row in set (0.00 sec)

mysql>
mysql> update test set value='v8' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

事务B，查询发现值是旧值，然后开始更新，结果被阻塞

//事务B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where id=1;
+----+-------+
| id | value |
+----+-------+
|  1 | v7    |
+----+-------+
1 row in set (0.00 sec)

mysql> update test set value='v8' where id=1;

事务A提交

//事务A
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

事务B，获得锁更新成功，但是0 rows affected，但是再次查询发现查询的结果没变

//事务B
mysql> update test set value='v8' where id=1;
Query OK, 0 rows affected (23.47 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from test where id=1;
+----+-------+
| id | value |
+----+-------+
|  1 | v7    |
+----+-------+
1 row in set (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)