MySQL中的事务

我会带着你远行 2022-05-14 09:22 173阅读 0赞

## 事务 ##

**1.什么是事务?**

简单的讲事务就是管理一个包含多个步骤的业务,这个业务中的所有操作要么同时成功,要么同时失败.

**举例说明**  
例如一个转账业务,需要一方扣钱,另一方加钱.在此过程中,需要多次访问数据库才能完成.而当这个业务被事务管理之后,如果转账过程当中有一条SQL语句出现异常,则整个业务执行失败.  
下面演示说明:

-- 创建数据表
    CREATE TABLE account(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	NAME VARCHAR(20),
    	balance DOUBLE
    );
    
    -- 添加数据
    INSERT INTO  account VALUES(NULL,'张三',1000);
    INSERT INTO  account VALUES(NULL,'李四',1000);

现在张三转账给李四500元,有以下步骤

1.查询张三的余额是否大于500.  
2.张三账户余额-500.  
3.李四账户余额+500.

-- 查询张三账户金额是否大于 500 
    SELECT * FROM account WHERE balance > 500 AND NAME = '张三';
    
    -- 张三账户金额 -500
    UPDATE account SET balance = balance - 500 WHERE NAME = '张三';
    
    -- 李四账户金额 +500
    UPDATE account SET balance = balance + 500 WHERE NAME = '李四';
    
    SELECT * FROM account;

假如张三转给李四500元后,服务器崩溃,数据会怎样呢?  
我们手动制造问题,演示一下.

-- 查询张三账户金额是否大于 500 
    SELECT * FROM account WHERE balance > 500 AND NAME = '张三';
    
    -- 张三账户金额 -500
    UPDATE account SET balance = balance - 500 WHERE NAME = '张三';
    服务器崩溃
    
    -- 李四账户金额 +500
    UPDATE account SET balance = balance + 500 WHERE NAME = '李四';
    
    SELECT * FROM account;

![这里写图片描述][70]

此时我们发现张三转给李四的500元,李四并没有收到,数据出现了问题,所以这时我们就需要用到事务.

**事务的分类**  
MySQL中事务分为两类:

1.手动提交事务  
2.自动提交事务 MySQL中每一条SQL语句默认提交一次事务

手动提交事务的流程:

![这里写图片描述][70 1]

1 执行成功的情况： 开启事务 -> 执行多条 SQL 语句 -> 成功提交事务  
2 执行失败的情况： 开启事务 -> 执行多条 SQL 语句-> 事务的回滚  
事务回滚,回滚到开启事务前,数据表最初的状态.  
简单点就是回滚到距离开启事务后第一行SQL语句未执行的状态.

**案例演示–事务提交**

假如张三,李四,账户余额都是1000,模拟张三给李四转账500,  
步骤如下

1. 使用 DOS 控制台进入 MySQL;
    2. 执行以下 SQL 语句:
    
    START TRANSACTION; -- transaction
    -- 张三账户金额 -500
    UPDATE account SET balance = balance - 500 WHERE NAME = '张三';
    -- 李四账户金额 +500
    UPDATE account SET balance = balance + 500 WHERE NAME = '李四';
    
    3. 再开启一个SQLyog 查看数据库,会发现数据并没有改变
    4. 在控制台中执行 commit 提交事务.
    5. 使用另一个 SQLyog 查看数据库：发现数据改变.

![这里写图片描述][70 2]

**案例演示–事务回滚**

假如张三,李四,账户余额都是1000,模拟张三给李四转账500  
步骤如下

1. 在控制台执行以下 SQL 语句： 1.开启事务， 2.张三账号-500.
    2.使用 SQLYog 查看数据库：发现数据并没有改变.
    3. 在控制台执行 rollback 回滚事务：
    4. 使用 SQLYog 查看数据库：发现数据没有改变.

![这里写图片描述][70 3]

总结: **如果事务中 SQL 语句没有问题,使用commit 提交事务, 会对数据库数据的数据进行改变. 如果事务中 SQL语句有问题, rollback 回滚事务,会回退到开启事务时的状态.**

**自动提交事务**

MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕自动提交事务， MySQL 默认开始自动提交事务.

![这里写图片描述][70 4]

**案例演示–自动提交事务**

1. 将金额重置为 1000.
    2. 更新其中某一个账户.
    3. 使用 SQLYog 查看数据库：发现数据已经改变.

![这里写图片描述][70 5]

**取消自动提交**

查看 MySQL 是否开启自动提交事务
    select @@autocommit;
    @@代表全局变量  1 表示开启  0 表示关闭.
    
    取消自动提交事务
    set @@autocommit = 0;

**事务原理**  
事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表中，其他任何情况都会清空事务日志(rollback，断开连接).  
原理图:  
![这里写图片描述][70 6]

**事务的步骤**

1.客户端连接数据库服务器，创建连接时创建此用户临时日志文件.  
2.开启事务以后，所有的操作都会先写入到临时日志文件中.  
3.所有的查询操作从表中查询，但会经过日志文件加工后才返回.  
4.如果事务提交则将日志文件中的数据写到表中，否则清空日志文件.

**回滚点**

什么是回滚点?

在某些成功的操作完成之后， 后续的操作有可能成功有可能失败， 但是不管成功还是失败， 前面操作都已经成功， 可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置， 而不是返回所有操作， 这个点称之为回滚点。

回滚点的操作语句

设置回滚点 savepoint 名字
    回到回滚点 rollback to 名字

具体操作:

1.  将数据还原到 1000.
2.  开启事务.
3.  让张三账号减 3 次钱，每次 10 块
4.  设置回滚点： savepoint three\_times;
5.  让张三账号减 4 次钱，每次 10 块
6.  回到回滚点： rollback to three\_times;
7.  分析执行过程.

**总结：设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。**

**事务四大特性(ACID)**

![这里写图片描述][70 7]

**事务的隔离级别产生的问题**

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

**MySQL 数据库中的四种隔离级别**

1. read uncommitted：读未提交
     产生的问题：脏读、不可重复读、幻读
     
    2. read committed：读已提交 （Oracle）
    产生的问题：不可重复读、幻读
    
    3. repeatable read：可重复读 （MySQL默认）
    产生的问题：幻读
    
    4. serializable：串行化
    可以解决所有的问题
    注意：隔离级别从小到大安全性越来越高，但是效率越来越低
    
    数据库查询隔离级别：
    	 select @@tx_isolation;
    数据库设置隔离级别：
    	 set global transaction isolation level  级别字符串;

[70]: /images/20220514/b4fe6b45d53c433d81dcf3539cd77974.png
[70 1]: /images/20220514/d21c86405ab948baa573903c31305e73.png
[70 2]: /images/20220514/afcc8d3f99ae43f6b42c70823e343fe8.png
[70 3]: /images/20220514/12d55611340348c4abf39de9fabe6ba4.png
[70 4]: /images/20220514/ca992bf3b54949caab196fbc0c0a3ba2.png
[70 5]: /images/20220514/2830604d494a4666af2154a112364994.png
[70 6]: /images/20220514/b89d6adfac78433fb0ea3a2f8ba77d7c.png
[70 7]: /images/20220514/d2b720fc62c74b028e0c6ae36bea4848.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MjQxMDAz_size_16_color_FFFFFF_t_70]: /images/20220514/6f964bf3feff47a2acd5b7b738c7c2ce.png