MySQL基础：事务_飞道的博客

MySQL基础：事务

2020-09-05 00:12 530人阅读评论(0)

目录

事务

事务的应用场景说明

手动提交事务

手动提交事务使用过程

自动提交事务

事务原理

回滚点

事务的隔离级别

MySQL 数据库有四种隔离级别

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

脏读的演示

不可重复读的演示

幻读的演示

 事务

 事务的应用场景说明

什么是事务

在实际的开发过程中，一个业务操作如：转账，往往是要多次访问数据库才能完成的。转账是一个用户扣钱，另一个用户加钱。

如果其中有一条 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 中可以有两种方式进行事务的操作：

手动提交事务

自动提交事务

手动提交事务的 SQL 语句

功能 SQL 语句

开启事务 start transaction;

提交事务 commit;

回滚事务 rollback;

手动提交事务使用过程

执行成功的情况：

开启事务 --> 执行多条 SQL 语句 --> 成功提交事务

执行失败的情况：

开启事务 --> 执行多条 SQL 语句 --> 事务的回滚

案例演示 1：事务提交

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

使用 DOS 控制台进入 MySQL

执行以下 SQL 语句： 1.开启事务， 2.张三账号-500， 3.李四账号+500

使用 SQLYog 查看数据库：发现数据并没有改变

在控制台执行 commit 提交事务：

使用 SQLYog 查看数据库：发现数据改变

案例演示 2：事务回滚

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

在控制台执行以下 SQL 语句：1.开启事务， 2.张三账号-500

使用 SQLYog 查看数据库：发现数据并没有改变

在控制台执行 rollback 回滚事务：

使用 SQLYog 查看数据库：发现数据没有改变

总结:

如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。

如果事务中 SQL语句有问题，rollback 回滚事务，会回退到开启事务时的状态。

自动提交事务

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

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

将金额重置为 1000

更新其中某一个账户

使用 SQLYog 查看数据库：发现数据已经改变

取消自动提交

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

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

取消自动提交事务

执行更新语句，使用 SQLYog 查看数据库，发现数据并没有改变

在控制台执行 commit 提交任务

 事务原理

原理图：

事务的步骤

客户端连接数据库服务器，创建连接时创建此用户临时日志文件

开启事务以后，所有的操作都会先写入到临时日志文件中

所有的查询操作从表中查询，但会经过日志文件加工后才返回

如果事务提交则将日志文件中的数据写到表中，否则清空日志文件。

回滚点

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

可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。

回滚点的操作语句

回滚点的操作语句语句

设置回滚点 savepoint 名字

回到回滚点 rollback to 名字

具体操作：

将数据还原到 1000

开启事务

让张三账号减 3 次钱，每次 10 块

设置回滚点：savepoint three_times;

让张三账号减 4 次钱，每次 10 块

回到回滚点：rollback to three_times;

分析执行过程

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

事务的隔离级别

事务的四大特性 ACID

事务特性 含义

原子性（Atomicity） 每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功，要么都失败。

一致性（Consistency）
事务在执行前数据库的状态与执行后数据库的状态保持一致。

如：转账前2个人的总金额是 2000，转账后 2 个人总金额也是 2000

隔离性（Isolation） 事务与事务之间不应该相互影响，执行时保持隔离的状态。

持久性（Durability） 一旦事务执行成功，对数据库的修改是持久的。就算关机，也是保存下来的。

事务的隔离级别

事务在操作时的理想状态：所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据。可能引发并发访问的问题：

并发访问的问题 含义

脏读
一个事务读取到了另一个事务中尚未提交的数据

不可重复读
一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这是事务 update 时引发的问题

幻读
一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致的，这是 insert 或 delete 时引发的问题

 MySQL 数据库有四种隔离级别

上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。

级别
名字 隔离级别 脏读 不可重复读 幻读 数据库默认隔离级别

1
读未提交
read uncommitted
是

是
是

2
读已提交
read committed 否是是
Oracle 和 SQL Server

3
可重复读
repeatable read 否是是
MySQL

4
串行化
serializable 否否否

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

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

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

设置隔离级别
set global transaction isolation level 级别字符串;
脏读的演示

将数据进行恢复：UPDATE account SET balance = 1000;

1) 打开 A 窗口登录 MySQL，设置全局的隔离级别为最低
set global transaction isolation level read uncommitted;
2) 打开 B 窗口,AB 窗口都开启事务
   
    
     
      
     
     
      
       use day23;
      
     
    
     
      
     
     
      
       start 
       transaction;
      
     
   
3）A 窗口更新 2 个人的账户数据（初始值两者均为：balance = 1000），未提交
   
    
     
      
     
     
      
       update 
       account 
       set balance=balance
       -500 
       where 
       id=
       1;
      
     
    
     
      
     
     
      
       update 
       account 
       set balance=balance+
       500 
       where 
       id=
       2;
      
     
   
4）B 窗口查询账户
select * from account;
5）A 窗口回滚
rollback;
6）B 窗口查询账户，钱没了

脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入 500 块，然后打电话给李四说钱已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。

解决脏读的问题

将全局的隔离级别进行提升

将数据进行恢复：
UPDATE account SET balance = 1000;
1）在 A 窗口设置全局的隔离级别为 read committed
set global transaction isolation level read committed;
2）B 窗口退出 MySQL，B 窗口再进入 MySQL

3）AB 窗口同时开启事务

4）A 更新 2 个人的账户，未提交
   
    
     
      
     
     
      
       update 
       account 
       set balance=balance
       -500 
       where 
       id=
       1;
      
     
    
     
      
     
     
      
       update 
       account 
       set balance=balance+
       500 
       where 
       id=
       2;
      
     
   
5）B 窗口查询账户

6）A 窗口 commit 提交事务

7）B 窗口查看账户

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

不可重复读的演示

将数据进行恢复：
UPDATE account SET balance = 1000;
1）开启 A 窗口
set global transaction isolation level read committed;
2）开启 B 窗口，在 B 窗口开启事务
   
    
     
      
     
     
      
       start 
       transaction;
      
     
    
     
      
     
     
      
       select * 
       from 
       account;
      
     
   
3）在 A 窗口开启事务，并更新数据
   
    
     
      
     
     
      
       start 
       transaction;
      
     
    
     
      
     
     
      
       update 
       account 
       set balance=balance+
       500 
       where 
       id=
       1;
      
     
    
     
      
     
     
      
       commit;
      
     
   
4）B 窗口查询
select * from account;
两次查询输出的结果不同，到底哪次是对的？

不知道以哪次为准。很多人认为这种情况就对了，无须困惑，当然是后面的为准。

我们可以考虑这样一种情况，比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户，结果在一个事务中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了。

解决不可重复读的问题：

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

将数据进行恢复：
UPDATE account SET balance = 1000;
1）A 窗口设置隔离级别为：repeatable read
set global transaction isolation level repeatable read;
2）B 窗口退出 MySQL，B 窗口再进入 MySQL
   
    
     
      
     
     
      
       start 
       transaction;
      
     
    
     
      
     
     
      
       select * 
       from 
       account;
      
     
   
3）A 窗口更新数据
   
    
     
      
     
     
      
       start 
       transaction;
      
     
    
     
      
     
     
      
       update 
       account 
       set balance=balance+
       500 
       where 
       id=
       1;
      
     
    
     
      
     
     
      
       commit;
      
     
   
4）B 窗口查询
select * from account;
结论：同一个事务中为了保证多次查询数据一致，必须使用 repeatable read 隔离级别

幻读的演示

在MySQL 中无法看到幻读的效果。

但我们可以将事务隔离级别设置到最高，以挡住幻读的发生将数据进行恢复：
UPDATE account SET balance = 1000;
1）开启 A 窗口
set global transaction isolation level serializable; -- 设置隔离级别为最高
2）A 窗口退出 MySQL，A 窗口重新登录 MySQL
   
    
     
      
     
     
      
       start 
       transaction;
      
     
    
     
      
     
     
      
       select 
       count(*) 
       from 
       account;
      
     
   
3）再开启 B 窗口，登录 MySQL

4）在 B 窗口中开启事务，添加一条记录
   
    
     
      
     
     
      
       start 
       transaction; 
       -- 开启事务
      
     
    
     
      
     
     
      
       insert 
       into 
       account (
       name,balance) 
       values (
       'LaoWang', 
       500);
      
     
   
5）在 A 窗口中 commit 提交事务，B 窗口中 insert 语句会在 A 窗口事务提交后立马运行

6）在 A 窗口中接着查询，发现数据不变
select count(*) from account;
7）B 窗口中 commit 提交当前事务

8）A 窗口就能看到最新的数据

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

【注】参考黑马MySQL教程

转载：https://blog.csdn.net/baidu_41388533/article/details/108263927

查看评论

飞道的博客

飞道的博客

个人资料

文章分类

文章存档

阅读排行

评论排行

推荐文章

MySQL基础：事务

事务

事务的应用场景说明

手动提交事务

手动提交事务使用过程

自动提交事务

事务原理

回滚点

事务的隔离级别

MySQL 数据库有四种隔离级别

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

脏读的演示

不可重复读的演示

幻读的演示

* 以上用户言论只代表其个人观点，不代表本网站的观点或立场

功能	SQL 语句
开启事务	start transaction;
提交事务	commit;
回滚事务	rollback;

回滚点的操作语句	语句
设置回滚点	savepoint 名字
回到回滚点	rollback to 名字

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

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

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle 和 SQL Server
3	可重复读	repeatable read	否	是	是	MySQL
4	串行化	serializable	否	否	否