当前位置：首页 > article >正文

MySQL——隔离级别及解决方案

article 2024/9/23 19:25:52

CRUD不加控制，会有什么问题？

在这里插入图片描述

比如上图场景，当我们的客户端A发现还有一张票的时候，将票卖掉，嗨还没有执行更新数据库的时候，客户端B又检查票数，发现票数大于0，又卖掉了一张票。然后客户端A更新数据库，客户端B也更新数据库。那么此时就导致了同一张票被卖了2次的问题。

所以要解决以上的问题，有满足一下的操作

买票的过程是原子的
买票相互之间不能影响
买完票后是永久有效的
买票和买后都要是确定的状态

所以要解决以上问题，有了事务的概念。

什么是事务

一个MySQL数据库，同一时刻有很多客户端，甚至有大量的请求包装成事务，而每条事务至少有一条SQL，甚至多条SQL，如果大家都访问相同的表数据，在不加保护的情况下，绝对会出现问题。因为事务由多条SQL组成，那么就存在着执行到一半或出现其他意外的情况发生，那么已经执行的SQL语句怎么办呢？

所以，一个完整的事务，要包括一下的四个属性

原子性：一个事务中所有的操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行的过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和结束之后，数据库的完整性没有被破坏。这表示写入的资料必须为完全符合所有的预设规则，这包含了资料的精确度、串联性以及后序数据库可以自发性的完成预定的工作
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行的时候，由于交叉执行而导致数据的不一致。事务隔离分为了不同的级别，包括读未提交、读提交、可重复度、串行化。

上述也就是所谓的ACID。

原子性（Atomicity，或称不可分割性）
一致性（Consistency）
隔离性（Isolation，又称独立性）
持久性（Durability）

并发的事务会导致什么问题

MySQL服务端是允许多个客户端来连接的，这意味着MySQL会出现同时处理多个事务的情况。

那么在处理多个事务的时候，就可能会出现脏读、不可重复读、幻读的问题

脏读

如果一个事务读到了另一个事务没有提交后的数据，那么就叫做脏读。

下面以读未提交的场景下来演示

首先先将隔离级别设置为读为提交

mys l> set global transaction isolation level read uncommitted; 
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation; 
+------------------+ 
| @@tx_isolation | 
+------------------+ 
| READ-UNCOMMITTED | 
+------------------+ 
1 row in set, 1 warning (0.00 sec) 
 
mysql> select * from account; 
+----+--------+----------+ 
| id | name | blance | 
+----+--------+----------+ 
| 1 | 张三 | 100.00 | 
| 2 | 李四 | 10000.00 | 
+----+--------+----------+ 
2 rows in set (0.00 sec) 
 
mysql> begin; --开启事务 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> update account set blance=123.0 where id=1; --更新指定行 
Query OK, 1 row affected (0.05 sec) 
Rows matched: 1 Changed: 1 Warnings: 0 
 
--没有commit哦！！！

--终端B 
mysql> begin; 
mysql> select * from account; 
`+----+--------+----------+ 
| id | name | blance | 
+----+--------+----------+ 
| 1 | 张三 | 123.00 | --读到终端A更新但是未commit的数据[insert，delete同样] 
| 2 | 李四 | 10000.00 | 
+----+--------+----------+ 
2 rows in set (0.00 sec) 
 
--一个事务在执行中，读到另一个执行中事务的更新(或其他操作)
-- 但是未commit的数据，这种现象叫做脏读(dirty read) ``

不可重复读

在同一个事务中多次读取同一个数据，如果出现了数据不一致的问题，那么就发生了不可重复读

下面以读提交来演示

-- 终端A 
mysql> set global transaction isolation level read committed; 
Query OK, 0 rows affected (0.00 sec) 
--重启客户端 
mysql> select * from account; --查看当前数据 
+----+--------+----------+ 
| id | name | blance | 
+----+--------+----------+ 
| 1 | 张三 | 123.00 | 
| 2 | 李四 | 10000.00 | 
+----+--------+----------+ 
2 rows in set (0.00 sec) 
 
mysql> begin; --手动开启事务，同步的开始终端B事务 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> update account set blance=321.0 where id=1; --更新张三数据 
Query OK, 1 row affected (0.00 sec) 
Rows matched: 1 Changed: 1 Warnings: 0 
mysql> commit; --commit提交！ 
Query OK, 0 rows affected (0.01 sec)

--切换终端到终端B，再次查看数据。 
 
--终端B 
mysql> begin; --手动开启事务，和终端A一前一后 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> select * from account; --终端A commit之前，查看不到 
+----+--------+----------+ 
| id | name | blance | 
+----+--------+----------+ 
| 1 | 张三 | 123.00 | --老的值 
| 2 | 李四 | 10000.00 | 
+----+--------+----------+ 
2 rows in set (0.00 sec) 
 
--终端A commit之后，看到了！ 
mysql> select *from account; 
+----+--------+----------+ 
| id | name | blance | 
+----+--------+----------+ 
| 1 | 张三 | 321.00 | --新的值 
| 2 | 李四 | 10000.00 | 
+----+--------+----------+ 
2 rows in set (0.00 sec)

幻读

在一个事务内多次查询某个已经符合条件的数据，如果出现了前后两次查询到的记录数量不一致的情况，那么就出现了幻读问题

事务的隔离级别

当多个事务并发执行的时候，会出现脏读、不可重复读、幻读的情况。这些现象会对一致性产生同步程度的影响。

脏读：读到了另一个事务还没有提交的数据
不可重复读：前后读取到的数据不一致
幻读：前后读取的记录数量不一致

所以针对以上问题，MySQL提出了四种隔离级别来解决问题。

**读未提交（**read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到
读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到
可重复读 （repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别
串行化（serializable ）会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

不同的隔离级别，并发的现象也会不同

加粗样式

总结：

其中隔离级别越严格，安全性越高，但数据库的并发性能也就越低，往往需要在两者之间找一个平衡点
不可重复读的重点是修改和删除：同样的条件, 你读取过的数据,再次读取出来发现值不一样了幻读的重点在于同样的条件, 第1次和第2次读出来的记录数不一样

幻读问题解决

在解决幻读问题的时候有两种方式，第一种就是MVCC，第二种就是通过加next-key锁解决

在聊MVCC之前，先理解一下3个知识

3个记录隐藏字段
undo日志
Read View

3个隐藏列字段

DB_TRX_ID：6btye，最近修改事务ID，记录创建这条记录/最近一次修改该记录的事务ID
DB_ROLL_PTR：7byte，回滚指针，指向了这条记录的上个版本（数据一般保存在undo日志中）
DB_ROW_ID：6btye，隐含了自增的ID（隐藏主键），如果数据表中没有主键，InnoDB会自动以BD_ROW_ID产生一个聚簇索引
flag删除字段，既记录被更新或删除并不代表真的删除了，只是flag字段别设置为了1

假设表的结构是：

mysql> create table if not exists student( 
 name varchar(11) not null, 
 age int not null 
 ); 
 
mysql> insert into student (name, age) values ('张三', 28); 
Query OK, 1 row affected (0.05 sec) 
 
mysql> select * from student; 
+--------+-----+ 
| name | age | 
+--------+-----+ 
| 张三 | 28 | 
+--------+-----+ 
1 row in set (0.00 sec)

其实表结构在MySQL中真实为
在这里插入图片描述
目前并不知道创建该记录的事务ID，隐式主键，我们就默认设置成null，1。第一条记录也没有其他版本，所以设置回滚指针为null。

undo日志，先简单理解为一段内存缓冲区，用来保存日志数据。

模拟MVCC

现在有一个事务10(仅仅为了好区分)，对student表中记录进行修改(update)：将name(张三)改成name(李四)。

事务10，因为要修改，所以要给该记录加锁
修改前，现将改行记录拷贝到undo log中，所以，undo log中就有了一行副本数据。(原理就是写时拷贝)
所以现在 MySQL 中有两行同样的记录。现在修改原始记录中的name，改成 ‘李四’。并且修改原始记录的隐藏字段 DB_TRX_ID 为当前事务10 的ID, 我们默认从 10 开始，之后递增。而原始记录的回滚指针 DB_ROLL_PTR 列，里面写入undo log中副本数据的地址，从而指向副本记录，既表示我的上一个版本就是它。
事务10提交，释放锁。

在这里插入图片描述

现在又有一个事务11，对student表中记录进行修改(update)：将age(28)改成age(38)。

事务11,因为也要修改，所以要先给该记录加行锁。
修改前，现将改行记录拷贝到undo log中，所以，undo log中就又有了一行副本数据。此时，新的副本，我们采用头插方式，插入undo log。
现在修改原始记录中的age，改成 38。并且修改原始记录的隐藏字段 DB_TRX_ID 为当前事务11 的ID。而原始记录的回滚指针 DB_ROLL_PTR 列，里面写入undo log中副本数据的地址，从而指向副本记录，既表示我的上一个版本就是它。
事务11提交，释放锁。

在这里插入图片描述

你看，上面的一个一个版本，其实就是一个一个的快照。

如果上面的操作是delete呢？不是update呢？是否可以形成版本链呢？？

其实是可以的，我们隐藏字段中，有一个flag字段，删除数据其实不是将数据真的删除了，而是将flag字段设置为了1

对于select而言，select不会对数据产生影响，所以为了seelct维护版本链没有意义。那么有一个问题就很重要了，select 是读取最新的数据呢，还是读取历史的数据呢？？

当前读：读取最新的记录，就是当前读。增删改，都叫做当前读，select也可以通过 select lock in share 或者 select … for update

可以看到在多个事务中，同时进行增删改的操作的时候，是要加锁的。那么同时select也是要加锁的。但如果是快照读，那么是不需要加锁的，也就是可以并发控制了。 这就提高了效率，这就是MVCC的意义所在了。

如何保证不同的事务之间，看到的不同的内容呢？也就是如何实现隔离级别的

那么就要讲到Read View了。 Read View就是事务进行快照读的时候生产的读视图，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务ID。

在MySQL源码中，Read View其实就是一个类，本质就是对可见性的判断。当我们某个事务执行快照读的时候，该记录会生成一个Read View读视图，用来判断当前事务可以看到哪个版本的数据。可能是最近的数据，也可能是该记录中undo日志中的某个版本。

class ReadView { 
 // 省略... 
 private: 
 /** 高水位，大于等于这个ID的事务均不可见*/ 
 trx_id_t m_low_limit_id 
 
 /** 低水位：小于这个ID的事务均可见 */ 
 trx_id_t m_up_limit_id; 
 
 /** 创建该 Read View 的事务ID*/ 
 trx_id_t m_creator_trx_id; 
 
 /** 创建视图时的活跃事务id列表*/ 
 ids_t m_ids; 
 
 /** 配合purge，标识该视图不需要小于m_low_limit_no的UNDO LOG， 
 * 如果其他视图也不需要，则可以删除小于m_low_limit_no的UNDO LOG*/ 
 trx_id_t m_low_limit_no; 
 
 /** 标记视图是否被关闭*/ 
 bool m_closed; 
 
 // 省略... 
}; 

m_ids; //一张列表，用来维护Read View生成时刻，系统正活跃的事务ID 
up_limit_id; //记录m_ids列表中事务ID最小的ID(没有写错) 
low_limit_id; //ReadView生成时刻系统尚未分配的下一个事务ID，也就是目前已出现过的事务ID的最大值+1(也没
有写错) 
creator_trx_id //创建该ReadView的事务ID

在这里插入图片描述

所以一个事务去访问记录的时候，除了自己的更新记录总是可见的之外，还有几种情况：

如果记录的trx_id值小于 Read View 中的min_trx_id ，表示这个版本的记录是在创建 Read View前已经提交的事务生成的，所以该版本的记录对当前事务可见
如果记录的 trx_id 值大于等于 Read View 中的max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见
如果记录的 trx_id 值在 Read View 的min_trx_id 和max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中
- 如果记录的trx_id在m_ids列表中，表示生成的该版本的活跃事务依然在运行，还没有提交事务。所以对当事务不可见
- 如果记录的trx_id不在m_ids列表中，表示该事务已经提交了，所以对当前的事务是可见的。

RR和RC的本质区别

正是Read View生成时机的不同，从而造成RC,RR级别下快照读的结果的不同
在RR级别下的某个事务的对某条记录的第一次快照读会创建一个快照及Read View, 将当前系统活跃的其他事务记录起来
此后在调用快照读的时候，还是使用的是同一个Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View，所以对之后的修改不可见
而在RC级别下的，事务中，每次快照读都会新生成一个快照和Read View, 这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因
总之，RC隔离级别下，是每个快照读都会生成并获取最近的Read View，而在RR的隔离级别下，是同一个事务中的第一个快照读才会创建Read View，之后的快照读获取的都是第一个Read View
正是因为RC快照读的时候，都会生成一次Read View，所以RC才会有不可重复读的问题。