MySQL总结(十二)数据库事务-详解

╰+攻爆jí腚メ 2023-02-24 11:17 1阅读 0赞

[精选30+云产品，助力企业轻松上云！>>> ![hot3.png][]][30_ _hot3.png]

![up-a344f41e81d3568e3310b5da00c57ced8ea.png][]

# 数据库事务 #

## 1. 事务的应用场景说明 ##

*  什么是事务?

> 在实际的开发过程中，一个业务操作如：转账，往往是要多次访问数据库才能完成的。转账是一个用户扣钱，另一个用户加钱。如果其中有一条 SQL 语句出现异常，这条 SQL 就可能执行失败。
> 
> 事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常，则所有的SQL 语句都要回滚，整个业务执行失败。

*  转账操作

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

模拟张三给李四转 500 元钱，一个转账的业务操作最少要执行下面的 2 条语句：

-- 张三账号-500
    update account set balance = balance - 500 where name='张三';
    -- 李四账号+500
    update account set balance = balance + 500 where name='李四';

假设当张三账号上-500 元,服务器崩溃了。李四的账号并没有+500 元，数据就出现问题了。我们需要保证其中一条 SQL 语句出现问题，整个转账就算失败。只有两条 SQL 都成功了转账才算成功。这个时候就需要用到事务。

## 2. 手动提交事务 ##

Mysql中可以有两种方式进行事务的操作:

*  1.  手动提交事务
 *  1.  自动提交事务

### 2.1 手动提交事务的SQL语句 ###

<table> 
 <thead> 
  <tr> 
   <th>功能</th> 
   <th>SQL语句</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>开启事务</td> 
   <td>start transaction;</td> 
  </tr> 
  <tr> 
   <td>提交事务</td> 
   <td>commit;</td> 
  </tr> 
  <tr> 
   <td>回滚事务</td> 
   <td>rollback;</td> 
  </tr> 
 </tbody> 
</table>

### 2.2 手动提交事务使用过程 ###

*  1)执行成功的情况: 开启事务 -> 执行多条 SQL 语句 -> 成功提交事务
 *  2)实行失败的情况: 开启事务 -> 执行多条 SQL 语句 -> 事务的回滚

![up-0fafe5f1b220a328935d02c97d94503255e.png][]

### 2.3 案例演示(一):事务提交 ###

模拟张三给李四转 500 元钱（成功） 目前数据库数据如下：

![up-557c40e14d5e56ae35f36a3ee2a5e9fa984.png][]

*  1)使用 DOS 控制台进入 MySQL
 *  2)执行以下 SQL 语句： 1.开启事务， 2.张三账号-500， 3.李四账号+500
 *  3)使用 SQLYog 查看数据库：发现数据并没有改变
 *  4)在控制台执行 commit 提交事务
 *  5)使用 SQLYog 查看数据库：发现数据改变

![up-42e4c611cedac71008a6153cd4b60a7e99b.png][]

### 2.3 案例演示(二):事务回滚 ###

模拟张三给李四转 500 元钱（失败） 目前数据库数据如下：

![up-4f21a6b8f403400a9780cb22f7ad6b8e13b.png][]

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

![up-ac276771b3d6e136621b9c10e287380f3ce.png][]

### 2.4 总结 ###

如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。 如果事务中 SQL语句有问题，rollback 回滚事务，会回退到开启事务时的状态。

## 3. 自动提交事务 ##

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

### 3.1 案例演示(三):自动提交事务 ###

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

![up-20c1dba920b2653b4045b2f31ca93b0539a.png][]

![up-9174fe015aa11ab118a809ba32a3d6a366c.png][]

### 3.2 取消自动提交 ###

*  查看 MySQL 是否开启自动提交事务

@@表示全局变量，1 表示开启，0 表示关闭

![up-61487ba26c54976d3013b4ec49eabdc2fa6.png][]

*  取消自动提交事务

![up-93b9e83b227b1e81b5d2c35f33337106904.png][]

*  执行更新语句，使用 SQLYog 查看数据库，发现数据并没有改变
 *  在控制台执行commit提交事务

![up-f02844eed75dc0fef6d435b42165bc81b39.png][]

## 4. 事务原理 ##

> 事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表中，其他任何情况都会清空事务日志(rollback，断开连接)

### 4.1 原理图 ###

![up-8a1827b8fbbf814f6c1f80659727d2ef7f7.png][]

### 4.2 事务的步骤 ###

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

## 5. 回滚点 ##

### 5.1 什么是回滚点 ###

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

### 5.2 回滚点的操作语句 ###

<table> 
 <thead> 
  <tr> 
   <th>操作语句</th> 
   <th>语句</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>设置回滚点</td> 
   <td>savepoint 名字</td> 
  </tr> 
  <tr> 
   <td>回到回滚点</td> 
   <td>rollback to 名字</td> 
  </tr> 
 </tbody> 
</table>

### 5.3 具体操作 ###

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

### 5.4 回滚点总结 ###

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

## 6. 事务隔离级别 ##

事务隔离级别在下篇进行详解

# OK 这篇就到这里 #

[hot3.png]: /images/20230209/5bee0624fd534ec48dc058bc958788ae.png
[30_ _hot3.png]: https://www.oschina.net/action/visit/ad?id=1221
[up-a344f41e81d3568e3310b5da00c57ced8ea.png]: https://oscimg.oschina.net/oscnet/up-a344f41e81d3568e3310b5da00c57ced8ea.png
[up-0fafe5f1b220a328935d02c97d94503255e.png]: https://oscimg.oschina.net/oscnet/up-0fafe5f1b220a328935d02c97d94503255e.png
[up-557c40e14d5e56ae35f36a3ee2a5e9fa984.png]: https://oscimg.oschina.net/oscnet/up-557c40e14d5e56ae35f36a3ee2a5e9fa984.png
[up-42e4c611cedac71008a6153cd4b60a7e99b.png]: https://oscimg.oschina.net/oscnet/up-42e4c611cedac71008a6153cd4b60a7e99b.png
[up-4f21a6b8f403400a9780cb22f7ad6b8e13b.png]: https://oscimg.oschina.net/oscnet/up-4f21a6b8f403400a9780cb22f7ad6b8e13b.png
[up-ac276771b3d6e136621b9c10e287380f3ce.png]: https://oscimg.oschina.net/oscnet/up-ac276771b3d6e136621b9c10e287380f3ce.png
[up-4c914caabb3c2ff97d9433a0634a5cc61b0.png]: https://oscimg.oschina.net/oscnet/up-4c914caabb3c2ff97d9433a0634a5cc61b0.png
[up-20c1dba920b2653b4045b2f31ca93b0539a.png]: https://oscimg.oschina.net/oscnet/up-20c1dba920b2653b4045b2f31ca93b0539a.png
[up-9174fe015aa11ab118a809ba32a3d6a366c.png]: https://oscimg.oschina.net/oscnet/up-9174fe015aa11ab118a809ba32a3d6a366c.png
[up-61487ba26c54976d3013b4ec49eabdc2fa6.png]: https://oscimg.oschina.net/oscnet/up-61487ba26c54976d3013b4ec49eabdc2fa6.png
[up-93b9e83b227b1e81b5d2c35f33337106904.png]: https://oscimg.oschina.net/oscnet/up-93b9e83b227b1e81b5d2c35f33337106904.png
[up-f02844eed75dc0fef6d435b42165bc81b39.png]: https://oscimg.oschina.net/oscnet/up-f02844eed75dc0fef6d435b42165bc81b39.png
[up-8a1827b8fbbf814f6c1f80659727d2ef7f7.png]: https://oscimg.oschina.net/oscnet/up-8a1827b8fbbf814f6c1f80659727d2ef7f7.png