Mysql事务[学习笔记]

向右看齐 2023-02-21 09:13 17阅读 0赞

# 一、事务的应用场景说明 #

## 什么是事务： ##

在实际的开发过程中，一个业务操作如：转账，往往是要多次访问数据库才能完成的。转账是一个用户扣钱，另一个用户加钱。如果其中有一条 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  
李四账号+500

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

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

# 二、手动提交事务 #

MYSQL 中可以有两种方式进行事务的操作：  
(1) 手动提交事务  
(2) 自动提交事务

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

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

**手动提交事务使用过程：**

1.  执行成功的情况： 开启事务 → 执行多条 SQL 语句 →成功提交事务
2.  执行失败的情况： 开启事务 →执行多条 SQL 语句 → 事务的回滚  
    ![在这里插入图片描述][20200629213936691.png]

案例演示 1：事务提交  
模拟张三给李四转 500 元钱（成功） 目前数据库数据如下：  
![在这里插入图片描述][20200629213951207.png]

1.  使用 DOS 控制台进入 MySQL
2.  执行以下 SQL 语句： 1.开启事务， 2.张三账号-500， 3.李四账号+500
3.  使用 SQLYog 查看数据库：发现数据并没有改变
4.  在控制台执行 commit 提交事务：
5.  使用 SQLYog 查看数据库：发现数据改变  
    ![在这里插入图片描述][20200629214009800.png]

案例演示 2：事务回滚  
模拟张三给李四转 500 元钱（失败） 目前数据库数据如下：

1.  在控制台执行以下 SQL 语句：1.开启事务， 2.张三账号-500
2.  使用 SQLYog 查看数据库：发现数据并没有改变
3.  在控制台执行 rollback 回滚事务：
4.  使用 SQLYog 查看数据库：发现数据没有改变  
    ![在这里插入图片描述][2020062923074396.png]

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

## ②自动提交事务 ##

MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕自动提交事务，MySQL 默认开始自动提交事务  
![在这里插入图片描述][2020062923081141.png]

案例演示 3：自动提交事务

1.  将金额重置为 1000
2.  更新其中某一个账户  
    ![在这里插入图片描述][20200629230840243.png]
3.  使用 SQLYog 查看数据库：发现数据已经改变

**取消自动提交**

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

![在这里插入图片描述][20200629231543841.png]  
@@表示全局变量，1 表示开启，0 表示关闭

*  取消自动提交事务  
    ![在这里插入图片描述][2020062923162989.png]
 *  执行更新语句，使用 SQLYog 查看数据库，发现数据并没有改变  
    ![在这里插入图片描述][20200629231648806.png]
 *  在控制台执行 commit 提交任务

# 三、事务原理 #

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

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

# 四、回滚点 #

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

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

（三）具体操作：

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

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

# 六、事务的隔离级别 #

（一）事务的四大特性 ACID

<table> 
 <thead> 
  <tr> 
   <th>事务特性</th> 
   <th><strong>含义</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>原子性（<strong><strong>Atomicity</strong></strong>）</strong></td> 
   <td><strong>每个事务都是一个整体，不可再拆分，事务中所有的</strong> <strong>SQL</strong> <strong>语句要么都执行成功，</strong> <strong>要么都失败。</strong></td> 
  </tr> 
  <tr> 
   <td><strong>一致性（<strong><strong>Consistency</strong></strong>）</strong></td> 
   <td><strong>事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2</strong> <strong>个人的</strong> <strong>总金额是</strong> <strong>2000</strong>，转账后<strong>2</strong> <strong>个人总金额也是</strong> <strong>2000</strong></td> 
  </tr> 
  <tr> 
   <td><strong>隔离性（<strong><strong>Isolation</strong></strong>）</strong></td> 
   <td><strong>事务与事务之间不应该相互影响，执行时保持隔离的状态。</strong></td> 
  </tr> 
  <tr> 
   <td><strong>持久性（<strong><strong>Durability</strong></strong>）</strong></td> 
   <td><strong>一旦事务执行成功，对数据库的修改是持久的。就算关机，也是保存下来的。</strong></td> 
  </tr> 
 </tbody> 
</table>

（二）事务的隔离级别

<table> 
 <thead> 
  <tr> 
   <th>**并发访问的问题 **</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>脏读</strong></td> 
   <td><strong>一个事务读取到了另一个事务中尚未提交的数据</strong></td> 
  </tr> 
  <tr> 
   <td><strong>不可重复读</strong></td> 
   <td><strong>一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这</strong> <strong>是事务</strong> <strong>update</strong> <strong>时引发的问题</strong></td> 
  </tr> 
  <tr> 
   <td><strong>幻读</strong></td> 
   <td><strong>一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致</strong> <strong>的，这是</strong> <strong>insert</strong> <strong>或</strong> <strong>delete</strong> <strong>时引发的问题</strong></td> 
  </tr> 
 </tbody> 
</table>

（三）MySQL 数据库有四种隔离级别  
上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。

<table> 
 <thead> 
  <tr> 
   <th>级别</th> 
   <th>名字</th> 
   <th>隔离级别</th> 
   <th>脏读</th> 
   <th>不可重复读</th> 
   <th>幻读</th> 
   <th>数据库默认隔离级别</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>1</strong></td> 
   <td>读未提交</td> 
   <td>read uncommitted</td> 
   <td>是</td> 
   <td>是</td> 
   <td>是</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td><strong>2</strong></td> 
   <td>读已提交</td> 
   <td>read committed</td> 
   <td>否</td> 
   <td>是</td> 
   <td>是</td> 
   <td><strong>Oracle</strong> <strong>和</strong> <strong>SQL Server</strong></td> 
  </tr> 
  <tr> 
   <td><strong>3</strong></td> 
   <td>可重复读</td> 
   <td>repeatable read</td> 
   <td>否</td> 
   <td>否</td> 
   <td>是</td> 
   <td><strong>MySQL</strong> <img src="https://img-blog.csdnimg.cn/20200629232956380.png" alt="在这里插入图片描述"></td> 
  </tr> 
  <tr> 
   <td><strong>4</strong></td> 
   <td><strong>串行化</strong></td> 
   <td><strong>serializable</strong></td> 
   <td><strong>否</strong></td> 
   <td><strong>否</strong></td> 
   <td><strong>否</strong></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

隔离级别越高，性能越差，安全性越高。

（四）MySQL 事务隔离级别相关的命令

查询隔离级别	select @@tx_isolation;

*  查询全局事务隔离级别
 *  设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化

设置隔离级别	set global transaction isolation level 级别字符串;

（五）脏读的演示  
将数据进行恢复：UPDATE account SET balance = 1000;

1.  打开 A 窗口登录 MySQL，设置全局的隔离级别为最低

mysql -uroot -proot
    set global transaction isolation level read uncommitted;

![在这里插入图片描述][20200629233242479.png]  
2. 打开 B 窗口,AB 窗口都开启事务

use day23;
    start transaction;

![在这里插入图片描述][20200629233308735.png]  
3. A 窗口更新 2 个人的账户数据，未提交

update account set balance=balance-500 where id=1; update account set balance=balance+500

![在这里插入图片描述][20200629233325153.png]

1.  B 窗口查询账户

select * from account;

![在这里插入图片描述][20200629233344926.png]  
5. A 窗口回滚

rollback;

![在这里插入图片描述][20200629233418673.png]  
6. B 窗口查询账户，钱没了  
![在这里插入图片描述][20200629233435358.png]  
脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入 500 块，然后打电话给李四说钱已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。

解决脏读的问题：将全局的隔离级别进行提升将数据进行恢复：

UPDATE account SET balance = 1000;

1.  在 A 窗口设置全局的隔离级别为 read committed

set global transaction isolation level read committed;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTg2_size_16_color_FFFFFF_t_70 1]  
B 窗口退出 MySQL，B 窗口再进入 MySQL  
![在这里插入图片描述][20200629233519550.png]  
![在这里插入图片描述][2020062923353636.png]

AB 窗口同时开启事务

1.  A 更新 2 个人的账户，未提交

update account set balance=balance-500 where id=1; 
    update account set balance=balance+500

![在这里插入图片描述][20200629233633333.png]

1.  B 窗口查询账户  
    ![在这里插入图片描述][20200629233652738.png]  
    A 窗口 commit 提交事务  
    ![在这里插入图片描述][20200629233701929.png]
2.  B 窗口查看账户  
    ![在这里插入图片描述][20200629233721464.png]

结论：read committed 的方式可以避免脏读的发生

（六）不可重复读的演示

UPDATE account SET balance = 1000;

将数据进行恢复：

1.  开启 A 窗口

set global transaction isolation level read committed;

![在这里插入图片描述][20200629233827466.png]  
2. 开启 B 窗口，在 B 窗口开启事务

start transaction; 
    select * from account;

![在这里插入图片描述][20200629233844684.png]  
3. 在 A 窗口开启事务，并更新数据

start transaction;
    update account set balance=balance+500 where id=1; 
    commit;

![在这里插入图片描述][2020062923392043.png]

1.  B 窗口查询

select * from account;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTg2_size_16_color_FFFFFF_t_70 2]  
两次查询输出的结果不同，到底哪次是对的？不知道以哪次为准。 很多人认为这种情况就对了，无须困惑， 当然是后面的为准。我们可以考虑这样一种情况，比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户，结果在一个事务中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了。

**解决不可重复读的问题：**

将全局的隔离级别进行提升为：repeatable read

UPDATE account SET balance = 1000;

将数据进行恢复：

1.  A 窗口设置隔离级别为：repeatable read

set global transaction isolation level repeatable read;

![在这里插入图片描述][20200629234023337.png]  
2. B 窗口退出 MySQL，B 窗口再进入 MySQL

start transaction; 
    select * from account;

![在这里插入图片描述][20200629234040377.png]

1.  A 窗口更新数据

start transaction;
    update account set balance=balance+500 where id=1; 
    commit;

![在这里插入图片描述][20200629234104406.png]  
4. B 窗口查询

select * from account;

![在这里插入图片描述][20200629234159656.png]  
![在这里插入图片描述][20200629234212977.png]

结论：同一个事务中为了保证多次查询数据一致，必须使用 repeatable read 隔离级别

（七）幻读的演示  
在 MySQL 中无法看到幻读的效果。

UPDATE account SET balance = 1000;

但我们可以将事务隔离级别设置到最高，以挡住幻读的发生 将数据进行恢复：

1.  开启 A 窗口

set global transaction isolation level serializable; -- 设置隔离级别为最高

![在这里插入图片描述][2020062923430060.png]

1.  A 窗口退出 MySQL，A 窗口重新登录 MySQL

start transaction;
    select count(*) from account;

![在这里插入图片描述][20200629234319256.png]  
3. 再开启 B 窗口，登录 MySQL

1.  在 B 窗口中开启事务，添加一条记录

start transaction; -- 开启事务
    insert into account (name,balance) values ('LaoWang', 500);

![在这里插入图片描述][20200629234347385.png]  
![在这里插入图片描述][20200629234353931.png]  
5. 在 A 窗口中 commit 提交事务，B 窗口中 insert 语句会在 A 窗口事务提交后立马运行  
6. 在 A 窗口中接着查询，发现数据不变

select count(*) from account;

![在这里插入图片描述][20200629234422301.png]  
![在这里插入图片描述][20200629234439404.png]

1.  B 窗口中 commit 提交当前事务
2.  A 窗口就能看到最新的数据  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTg2_size_16_color_FFFFFF_t_70 3]

结论：使用 serializable 隔离级别，一个事务没有执行完，其他事务的 SQL 执行不了，可以挡住幻读

[20200629213936691.png]: https://img-blog.csdnimg.cn/20200629213936691.png
[20200629213951207.png]: https://img-blog.csdnimg.cn/20200629213951207.png
[20200629214009800.png]: https://img-blog.csdnimg.cn/20200629214009800.png
[2020062923074396.png]: https://img-blog.csdnimg.cn/2020062923074396.png
[2020062923081141.png]: https://img-blog.csdnimg.cn/2020062923081141.png
[20200629230840243.png]: https://img-blog.csdnimg.cn/20200629230840243.png
[20200629231543841.png]: https://img-blog.csdnimg.cn/20200629231543841.png
[2020062923162989.png]: https://img-blog.csdnimg.cn/2020062923162989.png
[20200629231648806.png]: https://img-blog.csdnimg.cn/20200629231648806.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTg2_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200629232139300.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTg2,size_16,color_FFFFFF,t_70
[20200629233242479.png]: https://img-blog.csdnimg.cn/20200629233242479.png
[20200629233308735.png]: https://img-blog.csdnimg.cn/20200629233308735.png
[20200629233325153.png]: https://img-blog.csdnimg.cn/20200629233325153.png
[20200629233344926.png]: https://img-blog.csdnimg.cn/20200629233344926.png
[20200629233418673.png]: https://img-blog.csdnimg.cn/20200629233418673.png
[20200629233435358.png]: https://img-blog.csdnimg.cn/20200629233435358.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTg2_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200629233509367.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTg2,size_16,color_FFFFFF,t_70
[20200629233519550.png]: https://img-blog.csdnimg.cn/20200629233519550.png
[2020062923353636.png]: https://img-blog.csdnimg.cn/2020062923353636.png
[20200629233633333.png]: https://img-blog.csdnimg.cn/20200629233633333.png
[20200629233652738.png]: https://img-blog.csdnimg.cn/20200629233652738.png
[20200629233701929.png]: https://img-blog.csdnimg.cn/20200629233701929.png
[20200629233721464.png]: https://img-blog.csdnimg.cn/20200629233721464.png
[20200629233827466.png]: https://img-blog.csdnimg.cn/20200629233827466.png
[20200629233844684.png]: https://img-blog.csdnimg.cn/20200629233844684.png
[2020062923392043.png]: https://img-blog.csdnimg.cn/2020062923392043.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTg2_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200629233945576.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTg2,size_16,color_FFFFFF,t_70
[20200629234023337.png]: https://img-blog.csdnimg.cn/20200629234023337.png
[20200629234040377.png]: https://img-blog.csdnimg.cn/20200629234040377.png
[20200629234104406.png]: https://img-blog.csdnimg.cn/20200629234104406.png
[20200629234159656.png]: https://img-blog.csdnimg.cn/20200629234159656.png
[20200629234212977.png]: https://img-blog.csdnimg.cn/20200629234212977.png
[2020062923430060.png]: https://img-blog.csdnimg.cn/2020062923430060.png
[20200629234319256.png]: https://img-blog.csdnimg.cn/20200629234319256.png
[20200629234347385.png]: https://img-blog.csdnimg.cn/20200629234347385.png
[20200629234353931.png]: https://img-blog.csdnimg.cn/20200629234353931.png
[20200629234422301.png]: https://img-blog.csdnimg.cn/20200629234422301.png
[20200629234439404.png]: https://img-blog.csdnimg.cn/20200629234439404.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTg2_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200629234451267.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTg2,size_16,color_FFFFFF,t_70