当前位置： 首页 > article >正文

MySQL（七）——事务

article 2024/9/24 11:09:21

文章目录

  • 事务
    • 事务的概念
    • 事务的ACID特性
    • 事务的语法
      • 查看存储引擎
      • 查看自动提交参数和设置
      • 手动事务操作
      • 保存点
    • 隔离级别与并发事务问题
      • 隔离级别
      • 并发事务问题

事务

事务的概念

事务（Transaction）是数据库管理系统中执行过程中的一个逻辑单位，由一个或多个 SQL 语句组成，这些语句作为一个整体一起向系统提交，要么全部执行，要么全部不执行，即事务不可分割。 事务的目的是为了保证数据的完整性和安全性。

MySQL在5.5版本开始，就将InnoDB引擎作为默认存储引擎。InnoDB引擎是支持事务的，但其他常见的如MyISAM和Memory都是不支持事务的。

所以我们都是在InnoDB默认引擎的场景下介绍的。

可以从以下场景理解事务：

假设张三和李四各有1000元，张三向李四转账100元就可以看作一个事务，该事务中包含以下SQL逻辑：

  • 查询张三的余额
  • 更新张三余额，使其减少100
  • 更新李四余额，使其增加100

这三句SQL，尤其是第2、3条，要么全部成功，要么全部失败。正常情况下，三步完成后，张三的余额减少100，李四的余额增加100。但如果出现张三的余额减少但李四的余额没有增加的情况，此时数据就出现了问题。

为了解决上述问题，就需要通过事务来完成，将上面三条打包成一个事务。只需要在业务逻辑开始前开启事务，执行完毕后提交事务，如果执行过程中出现差错，就回滚事务，将所有的数据回退到开启事务前的状态，这样就能确保这一组语句"要么全部失败，要么全部成功"。

事务的ACID特性

事务的ACID特性 指的是Atomicity（原子性）、Consistency（一致性）、Isolation（隔离性）和Durability（持久性）。

  1. 原子性

    • 定义：原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都成功执行，要么都不执行。如果事务中的任何一个操作失败，整个事务将被回滚到开始状态。

    • 实现原理：原子性通过使用 重做日志 和 回滚日志 来实现。

      当事务开始时，InnoDB会为该事务分配一个唯一的事务ID，并开始记录重做日志和回滚日志。如果在事务执行过程中发生错误或系统故障，MySQL将利用这些日志来回滚事务中的所有操作，保证数据库的原子性不被破坏。

    • 举例：张三的余额减少了100，李四的余额增加了100，不能出现张三的余额减少而李四的余额没有增加的情况。

  2. 一致性

    • 定义：一致性是指事务将数据库从一个一致的状态转变为另一个一致的状态（数据库的完整性不会被破坏）。这意味着事务执行的结果必须符合所有预定义的规则和约束，包括数据库的内部规则（如主键、外键约束、唯一性约束等）以及应用层面的业务规则。
    • 实现原理：一致性通过约束、隔离级别等几个机制来保证数据的一致性，实际上是在其他三种特性基础上实现的。
    • 举例：张三和李四转账前后的总金额数2000不变。

  3. 隔离性

    • 定义：隔离性是指在多个事务同时对数据库进行操作时，每个事务都是独立的，一个事务的操作不会影响到其他事务。这保证了并发执行的事务不会互相干扰。
    • ★实现原理：隔离性通过四种不同的隔离级别来控制事务之间的隔离程度，包括：读未提交、读已提交、可重复读以及串行化，它们决定了事务之间如何相互影响，从而保证了不同级别的数据一致性和性能需求。
    • 举例：在转账的处理过程中张三和李四的余额不能因其他的转账时间而受到干扰。

  4. 持久性

    • 定义：持久性是指一旦事务被提交，它对数据库的修改就是永久性的，即使系统发生故障也不会丢失。这确保了事务一旦完成，其结果就会永久保存在数据库中。
    • 实现原理：持久性通过将事务的日志记录到存储介质（如磁盘）上来实现。
    • 举例：转账后的余额结果被保存到存储介质中，方便以后读取。

事务的ACID特性就是我们要使用事务的原因，支持事务的数据库能够简化我们的编程模型，不需要我们去考虑各种潜在的错误和并发问。所以事务本质上是数据库ACID模型的一个实现，是为应用层服务的。

事务的语法

默认情况下，MySQL的事务是自动提交的，即当执行DML语句进行修改等操作时，都会隐式地自动开启一个事务并在语句执行完成之后自动提交，发生异常时自动回滚。

查看存储引擎

查看MySQL支持的存储引擎，以确定哪些存储引擎支持事务

SHOW ENGINES;

在这里插入图片描述

结果集验证了MySQL默认的存储引擎InnoDB是支持事务的。

查看自动提交参数和设置

-- 查看是否自动提交（的开关）
# 会话级别
SHOW VARIABLES LIEK 'autocommit'; -- 返回结果是ON或者OFF
SELECT @@autocommit;              -- 返回结果是1(表示ON)或0(表示OFF)
# 全局级别
SHOW GLOBAL VARIABLES LIKE 'autocommit';
SELECT @@GLOBAL.autocommit;

-- 设置autocommit的值
# 会话级别
SET SESSION autocommit = 1;     -- 设置为自动提交
SET SESSION autocommit = ON;    

SET SESSION autocommit = 0;     -- 设置为不自动提交
SET SESSION autocommit = OFF;
# 全局级别
SET GLOBAL autocommit = 1;
SET GLOBAL autocommit = ON;

SET GLOBAL autocommit = 0;
SET GLOBAL autocommit = OFF;
  • 不论设置全局级别的变量还是会话级别的变量，重启服务时autocommit的值都会恢复为默认值。 如果要使该设置永久化，则需要修改配置文件，修改时确保你有修改的权限
  • 设置会话级别的变量时，该设置只会对当前会话生效；设置全局级别的变量时，该设置会对所有的会话生效
  • 自动提交的开关默认是打开的
  • 如果编写设置autocommit值的SQL时既不指定SESSION，也不指定GLOBAL，此时的设置默认是SESSION。
  • 会话级别的autocommit和全局级别的autocommit不是同一个变量，如果发生冲突，会话级别的优先生效。
  • autocommit的设置主要影响的是自动事务的提交方式，而对手动事务的提交则没有影响，即当autocommit的开关打开，执行普通的（指没有被手动打包在事务中的）DML语句进行修改等操作时，隐式开启的事务在语句执行完成之后会自动提交；当autocommit的开关关闭，执行普通的（指没有被手动打包在事务中的）DML语句进行修改等操作时，事务不会自动提交，必须手动提交，否则实际该操作修改的数据不会持久化保存在存储介质中。另外，手动提交模式下不用显式开启事务，执行修改操作后，直接COMMIT;提交或ROLLBACK;回滚即可。

手动事务操作

# 开启事务，两种方式均可以
BEGIN;
START TRANSACTION;

# 提交事务
COMMIT;

# 回滚事务
ROLLBACK;
  • 事务提交前的各种操作都是"临时"操作，只有提交后才会永久化保存，或者回滚
  • COMMIT提交事务后，对数据进行的修改等操作才会被持久化保存到存储介质中
  • ROLLBACK回滚事务，将数据恢复到事务开启前的状态。当事务中的某一部分报错，此时就需要回滚事务以避免出现数据安全问题
  • 使用COMMIT提交事务和ROLLBACK回滚事务都会结束当前的事务

【SQL演示】

以转账场景为例：

  • 事务提交：张三向李四转账100元，成功！

    在这里插入图片描述

  • 事务回滚：张三再次向李四转账100元，中途出错，事务需要回滚

    在这里插入图片描述

保存点

保存点（Savepoint）是一种用于管理事务的机制，允许在一个事务内部创建多个“标记”，以便在需要时回滚到这些标记，即在事务执行过程中设置保存点，回滚时指定保存点可以把数据恢复到保存点的状态。保存点可以看作是事务的子事务，使得事务处理更加灵活和可控。

语法：

# 创建保存点
SAVEPOINT savepoint_name;

# 释放保存点
RELEASE SAVEPOINT savepoint_name;

# 回滚到某个保存点
ROLLBACK TO SAVEPOINT savepoint_name;

  • 保存点必须在手动创建的事务（BEGIN;或START TRANSACTION;）中使用，保存点的具体使用步骤如下：

    1. 手动开启事务：使用BEGIN;或者START TRANSACTION;
    2. 设置保存点：使用SAVEPOINT savepoint_name;
    3. 进行数据操作：CRUD
    4. 使用（回滚到）保存点：使用保存点回滚语句，如：ROLLBACK TO SAVEPOINT savepoint_name;
    5. 提交或回滚：COMMIT;、ROLLBACK;

  • 当一个事务被提交或回滚时，该事务中定义的所有保存点都会被自动回收。 此外，保存点只在当前会话中有效，一旦会话结束，所有保存点都被清除。

  • 回滚到某个保存点的操作不会使得整个事务结束。

  • 保存点可以嵌套，即可以在一个保存点内再设置另一个保存点。

  • 在回滚到某个保存点时会自动释放所有在此保存点之后创建的保存点。

  • 避免不必要的保存点释放，这可能导致意外行为，保存点在事务结束时会自动回收的。

  • 避免创建太多的保存点，过度使用保存点可能会增加数据库的开销，因为每个保存点都需要额外的资源来管理。

【SQL演示】

开启前查询的结果是：

在这里插入图片描述

在这里插入图片描述

提交后查询的结果是：

在这里插入图片描述

解释： 提交事务前回滚到了sp2保存点，这使得插入数据的行为被回滚，最终导致只有更新操作生效。

隔离级别与并发事务问题

隔离级别

前面介绍过事务的ACID特性，其中原子性指事务不可再分，持久性指将事务存储到存储介质中，一致性则强调数据状态的一致性，是在其他三种特性的基础上实现的，而隔离性保证了多个并发事务之间不受影响。

并发事务之间保证隔离性可以通过事务的 隔离级别，设置合适的隔离级别可以有效解决一些并发事务问题，MySQL支持四种隔离级别：

  • READ UNCOMMITTED（读未提交）
  • READ COMMITTED（读已提交）
  • REPEATABLE READ（可重复读）
  • SERIALIZABLE（串行化）

其中，REPEATABLE READ是默认隔离级别。

【查看和设置隔离级别】

事务的隔离级别分为全局作用域的和会话作用域的，查看不同作用域事务的隔离级别，语法如下：

# 全局作用域
SELECT @@GLOBAL.transaction_isolation;

# 会话作用域
SELECT @@SESSION.transaction_isolation;

在这里插入图片描述

  • 可以看到，默认的隔离级别就是REPEATABLE READ

设置事务隔离级别语法如下：

# 第一种
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE};

# 第二种
SET [GLOBAL|SESSION] transaction_isolation = {'READ-UNCOMMITTED'|'READ-COMMITTED'|'REPEATABLE-READ'|'SERIALIZABLE'};

# 第三种
SET [@@GLOBAL.transaction_isolation|@@SESSION.transaction_isolation] =  {'READ-UNCOMMITTED'|'READ-COMMITTED'|'REPEATABLE-READ'|'SERIALIZABLE'};
  • 注意第二、三种方式指定隔离级别时需要加-。
  • 设置事务隔离级别时也可以不指定作用域，此时的设置默认只对下一个事务生效。
  • 设置全局事务隔离级别会影响所有新创建的会话，但不会影响已经存在的会话；设置会话事务隔离级别会影响当前会话中所有后续的事务，但不会影响当前正在进行的事务。
  • 使用SQL语句设置的隔离级别都是临时的，在服务器重启时都会恢复默认值。

上面只是简单介绍了有四种隔离级别，那么这几种隔离级别有什么区别？分别解决了什么并发事务问题？如下表所示

隔离级别脏读不可重复读幻读
READ UNCOMMITTED（读未提交）存在存在存在
READ COMMITTED（读已提交）不存在存在存在
REPEATABLE READ（可重复读）不存在不存在存在
SERIALIZABLE（串行化）不存在不存在不存在
  • 上表格列举的三种并发事务问题：
    1. 脏读
    2. 不可重复读
    3. 幻读
  • 以表格列举的顺序，从上到下，隔离级别越来越高，最高级别SERIALIZABLE强制事务按顺序执行，不允许并发。
  • 隔离级别越高，安全性越高，但性能越差。 选择合适的隔离级别尤为重要，既要考虑安全性又要考虑性能。
  • REPEATABLE READ（可重复读）尽管没有完全解决幻读问题，但通过多版本并发控制（MVCC）和一种称为“Next-Key Lock”的技术解决了部分幻读问题。

并发事务问题

演示时模拟并发事务，将展示两个会话及SQL操作，同时为了演示三种并发事务问题，会将隔离级别提前修改为READ UNCOMMITTED，该隔离级别下，三种并发事务问题都存在。

演示用表如下：

在这里插入图片描述

【脏读】

定义：一个事务读取了另一个事务尚未提交的数据。 如果第二个事务后来被回滚，第一个事务读取的数据将是无效的。

演示：

会话一会话二
BEGIN;BEGIN;
UPDATE account SET balance = balance - 100 WHERE name = ‘张三’;
SELECT * FROM account;
ROLLBACK;

两个会话都开启了事务，会话二先修改数据，然后会话一执行查询操作，能够查询到会话二还没提交的数据，此时会话一就发生了脏读现象，然后会话二回滚事务，此时会话一的查询结果无效。

【不可重复读】

定义：在一个事务内，多次读取同一数据时，结果不一致，因为其他事务已经提交了对该数据的修改。

演示：

会话一会话二
BEGIN;BEGIN;
SELECT * FROM account WHERE name = ‘张三’;
UPDATE account SET balance = balance - 100 WHERE name = ‘张三’;
COMMIT;
SELECT * FROM account WHERE name = ‘张三’;

两个会话都开启了事务，会话一先查询张三的记录，会话二更新张三的数据并提交事务，然后会话一再次查询张三的记录，此时会话一发生了不可重复读的问题，两次查询到的张三的记录不一致。

【幻读】

定义：在一个事务内，多次执行同一查询时，结果集的行数不一致，因为其他事务插入了新的行。

演示：

会话一会话二
BEGIN;BEGIN;
SELECT * FROM account;
INSERT INTO account VALUES (3,‘王五’,1000);
COMMIT;
SELECT * FROM account;

两个会话都开启了事务，会话一先查询了所有数据，共两行，此时会话二插入了一条新数据并提交，然后会话一执行相同的查询，返回的结果共三行，多出来的一行就像“幻像”一样，此时会话一发生了幻读。

完


http://www.kler.cn/news/317398.html

相关文章：

  • vue3中使用nexttick
  • QTableView使用QSortFilterProxyModel后行号错乱
  • 深度学习经典模型解析
  • 基于SpringBoot+Vue+MySQL的教学资料管理系统
  • Web+Mysql——MyBatis
  • 简单的spring缓存 Cacheable学习
  • Rust 全局变量的最佳实践 lazy_static/OnceLock/Mutex/RwLock
  • 02 BlockChain-- ETH
  • 着色器ShaderMask
  • HTML、CSS
  • python机器学习足球数据建模与分析——数据预测与预测建模
  • 嵌入式综合实验平台-嵌入式综合实训实验箱
  • [Excel VBA]如何使用VBA按行拆分Excel工作表
  • PHP智慧教育新篇章优校管理系统小程序源码
  • uniapp常用声明周期
  • 【C++指南】inline内联函数详解
  • network request to https://registry.npmjs.org/xxx failed, reason: connect ETIM
  • Cesium影像纠偏：高德地图加载与坐标系纠偏技巧
  • 【机器学习(九)】分类和回归任务-多层感知机 (MLP) -Sentosa_DSML社区版
  • Github 2024-09-21Rust开源项目日报 Top10
  • Qt/C++ 了解NTFS文件系统，解析0x80 $Data属性，获取Run Lists数据列表
  • 仓颉编程语言4，遇到BUG求助
  • 数据中心里全速运行的处理器正在浪费能源
  • golang格式化输入输出
  • 【3D打印】使用simplify 3D切片更改Gcode手动断电续打、掉电、未打完继续打印、补救
  • Parallels Desktop 20 for Mac 推出：完美兼容 macOS Sequoia 与 Win11 24H2
  • 【Godot4.3】自定义数列类NumList
  • 【Qt | Qstring】Qstring详细介绍(字符串的添加、删除、修改、查询、截断、删除空白字符等)
  • Gitlab runner的使用示例（二）：Maven + Docker 自动化构建与部署
  • 【游戏引擎】C++自制游戏引擎 Lunar Game Engine