MySQL事务_数据库保存点名怎么设置-CSDN博客

本文链接：https://blog.csdn.net/Colorful_X/article/details/117126118

文章目录

事务介绍
事务原理
事务日志

事务介绍

事物的定义： 一个事务是由一条或者多条对数据库操作的SQL语句组成的不可分割的单元。只有当事务中所有的SQL操作都正常执行了，整个事务才会提交到数据库，如果事务中部分SQL语句执行失败，则整个事务要么回滚到最初的状态，要么失败。所以，一个事务要么全部提交给数据库，要么就不提交给数据库。

事务的特征(ACID)

① 原子性(Atomic)：事务是一个不可分割的单元，事务必须具有原子性，即要么全部执行，要么都不执行，不允许部分执行的情况。

② 一致性(Consistency)：一个事务执行之前和执行之后，数据库的数据必须保持一致状态。拿转账来说，比如A和B中两者的钱加起来一共有10000，无论A和B怎么转账，转几回账，最终A和B的用户加起来还是10000，这就是一致性。

③ 隔离性(Isolation)：当两个事务或者多个事务并发执行时，为了保证数据的安全性，将一个事务内存的操作和其他事物的操作需要隔离起来，不被其他正在执行的事务所看到，隔离性使得每个事务的更新在提交之前，对于其他的事务是不可见的。

④ 持久性(Durability)：事务完成之后，对数据库中的数据改变是永久性的。

事务的使用

① 查看事务： select @@autocommit;，默认值为1，表示事务自动提交，0表示事务手动提交。
在这里插入图片描述

② 设置事务：set @@autocommit = 1;：表示事务自动提交。set @@autocommoit = 0;表示事务需要手动提交。
在这里插入图片描述

③ 开启事务： begin 或者 start transaction

④ 事务提交： commit;当我们设置事务需要手动提交时，执行commit，才会提交完成。

⑤ 事务回滚：rollback;如果事务在执行的过程中有部分SQL语句执行失败，则使用rollback回到最初的状态。

⑥ 设置保存点：savepoint 保存点名：设置保存点，保存点使事务回滚到指定的保存位置，

⑦ 查看事务隔离级别：select @@tx_isolation

举个例子使用一下事务：

                        我们首先设置手动提交事务。student表中数据如下：
在这里插入图片描述
        接着，我们使用事务，向student表中添加一行数据，并查看如果不手动提交，student表中数据是否可以被修改;

        我们重新打开一个MySQL客户端，查看student表中的数据有没有被修改

        我们发现student表中的记录保持不变，没有新添加的那行数据，这就是因为我们设置了事务的手动提交，我们需要显示的调用 commit完成上面添加操作对数据库的改变，如下：
在这里插入图片描述

我们接下来看一看保存点是如何使用的：

同样的，我们向student表中添加一行数据，只不过，这次我在执行完之后，忽然不想添加这行数据了，这个时候，我们的保存点就起作用了，如下：

事务执行前，设置保存点p1；
在这里插入图片描述

        执行事务：
在这里插入图片描述
        于是，我们让事务回滚到保存点位置
        再次提交事务，看看student表有没有变化：

        我们发现，student表中没有任何变化。这就是保存点的作用。当一个事务执行失败或者不想让他执行时，我们就可以使用保存点。

二更：

事务并发会出现的问题

① 脏读(Dirty read)：A事务读取到了B事务修改但为提交的数据，那么A读取到的数据就是脏数据。

举个例子：
假设数据为1000，B事务修改了这个数据将其变为500，此时A事务读取数据，读取到了500，但是，B事务由于一些原因，将刚才的执行的事务回滚了，此时数据变回了1000，这个时候A事务再去读取时发现读取到的数据是1000，我们把第一次A事务读取到的数据称为脏数据，这就是因为A事务读取到了B事务修改且未提交的数据。

        ② 不可重复读(Unrepeatable read)：事务A多次读取统一数据，B事务在A事务多次读取的过程中，修改了数据并提交，则A事务在后面读取到的内容，和最开始读取到的内容不一致。

        举个例子：
                A事务读取到的数据为1000，此时B事务也读取到的数据为1000，并修改了这个数据变为500，并且提交了事务。则这个数据现在在数据库中的值为500，此时A再去读，读到的就是500。我们发现，A事务两次读取到的数据不一致，这就是不可重复读。

③ 幻读(Phantom read)：A事务读取到了B事务提交的新增数据，幻读一般发生在数据的统计事务中。

举个例子：
A事务先开始读取到了数据库中一共有10条数据，此时B事务又向数据库添加了一条数据，然后A事务再去查询数据库发现一共有11条数据，好像是发生了幻觉一样，我们称这种情况为幻读。

幻读和不可重复读的区别：不可重复读的重点在于修改，而幻读一般针对的是对表的新增数据或删除。解决幻读需要锁住表，解决不可重复读只需要锁住那一行数据即可。

MySQL事务的隔离级别

        MySQL一共分为四种事务的隔离级别。
        分别是：
                ① READ-UNCOMMITED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，会导致脏读、不可重复读、幻读。

② READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但不可重复读、幻读仍有可能发生。

③ REPEATABLE-READ(可重复读)：对同一字段的多次读取的结果是一致的，除非数据是被本身事务所修改，可以阻止脏读、不可重复度，但是不能阻止幻读的发生。

④ 可串行化(SERIABLIZABLE)：最高的隔离级别，所有事务逐个执行，该级别可以防止脏读、幻读、不可重复读。

隔离级别越低，事务请求的锁就越少。

使用select @@tx_isolation 查看MySQL的事务隔离级别。
在这里插入图片描述
可以看到MySQL的事务隔离级别默认为：REPEATABLE-READ

演示事务并发的问题

一、脏读：
① 我们打开两个MySQL客户端，模拟并发场景。我们先设置客户端A的事务隔离级别为READ-UNCOMMITTED(读未提交)，即最低的隔离级别。

        ② 在客户端A中，先查询所有信息，然后于此同时，客户端B修改id为5的数据信息：
在这里插入图片描述
                我们发现，在事务A中出现了脏读。这是因为我们设置了事务隔离级别为读未提交，是最低的隔离级别，此时就会出现事务A中会读取到异常数据。

我们将事务的隔离级别设置为READ COMMITTED(读取已提交)，再来看看是否还会出现脏读。
在这里插入图片描述
我们发现，这次并没有出现脏读，这是为什么呢？我们往下来看。

事务原理

MVCC

出现“脏读”、“不可重复读”、“幻读”，都是数据库读一致性的问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离机制的方式，基本上可分为以下两种：

一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照(Snapshot)，并用这个快照来提供一定级别(与语句级或事务级)的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制(Multi Version Concurrency Control)，简称MVCC。

InnoDB存储引擎的行锁主要有两种：

读锁：也叫共享锁、S锁，允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。大白话就是，事务A获取到了这个数据行的读锁，事务B也可以获取当前数据行的的读锁，两个事务各读各的，但是一旦事务A获取到了该数据行的读锁，其它事务就不能再获取该数据行的写锁了。即可以共享读数据，不允许任何一个事务修改该数据航
写锁：也叫排它锁、X锁，允许获得排它锁的事务更新数据，阻止其它事务取得相同数据集的共享读锁和排他写锁。大白话就是，事务A获取了当前数据行的写锁，那么事务A既可以读，也可以修改。但是其它事务不能再对当前数据行加任何锁，直到事务A释放当前数据行的锁，这样保证了其它事务在事务A释放锁之前不再读取和修改当前数据行。

快照读：读取的是快照版本，也就是历史版本

当前读：读取的是最新版本。

一致性读(consistent read)：InnoDB用多版本控制来提供数据库在某个时间点的快照，如果隔离级别为REPEATABLE READ，那么在同一个事务中所有一致性读都读的是事务中第一个这样读读到的快照，如果是READ COMMITTED，那么事务中的每一个一致性读都会读到它自己刷新的快照版本。==一致性读是READ COMMITTED 和 REPEATABLE READ隔离级别下普通SELECT语句默认的模式。==一致性读不会给它所访问的表加任何形式的锁，因此其他事物可以同时并发的修改它们。

总结一点就是：只有是普通的SELECT读，读取的是快照读，而在修改的时候，遵循当前读。

MVCC实现原理：

首先，介绍一种日志：Undo log，Undo log是InnoDB存储引擎日志的一种，记录了老版本的数据，InnoDB的MVCC实现就是基于Undo log的。

当我们对数据进行操作的时候，就会产生undo记录。

InnoDB存储引用在数据表中的记录中的每一行都新添加了三个字段,下图是真实的每条记录结构：
在这里插入图片描述

DATA_TRX_ID：标记了最新更新这条行记录的事务id，每处理一个事务，其值自动+1。

DATA_ROLL_PTR：指向当前记录想的uodo log记录，找到之前版本的数据就是通过这个指针。

DB_ROW_ID：行标识，当有innodb自动产生聚集索引时，聚集索引包括这个DB_ROW_ID的值，否则聚集索引中不包括这个值，这个用于索引当中。

当我们针对一条记录进行修改操作时，则会：

针对该记录加入排它锁
把该记录的原本的最新的记录拷贝到undo log日志中，DB_TRX_ID 和 DB_ROLL_PTR不变
修改生成新的记录，此时该记录中的DB_ROLL_PTR指向被拷贝到undo log日志中的那个版本，DB_TRX_ID变为修改这次记录的事务ID。
通过这样往复，就会形成一个版本链。

通过下面的图，来说明上面的过程：
在这里插入图片描述
假设，原本的数据为10，事务ID为100，此时，事务ID为200的事务修改这个数据，则会首先将原数据行拷贝到undo log日志中，原数据行中的值都不变，然后事务ID为200的事务修改并生成新的数据行，这个数据行的DATA_ROLL_PTR指向已经被拷贝到undo log日志中的数据行，DATA_TRX_ID变为200。

ReadView

对于READ UNCOMMITTED隔离级别的事务来说，由于可以读取到未提交事务修改过的记录，所以，READ UNCOMMITTED是通过读取版本链中最新的版本来实现的。而对于SERIALIZABLE隔离级别的事务来说，是通过加锁的方式来实现的，而对于READ COMMITTED和REPEATABLE READ隔离级别的事务来说，它们都保证了读到的是已提交了的事务修改过的记录，也就是说，另一个事务已经修改了记录但是未提交，其他事务是不能读取最新版本的记录的。那么问题来了，怎么判断版本链中的哪个版本对于当前事务是可见的？

这时，就出现了ReadView的概念。ReadView中主要包含4个内容：

m_ids：表示在生成ReadView时当前系统中活跃的读写事务的 事务id列表。

min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。

max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。

creator_trx_id：表示生成该ReadView的事务的事务id。当只对表做改动时(执行INSERT、DELETE、UPDATE这些语句时)才会为事务分配id，否则一个只读事务中的事务id默认为0。

注意，max_trx_id不是当前活跃的事务中的最大事务id，比如现在有id为1 2 3 这三个事务活跃，之后id为3的事务提交了，那么一个新的读事务在生成ReadView时，m_ids就包括1和2，min_trx_id的值为1，max_trx_id的值就为4。

有了ReadView，在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：

如果被访问版本的trx_id(事务id)属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可被当前事务访问。
如果被访问版本的trx_id属性值小于ReadView中的min_trx_id，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值大于或等于ReadView中的max_trx_id值，说明生成生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
如果被访问版本的trx_id属性值在min_trx_id和max_trx_id值之间，那就需要判断一下trx_id属性值是不是在m_ids中，如果在，说明创建ReadView时生成该版本的事务还在活跃状态，该版本不可以被访问，如果不在，说明生成ReadView时生成该版本的事务已经被提交，该版本可以被访问。

在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的一个非常大的区别就在于生成ReadView的时机不同。

READ COMMITTED：每次读取数据之前都生成一个ReadView。
REPEATABLE READ：在第一次读取数据时生成一个ReadView，之后无论读多少次数据，都不会改变这个ReadView。

READ COMMITTED和REPEATABLE READ是如何实现各自的隔离机制的？

综上，我们再来看一看我们之前举得例子，为什么使用READ COMMITTED隔离机制，会避免脏读。

我们事务A和事务B的执行流程如下：
在这里插入图片描述
此时，版本链如图所示：

当我们使用的READ COMMITTED隔离级别时，事务A每进行一次SELECT 查询时，都会生成一个ReadView，这个ReadView中保存有当前活跃的事务id，即m_ids字段中保存100,101，其中min_trx_id字段保存100，max_trx_id字段保存102，此时，事务A在访问版本链时，会根据每个版本的DATA_TRX_ID和自己生成的ReadView中的creator_trx_id相比较，就以上图为例，假设找到了S版本，事务A发现，这个版本的版本号和我生成的ReadView中的版本号相同，诶，这是我自己访问修改的，那我可以访问这个版本，又找到了Z版本，发现，这个版本的版本号在我生成的ReadView中的min_trx_id和max_trx_id之间，并且还属于m_ids字段中，即 102 ∈ {101,102}。那生成这个版本的事务还在活跃状态，所以事务A不能访问该版本。

当事务B提交以后，如果事务A继续进行查询操作的话，又会生成一个ReadView，此时这个ReadView中的 m_ids字段只有101，然后事务A找到版本链中的S版本时，发现，S版本的事务id(102)存在于 min_trx_id (101)和max_trx_id (103)之间，但是不存在于m_ids (此时m_ids中只有101)，那么，说明，生成该版本的事务(即事务B)已经被提交了，事务A可以访问该版本了。
这也验证了READ COMMITTED隔离级别只能避免脏读，而避免不了不可重复读。

而REPEATABLE READ是在每次执行事务前，就生成了ReadView，并且在多次查询时，这个ReadView是不变的，即，先开始是啥样最后还是啥样。

三更：

事务日志

在MVCC中，要保证事务的执行用到了undo log日志文件，事务要保证ACID特征的完整性就必须依靠事务日志做跟踪，每一个操作在真正写入数据库之前，会先写入到日志文件中，如要删除一行数据会先在日志文件中将此行标记为删除，但是数据库中的数据文件并没有发生变化。在事务引擎上的每一次写操作都需要执行两遍如下的过程：

先写入日志文件中，写入日志文件中的仅仅是操作过程，而不是操作数据本身，所以速度比写数据库文件要快很多。
然后再写入数据库文件中，写入数据库文件的操作是重做事务日志中已提交的事务操作的记录。

redo log

在InnoDB存储引擎中，事务日志通过重做(redo)日志和InnoDB存储引擎的日志缓冲实现。
事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中，在事务提交之前，这些缓冲的日志都需要提前刷新到磁盘上持久化，这就是"日志先行"。

InnoDB的redo log是固定大小的，比如可以配置一组为4个文件，每个文件的大小是1GB，那么这块日志总共就可以记录4GB的操作。

其实大白话就是，把每次要执行的事务都记录在一个日志文件中。

undo log

undo log主要为事务的回滚服务。在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。单个事务的回滚，只会回滚当前事务做的操作，并不会影响到其他的事务做的操作。

redo log其实保证的是事务的持久性和一致性，而undo log则保证了事务的原子性。

undo log是逻辑日志，可以理解为：

当delete一条记录时，undo log中会记录一条对应的insert记录
当insert一条记录时，undo log中会记录一条对应的delete记录
当update一条记录时，记录一条对应相反的update记录。

binlog

MySQL的主从复制原理就是通过binlog来实现的。binlog是server层的日志，主要做mysql功能层面的事情。

binlog与redo log的区别在于：

redo log是InnoDB独有的，binlog是所有存储引擎都可以使用的。redo log是物理日志，记录的是在某个数据页上做了什么修改，redo是循环写的，空间会用完，用完后会覆盖以前的内容。
binlog是逻辑日志，记录的是这个语句的原始逻辑。binlog是可以追加写的，不会覆盖之前的日志信息。