【MySQL】事物机制

柔光的暖阳◎ 2022-10-22 08:00 170阅读 0赞

### 事务机制 ###

*  1. 事务的概念与特征
 *  2. 关闭MySQL中的自动提交
 *  3. 回滚与提交（rollback and commit）
 *  4. 保存点（savepoint）
 *  5. 事务的流程图
 *  6. 事务的ACID特性
 *  7. 事务的隔离级别与并发问题
 *   *  7.1 标准事务隔离级别
     *  7.2 事务并发问题
     *  7.3 隔离级别与并发问题对应表
     *  7.4 设置事务的隔离级别

# 1. 事务的概念与特征 #

*  事务的概念：事务由一系列`更新操作`组成，这些更新操作是一个`不可分割的逻辑工作单元`。
 *  事务的特征：事务中的更新操作要么`全部执行成功`，要么`都不执行`。
 *  `MyISAM存储引擎`暂`不支持`事务，`InnoDB存储引擎`支持事物

> 事务的`原子性`：如果事务成功执行，那么事务中的所有的更新操作都会成功执行，并将执行结果提交到数据库文件中，称为数据库永久的组成成分。如果事务中的某个更新操纵执行失败，那么事务中的所有更新操作均被撤销。  
> `更新操作`：update/insert/delete等语句

# 2. 关闭MySQL中的自动提交 #

默认情况下，MySQL开启了`自动提交`，即某条更新语句一旦执行成功，MySQL服务实例会立即将执行结果提交到数据库文件当中，成为数据库永久的组成部分。

`方法一：显式关闭自动提交`

查看 autocommit 状态

show variables like ‘autocommit‘;

> 系统变量 autocommit 值为 ON 或 1 表示开启自动提交  
> 系统变量 autocommit 值为 OFF 或 0 表示关闭自动提交  
> 设置 autocommit 状态

set autocommit=0;

> 对于MyISAM存储引擎来说，自动提交无论开启还是关闭，更新操作都将立即解析、执行，并将执行结果立即提交至数据库文件中，成为数据库永久的组成部分  
> `方法二：隐式关闭自动提交`

start transaction;

> 该SQL命令会隐式关闭自动提交，但是，隐式关闭自动提交并不会修改系统变量 autocommit 的值  
> 改种方式为推荐方式，改种方式不仅可以开启新的事物，还可以在不影响系统变量的情况下关闭自动提交

# 3. 回滚与提交（rollback and commit） #

MySQL客户机A关闭自动提交后，客户机A的所有更新操作，都会在MySQL服务器内存中产生若干条 new 记录，这些 new 记录，并不会立即写入数据库文件中，虽然客户机A能够看到字段更新后的值，但是其他MySQL客户机看不到更新后的值。此时，客户机有两个选择：一是执行回滚，二是执行提交

rollback;

commit;

> 执行 rollback 后，MySQL服务器中，与客户机A对应的new记录都将被丢弃，此时，客户机A和客户机B看到的都是字段为原来的值  
> 执行 commit 后，MySQL服务器将new记录更新提交到数据库文件中，成为数据库永久的组成部分，此时，客户机A和客户机B看到的字段为更新后的值

# 4. 保存点（savepoint） #

*  默认情况下，事务一旦回滚，事务内的所有更新操作都将被撤销
 *  某些情况下，只想撤销一部分更新操作，此时就可以使用`保存点`来实现`部分更新提交`与`部分更新撤销`

> 以下SQL命令，可以在事务中`设置`一个保存点

savepoint 保存点名;

> 以下SQL命令，可以将事务`回滚`到保存点状态

roolback to savepoint 保存点名;

> 以下SQL命令，可以`删除`一个事务的保存点，若该保存点不存在则会出现错误信息

release to savepoint 保存点名;

事务回滚到savepoint仅仅是让数据库回到事务中的某个“一致性状态”，而这个“一致性状态”并没有将之前的更新回滚，也没有将之前的更新提交。提交还是得依靠 commit 命令

# 5. 事务的流程图 #

![事物流程图][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTQzNzAyMg_size_16_color_FFFFFF_t_70]

# 6. 事务的ACID特性 #

*  `原子性`（atomicity）：一个事务的执行只有两种结果：要么全部完成并写入外存，要么在执行过程中出错产生事务回滚，返回至事务开始前的状态。
 *  `一致性`（consistency）：一个事务的状态只有两个：事务提交导致从一种一致性状态到另一种一致性状态，事务回滚导致从一种一致性状态到另一种一致性状态。
 *  `隔离性`（isolation）：一个事务的执行不能被其他事务干扰，基于原子性和一致性，事务可以并发执行。
 *  `持久性`（durability）：当一个事务提交后，数据库状态永远的发生了改变，数据已经更新到了外存中。

> 事务的`隔离性`是通过事务的`隔离级别`实现的，而事务的隔离级别则是通过`锁机制`实现的。

# 7. 事务的隔离级别与并发问题 #

## 7.1 标准事务隔离级别 ##

事务的隔离级别是事务并发控制的整体解决方案，SQL标准定义了 4 种隔离级别：

*  `读取未提交的数据`（read uncommitted）：所有事务都可以看到其他`未提交事务的执行结果`
 *  `读取提交的数据`（read committed）：一个事务职能看见`已提交的事务`所做的改变
 *  `可重复读`（repeatable read）：确保同一事务内`相同的查询语句的执行结果一致`，该级别是MySQL默认的事务隔离级别
 *  `串行化`（serializable）：给每条查询语句加上共享锁，强制事务排序，使之不可能冲突，该级别可能会导致大量的`锁等待`现象。  
    `低级别的事务隔离`可以`提高`事务的`并发访问性能`，但会`导致`很多`并发问题`  
    `高级别的事务隔离`可以`解决`很多`并发问题`，但会`降低`事务的`并发访问性能`

## 7.2 事务并发问题 ##

*  `脏读`（dirty read）：一个事务可以读取到另一个事务未提交的数据。

> 例如：客户机A更新字段X的值，但是`还没有提交或回滚`，但是客户机B已经能够读取到字段X更新后的值了，客户机B就是脏读数据

*  `不可重复读`（non-repeatable read）：同一个事务内，两条相同的查询语句的查询结果不一致

> 例如：客户机A更新字段X的值，但是还没有提交或回滚，此时打开客户机B第一次查询字段X的值，客户机B读取到的值是原始未修改的值，但是当客户机A`提交更新操作`，那么客户机B第二次查询字段X的值，将与第一次查询的结果不一样，则造成了不可重复读的现象

*  `幻读`（phantom read）：同一个事务内，两条相同的查询语句应该相同，但是，如果另一个事务同时插入了新的记录，当本事务更新时，出现了后一次查询出现了新的记录，这就导致了幻读。

> 例如：客户机A插入新记录，但是`还没有提交或回滚`，此时打开客户机B此一次查询该表的记录，客户机B读取到的表记录为原始状态的表记录，但是当客户机A提交后，客户机B第二次查询该表的记录，依然查不到新增加的表记录，但是客户机B将新记录再次插入，会发生无法插入的现象，说明新记录已经存在，客户机B查询不到的现象，这就是幻读现象。

脏读问题对应的是`update`操作：事务`未提交`，前后查询`字段`结果不一致  
不可重复对应的是`update`操作：事务`提交`，前后查询`字段`结果不一致  
幻读问题对应的是`insert`操作：事务`提交`，前后查询`记录`结果一致，新记录无法被查到

> 不可重复读 和 幻读 现象`不同之处`在于：  
> 幻读现象 `读不到`其他事务已经提交的数据  
> 不可重复读现象 `读到`的是其他事务已经提交的数据

## 7.3 隔离级别与并发问题对应表 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTQzNzAyMg_size_16_color_FFFFFF_t_70 1]

✅代表该隔离级别`具有`该并发问题  
❌代表该隔离级别`解决`了该并发问题

## 7.4 设置事务的隔离级别 ##

> 以下SQL命令，可以查询当前MySQL会话的事务隔离级别（老版本MySQL）

select @@session.tx_isolation;	  -- 查看MySQL会话的事务隔离级别
    select @@global.tx_isolation;    -- 查看MySQL服务实例全局的事务隔离级别

MySQL 8查询方法：

select @@session.transaction_isolation;
    Select @@global.transaction_isolation;

> 以下SQL命令，可以设置事务的隔离级别

set { global | session } transaction isolation level 
    { read uncommitted | read committed | repeatable read | seralizable }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTQzNzAyMg_size_16_color_FFFFFF_t_70]: /images/20221022/6dfc192cf11e4890a4e4c242038aa9ff.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTQzNzAyMg_size_16_color_FFFFFF_t_70 1]: /images/20221022/ca2df2e984594b8b92dff24176a98a99.png