mysql高级——事务

何处望天明CS

已于 2022-10-24 15:03:41 修改

阅读量339

点赞数

分类专栏： mysql 文章标签： mysql 数据库 java 1024程序员节

于 2022-10-23 19:17:03 首次发布

本文链接：https://blog.csdn.net/weixin_43752912/article/details/127476508

版权

mysql 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

1.数据库事务概述

事务是数据库区别于文件系统的重要特性之一，当我们有了事务就会让数据库始终保持一致性，同时我们还能通过事务的机制恢复到某个时间点，这样可以保证已提交到数据库的修改不会因为系统崩溃而丢失。

1.1 存储引擎支持情况

SHOW ENGINES 命令来查看当前 MySQL 支持的存储引擎都有哪些，以及这些存储引擎是否支持事务。
在这里插入图片描述

1.2 基本概念

事务:一组逻辑操作单元，使数据从一种状态变换到另一种状态。
事务处理的原则:保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操时，要么所有的事务都被提交( commit)，那么这些修改就永久地保存下来;要么数据库管理系统将放弃所作的所有修改，整个事务回滚( rollback )到最初状态。

1.3 事物的ACID特性

原子性（atomicity）：

原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。即要么转账成功，要么转账失败，是不存在中间的状态。如果无法保证原子性会怎么样？就会出现数据不一致的情形，A账户减去100元，而B账户增加100元操作失败，系统将无故丢失100元。

一致性（consistency）：

（国内很多网站上对一致性的阐述有误，具体你可以参考 Wikipedia 对Consistency的阐述）

根据定义，一致性是指事务执行前后，数据从一个 合法性状态 变换到另外一个 合法性状态 。这种状态是 语义上 的而不是语法上的，跟具体的业务有关。

那什么是合法的数据状态呢？满足 预定的约束 的状态就叫做合法的状态。通俗一点，这状态是由你自己来定义的（比如满足现实世界中的约束）。满足这个状态，数据就是一致的，不满足这个状态，数据就是不一致的！如果事务中的某个操作失败了，系统就会自动撤销当前正在执行的事务，返回到事务操作之前的状态。

举例1：A账户有200元，转账300元出去，此时A账户余额为-100元。你自然就发现此时数据是不一致的，为什么呢？因为你定义了一个状态，余额这列必须>=0。

举例2：A账户有200元，转账50元给B账户，A账户的钱扣了，但是B账户因为各种意外，余额并没有增加。你也知道此时的数据是不一致的，为什么呢？因为你定义了一个状态，要求A+B的总余额必须不变。

举例3：在数据表中我们将姓名字段设置为唯一性约束，这时当事务进行提交或者事务发生回滚的时候，如果数据表的姓名不唯一，就破坏了事物的一致性要求。

隔离型（isolation）：

事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰。

如果无法保证隔离性会怎么样？假设A账户有200元，B账户0元。A账户往B账户转账两次，每次金额为50 元，分别在两个事务中执行。如果无法保证隔离性，会出现下面的情形：

UPDATE accounts SET money = money - 50 WHERE NAME = 'AA';
UPDATE accounts SET money = money + 50 WHERE NAME = 'BB';

在这里插入图片描述

持久性（durability）：

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

持久性是通过 事务日志 来保证的。日志包括了 重做日志 和 回滚日志 。当我们通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行，从而使事务具有持久性。

总结

ACID是事务的四大特征，在这四个特性中，原子性是基础，隔离性是手段，一致性是约束条件，而持久性是我们的目的。

数据库事务，其实就是数据库设计者为了方便起见，把需要保证原子性、隔离性、一致性和持久性的一个或多个数据库操作称为一个事务。

1.4 事务的状态

我们现在知道 事务 是一个抽象的概念，它其实对应着一个或多个数据库操作，MySQL根据这些操作所执行的不同阶段把 事务 大致划分成几个状态：

活动的（active）

事务对应的数据库操作正在执行过程中时，我们就说该事务处在 活动的 状态。
部分提交的（partially committed）

当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并 没有刷新到磁盘 时，我们就说该事务处在 部分提交的 状态。
失败的（failed）

当事务处在 活动的 或者部分提交的状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在失败的状态。
中止的（aborted）

如果事务执行了一部分而变为 失败的 状态，那么就需要把已经修改的事务中的操作还原到事务执行前的状态。换句话说，就是要撤销失败事务对当前数据库造成的影响。我们把这个撤销的过程称之为 回滚 。当 回滚 操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处在了 中止的 状态。

举例：
```
UPDATE accounts SET money = money - 50 WHERE NAME = 'AA';

UPDATE accounts SET money = money + 50 WHERE NAME = 'BB';
```
提交的（committed）

当一个处在 部分提交的 状态的事务将修改过的数据都 同步到磁盘 上之后，我们就可以说该事务处在了 提交的 状态。

一个基本的状态转换图如下所示：

在这里插入图片描述

图中可见，只有当事物处于提交的或者中止的状态时，一个事务的生命周期才算是结束了。对于已经提交的事务来说，该事务对数据库所做的修改将永久生效，对于处于中止状态的事物，该事务对数据库所做的所有修改都会被回滚到没执行该事物之前的状态。

2. 如何使用事务

使用事务有两种方式，分别为 显式事务 和 隐式事务 。

2.1 显式事务

步骤1： START TRANSACTION 或者 BEGIN ，作用是显式开启一个事务。

mysql> BEGIN;
#或者
mysql> START TRANSACTION;

START TRANSACTION 语句相较于 BEGIN 特别之处在于，后边能跟随几个 修饰符 ：

① READ ONLY ：标识当前事务是一个 只读事务 ，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。

补充：只读事务中只是不允许修改那些其他事务也能访问到的表中的数据，对于临时表来说（我们使用 CREATE TMEPORARY TABLE 创建的表），由于它们只能再当前会话中可见，所有只读事务其实也是可以对临时表进行增、删、改操作的。

② READ WRITE ：标识当前事务是一个 读写事务 ，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。

③ WITH CONSISTENT SNAPSHOT ：启动一致性读。

比如：

START TRANSACTION READ ONLY; # 开启一个只读事务

START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT # 开启只读事务和一致性读

START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT # 开启读写事务和一致性读

注意：

READ ONLY和READ WRITE是用来设置所谓的事物访问模式的，就是以只读还是读写的方式来访问数据库中的数据，一个事务的访问模式不能同时即设置为只读的也设置为读写的，所以不能同时把READ ONLY和READ WRITE放到START TRANSACTION语句后边。
如果我们不显式指定事务的访问模式，那么该事务的访问模式就是读写模式

**步骤2：**一系列事务中的操作（主要是DML，不含DDL）

**步骤3：**提交事务或中止事务（即回滚事务）

# 提交事务。当提交事务后，对数据库的修改是永久性的。
mysql> COMMIT;

# 回滚事务。即撤销正在进行的所有没有提交的修改
mysql> ROLLBACK;

# 将事务回滚到某个保存点。
mysql> ROLLBACK TO [SAVEPOINT]

其中关于SAVEPOINT相关操作有：

# 在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。
SAVEPOINT 保存点名称;

# 删除某个保存点
RELEASE SAVEPOINT 保存点名称;

2.2 隐式事务

MySQL中有一个系统变量 autocommit ：

mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    |  ON   |
+---------------+-------+
1 row in set (0.01 sec)

当然，如果我们想关闭这种 自动提交 的功能，可以使用下边两种方法之一：

显式的的使用 START TRANSACTION 或者 BEGIN 语句开启一个事务。这样在本次事务提交或者回滚前会暂时关闭掉自动提交的功能。
把系统变量 autocommit 的值设置为 OFF ，就像这样：
```
SET autocommit = OFF;
#或
SET autocommit = 0;
```

2.3 隐式提交数据的情况

数据定义语言（Data definition language，缩写为：DDL）

数据库对象，指的就是数据库、表、视图、存储过程等结构。当我们CREATE、ALTER、DROP等语句去修改数据库对象时，就会隐式的提交前边语句所属于的事物。即：
```
BEGIN;

SELECT ... # 事务中的一条语句
UPDATE ... # 事务中的一条语句
... # 事务中的其他语句

CREATE TABLE ... # 此语句会隐式的提交前边语句所属于的事务
```
隐式使用或修改mysql数据库中的表

当我们使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD等语句时也会隐式的提交前边语句所属于的事务。
事务控制或关于锁定的语句

① 当我们在一个事务还没提交或者回滚时就又使用 START TRANSACTION 或者 BEGIN 语句开启了另一个事务时，会隐式的提交上一个事务。即：
```
BEGIN;

SELECT ... # 事务中的一条语句
UPDATE ... # 事务中的一条语句
... # 事务中的其他语句

BEGIN; # 此语句会隐式的提交前边语句所属于的事务
```
② 当前的 autocommit 系统变量的值为 OFF ，我们手动把它调为 ON 时，也会隐式的提交前边语句所属的事务。

③ 使用 LOCK TABLES 、 UNLOCK TABLES 等关于锁定的语句也会隐式的提交前边语句所属的事务。
加载数据的语句

使用LOAD DATA语句来批量往数据库中导入数据时，也会隐式的提交前边语句所属的事务。
关于MySQL复制的一些语句

使用START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等语句会隐式的提交前边语句所属的事务
其他的一些语句

使用ANALYZE TABLE、CACHE INDEX、CAECK TABLE、FLUSH、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET等语句也会隐式的提交前边语句所属的事务。