MySQL 事务

标签：回滚 隔离 事务 提交 MySQL 数据 ID

  概念介绍 

        事务就是一组DML语句组成，这些语句在逻辑上存在相关性，这一组 DML 语句要么全部成功，要么全部失败，是一个整体。MySQL 提供一种机制，保证我们达到这样的效果。         事务就是要做的或所做的事情，主要用于处理操作量大，复杂度高的数据。假设一种场景：你毕业了，学校的教务系统后台 MySQL 中，不在需要你的数据，要 删除你的基本信息( 姓名，电话，籍贯等 ) 的同时，也删除和你有关的其他信息，比如：你的各科成绩，你在校表现，甚至你在论坛发过的文章等。这就需要多条 MySQL 语句构成，那么所有这些操作合起来，就构成了一个事务。         正如我们上面所说，一个 MySQL 数据库，可不止你一个事务在运行，同一时刻，甚至有大量的请求被包装成事务，在向 MySQL 服务器发起事务处理请求。而每条事务至少一条 SQL ， 这样如果大家都访问同样的表数据，在不加保护的情况，就绝对会出现问题。甚至，因为事务由多条 SQL 构成，那么，也会存在执行到一半出错或者不想再执行的情况，那么已经执行的怎么办呢？ 所以一个完整的事务，绝对不是简单的 sql 集合，还需要满足如下四个属性：

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（ Read uncommitted ）、读提交（ read committed ）、可重复读（ repeatable read ）和串行化（ Serializable ）
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

上面四个属性，可以简称为 ACID 。

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

事务操作

查看数据库引擎

show engines;

我们可以看到在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务， MyISAM 不支持。

事务提交方式

事务的提交方式常见的有两种：自动提交，手动提交。 查看事务提交方式

show variables like 'autocommit';

用 SET 来改变 MySQL 的自动提交模式 禁止自动提交

set autocommit=0;
set autocommit=false;
set autocommit=off;

开启自动提交

set autocommit=1;
set autocommit=true;
set autocommit=on;

开始事务

--两种皆可
begin;
start transaction;

提交事务

commit;

一个完整的事务需要开始和提交。我们以下表为例，创建一个事务。

create table if not exists student(
id int primary key,
name varchar(10) not null default '',
tel varchar(20)
);

只要输入begin或者start transaction，事务便必须要通过commit提交，才会持久化，与是否设置set autocommit无关。

事务回滚

在事务执行过程中，可以设置保存点，保存点用于回滚。

savepoint 保存点名;

回滚用于在一个事务内部进行撤销操作，即取消之前的某些操作。

rollback to 保存点名;

我们回滚到p1后，可以发现少了两条数据。也可以直接使用rollback，直接回滚到事务的最开始。

事务可以手动回滚，同时，当操作异常 MySQL 会自动回滚，对于 InnoDB 每一条 SQL 语言都默认封装成事务，自动提交。（ select 有特殊情况，因为 MySQL 有 MVCC ）

事务隔离级别

MySQL 服务可能会同时被多个客户端进程 ( 线程 ) 访问，访问的方式以事务方式进行，一个事务可能由多条SQL 构成，也就意味着，任何一个事务，都有执行前，执行中，执行后的阶段。而所谓的原子性，其实就是让用户层，要么看到执行前，要么看到执行后。执行中出现问题，可以随时回滚。所以单个事务，对用户表现出来的特性，就是原子性。 但，毕竟所有事务都要有个执行过程，那么在多个事务各自执行多个 SQL 的时候，就还是有可能会 出现互相影响的情况。比如：多个事务同时访问同一张表，甚至同一行数据。就如同你妈妈给你说：你要么别学，要学就学到最好。至于你怎么学，中间有什么困难，你妈妈不关心。那么你的学习，对你妈妈来讲，就是原子的。那么你学习过程中，很容易受别人干扰，此时，就需要将你的学习隔离开，保证你的学习环境是健康的。

• 数据库中，为了保证事务执行过程中尽量不受干扰，就有了一个重要特征：隔离性
• 数据库中，允许事务受不同程度的干扰，就有了一种重要特征：隔离级别

在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同。

• 读未提交【Read Uncommitted】： 在该隔离级别，所有的事务都可以看到其他事务没有提交的执行结果。（实际生产中不可能使用这种隔离级别的），但是相当于没有任何隔离性，也会有很多并发问题，如脏读，幻读，不可重复读等，我们上面为了做实验方便，用的就是这个隔离性。
• 读提交【Read Committed】 ：该隔离级别是大多数数据库的默认的隔离级别（不是 MySQL 默认的）。它满足了隔离的简单定义:一个事务只能看到其他的已经提交的事务所做的改变。这种隔离级别会引起不可重复读，即一个事务执行时，如果多次 select， 可能得到不同的结果。
• 可重复读【Repeatable Read】： 这是 MySQL 默认的隔离级别，它确保同一个事务，在执行中，多次读取操作数据时，会看到同样的数据行，但是会有幻读问题。
• 串行化【Serializable】: 这是事务的最高隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决了幻读的问题。它在每个读的数据行上面加上共享锁，但是可能会导致超时和锁竞争（这种隔离级别太极端，实际生产基本不使用）。

查看隔离级别

select @@session.transaction_isolation;//查看当前对话的隔离级别
select @@transaction_isolation;//同上
select @@global.transaction_isolation;//查看全局默认的隔离级别

在MySQL中，事务的默认隔离级别是可重复读。

设置隔离级别

set [session | global] transaction isolation level {read uncommitted | read
committed | repeatable read | serializable};

例如，我们把当前对话的隔离级别设置为设置为读未提交read uncommitted。

读未提交【Read Uncommitted】

在读未提交隔离级别下，所有事务都可以看到其他事务未提交的执行结果，也就是相当于没有任何隔离性。

当前两个对话的隔离级别都为读未提交read uncommitted，我们在左边的终端中在表中插入，更新数据，但并未提交，右边查看表时也会发现其中数据的变化，一个事务在执行中，读到另一个执行中事务的更新(或其他操作)但是未commit的数据，这种现象叫做脏读。

读提交【Read Committed】

读提交是大多数数据库的默认隔离级别，一个事务只能看到其它事务已经提交的数据

当前两个对话的隔离级别都为读提交read committed，我们在左边的终端中在表中更新数据，但并未提交，右边查看表时也不会发现其中数据的变化，但提交之后就可以看到，但是，右边的终端此时还在当前事务中，并未commit，那么就造成了，同一个事务内，同样的读取，在不同的时间段 ，读取到了不同的值，这种现象叫做不可重复读(non reapeatable read)！！

可重复读【Repeatable Read】

可重复读是MySQL的默认隔离级别，它确保一个事务中，多次读取时不会出现不一样的数据

当前两个对话的隔离级别都为可重复读repeatable read，我们在左边的终端中在表中更新数据，提交后，右边查看表时也不会发现其中数据的变化，但是，右边的终端也commit后就可以看到了。但是，一般的数据库在可重复读情况的时候，无法屏蔽其他事务insert的数据(为什么？因为隔离性实现是对数据加锁完成的，而insert待插入的数据因为并不存在，那么一般加锁无法屏蔽这类问题),会造成虽然大部分内容是可重复读的，但是insert的数据在可重复读情况被读取出来，导致多次查找时，会多查找出来新的记录，就如同产生了幻觉。这种现象，叫做幻读(phantom read)。

串行化【serializable】

串行化是最高的隔离级别，会对事务进行排序，同时只允许一个事务执行

当前两个对话的隔离级别都为串行化serializable，我们可以在左边和右边的终端同时查看表，但是，左边的终端想插入数据就会被阻塞，直到右边的终端也commit后才可以插入。

MVCC

数据库并发的场景有三种：

• 读-读 ：不存在任何问题，也不需要并发控制
• 读-写 ：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
• 写-写 ：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

多版本并发控制（ MVCC ）是一种用来解决 读 - 写冲突 的 无锁并发控制。为事务分配单向增长的事务ID ，为每个修改保存一个版本，版本与事务 ID 关联，读操作只读该事务开始前的数据库的快照。 所以 MVCC 可以为数据库解决以下问题：在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能，还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题。

事务ID

事务ID(Transaction ID，简称TID)是数据库系统为每个事务分配的唯一标识符。它通常是一个递增的整数，由数据库系统自动生成和管理。每当一个新的事务开始时，数据库系统就会为其分配一个全局唯一的事务ID。这个ID在事务的整个生命周期中保持不变，直到事务结束。

隐藏列

在MySQL中，创建表的时候，并不是指定了哪些列，就存在哪些列，而是有几个所有表都存在的隐藏列，用于管理数据。

• DB_TRX_ID：记录最近修改(insert、delete、update)该行的事务ID
• DB_ROLL_PTR：回滚指针，指向历史版本undo log
• DB_ROW_ID：隐藏主键，如果表中没有主键，则会有一个隐藏主键
• ROW_DELETE：标识一个行有没有被删除，如果为0表示该行已经被删除，如果为1表示没有被删除

以以下表为例：

create table Stu(
name varchar(20),
age int
);

插入('张三', 18)，假设该事务的事务ID为123，至少会包含以下列：

对于这条新数据，是通过事务123完成的插入，DB_TRX_ID = 123，由于没有历史版本，回滚指针 = NULL，DB_ROW_ID则是隐藏主键，自行完成自增。ROW_DELETE = 0表示这一行是存在的。

undo log​​​​​​​

MySQL作为一款数据库，自然是要把数据进行持久化保存，但是插入，删除，查询这样的操作，是要把数据加载到内存中计算的。对于一个事务，只有整个事务结束，才会把数据写入硬盘保存，在写入硬盘之前，会在内存中开辟一段缓冲区，用于保存历史版本以便回滚，这个缓冲区就叫做undo log。

假设我们在一个事务中，将张三的名字改为李四，事务ID为199：

此时旧的数据会进入undo log保存起来，随后将name改为李四，这是用户表面上看到的操作。实际上还会更新事务ID为199，表示这条数据最后一次是事务199操作的，并且将回滚指针指向在undo log中上一个版本的数据。

接着同一个事务199又把这一行删掉：

用户看来，这一行数据已经被删掉了，实际上这行数据还在，只是ROW_DELETE = 1，而原先ROW_DELETE = 0的版本，被保存在了undo log中。目前事务199还没有commit，此时还可以进行操作，比如说回滚。如果想要回滚到删除数据前，由于undo log中有ROW_DELETE = 0的版本，此时可以直接通过回滚指针找到上一个版本，然后复原。

如果想要回滚到最开始，那么就从回滚指针一直往回找，直到找到NULL空指针为止。只要事务199不提交，那么这个undo log就会一直在内存中，方便进行版本控制。当事务提交后，这个事务对应的undo log就会被MySQL自动释放，这也就是为什么事务结束后不能再回滚。

read view

回到这张图，可以发现一个事务在操作数据时，会生成多个版本。而MySQL中事务往往是并发的，其它事务来读取数据的时候，会读取到哪一个版本的数据呢？

mvcc通过快照读机制，读取某个固定的版本。快照读基于读视图read view实现，在MySQL源码中，readview包含以下内容：

class ReadView {
 private:
  trx_id_t m_low_limit_id;   // 高水位
  trx_id_t m_up_limit_id;    // 低水位
  trx_id_t m_creator_trx_id; // 当前事务ID
  ids_t m_ids;               // 活跃事务ID
};

• m_ids：一个数组，存储当前所有活跃的事务ID
• m_creator_trx_id：记录当前事务的事务ID，即哪一个事务创建了该读视图
• m_low_limit_id：高水位，存储m_ids的最小值，低于该值的事务都已经提交
• m_up_limit_id：低水位，存储m_ids的最大值 + 1，大于等于该值的事务都是快照后产生的事务

当一个事务99进行读取时，会对当前整个MySQL生成一个读视图，读视图分为三部分

当事务创建完读视图后，读取数据时依照这个读视图判断读取哪一个版本。

• 已提交事务：

查询数据时，首先看DB_TRX_ID列，看上一次是哪一个事务对其进行了修改：

该数据的DB_TRX_ID = 92，小于m_low_limit_id = 98，说明是已经提交的事务，那么该数据是可见的，最后就可以读取到李四这条数据。

• 活跃事务：

该数据的DB_TRX_ID = 100，大于m_low_limit_id = 98，小于m_up_limit_id = 106，在m_ids中，说明是活跃的事务，那么该数据不可见。随后通过回滚指针找到上一个版本，发现上一个版本李四是已经提交的事务，所以最后看到李四这条数据。

• 后产生的事务：

该数据的DB_TRX_ID = 110，大于m_up_limit_id = 106，说明是后产生的事务，那么该数据不可见。随后通过回滚指针找到上一个版本，发现上一个版本李四是已经提交的事务，所以最后看到李四这条数据。

• 当前事务：

该数据DB_TRX_ID = 99，m_creator_trx_id = 99，说明就是事务自己上一次修改的数据。可见，读取到孙七。

每个事务创建快照的时机不同，那么看到的版本也就不同，如果在当前事务之后，有其他事务修改了数据，那么一定是活跃事务或者是后来的事务，此时当前事务会屏蔽掉这些内容，通过回滚指针找到自己可见的版本！

那么这个机制是如何划分出四个不同的隔离级别的呢？

读未提交：事务可以读取到其他未提交事务的修改。这个级别不使用MVCC，因此没有快照读，事务总是读取到最新的数据。

读提交：事务只能读取到其他事务已经提交的修改。在读已提交级别下，每次读取操作都会创建一个新的快照，确保读取到的数据是其他事务提交后的数据。

可重复读：整个事务只在第一次读取的时候创建一个快照，后续整个事务都使用同一张快照，保证前后读取到的内容一致，可重复读。

串行化：事务通过锁来避免并发问题，MVCC的作用非常有限，因为数据访问通常是被锁定的，直到事务完成。MVCC机制在可重复读和读已提交隔离级别中起作用，但在读未提交和串行化隔离级别中，其作用有限或不适用。

标签：回滚,隔离,事务,提交,MySQL,数据,ID
From： https://blog.csdn.net/2301_79881188/article/details/144327291