MySQL事务的基础知识

一、数据库事务概述

1.1、存储引擎支持事务的情况

    show engines;

我们从图中可以看出来目前只有InnoDB支持事务！

1.2、事务的基本概念

数据库事务由一系列数据库操作构成的逻辑单元组成，在这种情况下不仅会实现所有操作的成功执行还会导致所有操作均发生回滚的情况出现。这种机制的主要目的是确保数据的一致性和完整性得以得到充分保障

⽐如，我们去银⾏转账，操作可以分为下⾯两个环节：

• 从第⼀个账户划出款项。
• 将款项存⼊第⼆个账户。

在这一过程中, 这两个环节相互关联. 第 ⼀ 个账户划出款项后必须确保第 ⼆ 个账户正确存⼊. 如果第 ⼆ 个环节未能完成, 整个流程都应终止, 否则会导致款项丢失的问题. 整个交易过程可被视为一个事务, 若成功, 则整个过程均成功; 若失败, 则需将所有操作撤销, 这样就能避免中间环节出现问题时导致数据不一致的情况.

**事务：**一组逻辑操作单元，使数据从一种状态变换到另一种状态。

该系统设计的核心原则在于确保每个事务都被完整地作为一个独立的操作单元进行处理。即使在操作过程中发生故障或异常情况也不会更改这一执行流程。在单个事务范围内涉及多个操作时 系统将采取以下两种策略以确保数据的一致性与安全性：要么允许所有完成的操作被成功提交（commit）从而永久保存相关修改；要么触发数据库管理系统的整体拒绝机制迫使所有当前未完成的操作归零并恢复到最初的状态（rollback to the initial state）.

1.3、事务的ACID

事务应当具备五个特性：原子性是指在系统中事务能够独立执行而不受其他事务干扰的一致性和不产生幻影现象的特点；一致性和是指在整个系统中所有节点对共享资源都保持相同的可见性和不可变性的特点；隔离性和是指一个交易内部操作能够串行化而不影响其他交易的整体执行效果；持久性和是指一旦一个事务完成就会永久保存其结果到数据库中而不受影响。其中这些特性通常被称为ACID特性

原子性（atomicity）

一致性（consistency）

隔离性（isolation）

持久性（durability）

1.3.1、原子性（atomicity）

每个事务都应被视为不可分割的整体单元，在整个事务中所有操作的结果必须是全成功或全失败。任何一个事务不允许部分操作被提交，并举个例子，在数据库处理中完成一次完整的锁机制。

连老师借给李老师1000元：

1.连老师工资卡扣除1000元

2.李老师工资卡增加1000元

整个事务的操作必须完全成功或者完全失败，并且必须避免连老师工资卡被扣除的情况发生；然而，在李老师的工资卡上不允许发生被增加的情况。如果无法保证操作的原子性，则这将不可避免地导致一致性问题。

1.3.2、一致性（consistency）

一致性是指事务将数据库从一种一致性的状态转移至另一种一致的状态，在事务执行前后不会破坏数据的完整性

连老师借给李老师1000元：

1.连老师工资卡扣除1000元

2.李老师工资卡增加1000元

减去的钱（-500）加上增加的钱（500）总和应为零；此外, 连老师与李老师的账户中的资金变动前后总额也应保持不变

1.3.3、持久性（durability）

当一次事务提交后，则其完成的所有变更将被长期存储在数据库中。即使系统出现故障时也不会遗漏任何已完成的变更

在执行事务操作时，在修改数据库数据之前会先将操作记录至重做日志中。在处理事务过程中会生成两种类型的记录：一个是用于回放未提交操作的重做日志（-commit log），另一个则是用于记录事务发生 rollback 的信息（rollback log）。在使用分布式事务系统时，在每个节点上都会维护自己的复制器副本以及相关的复制器指针，并定期与其它节点进行通信以达成一致性共识。

当数据库发生故障需要重启时，在恢复过程中系统会自动读取未提交到主数据库的重做指令以恢复数据。

1.3.4.隔离性（isolation）

一个事务的执行不受其他事物的影响。这是因为一个事物内部的操作及其使用的数据对并发的事物是隔离的，并发执行的事物之间不能互相影响。

如果隔离性不能保证，会导致什么问题？

根据理论计算，在全部还清后，请问您认为连老师的信用卡余额应为多少？

我们将连老师向李老师同时进行的两次转账操作分别称为T1和T2，在现实世界中T1和T2是应该没有关系的，可以先执行完T1，再执行T2，或者先执行完T2，再执行T1，结果都是一样的。但是很不幸，真实的数据库中T1和T2的操作可能交替执行的，执行顺序就有可能是：

如果按照上图所示的流程进行两次转账操作时，在连老师的账户中剩余资金为9千余元。这对比显示，在连老师的账户中剩余资金仅为此次转账所扣除的资金量即1千元。然而，在李老师的账户中显示的资金金额则达到了2.5千余元较首次转账前增加了1万多元这不禁让人质疑难道说这家银行因此就会亏损严重吗？

所以，在现实世界中涉及状态转换的某些数据库操作方面，则要求不仅这些数据库操作必须按原子性方式执行完毕，并且其他的状态转换不应干扰当前的状态转换这一规则被称为隔离性

二、如何使用事务

使用事务有两种方式，分别为显式事务和隐式事务。

2.1、显示事务

步骤1： START TRANSACTION 或者 BEGIN ，作用是显式开启一个事务。

    BEGIN;

    START TRANSACTION;

START TRANSACTION 语句相比BEGIN的主要区别在于可以附带几个修饰符：

① Read-only：将当前事务标记为一个只能进行读取而不进行任何修改的数据操作。

READ WRITE ：表示当前事务是一个读写事务，并归于该事务的数据库操作既能进行读取数据又能进行修改数据。

③ WITH CONSISTENT SNAPSHOT ：启动一致性读。

**步骤2：**一系列事务中的操作（主要是DML，不含DDL）

**步骤3：**提交事务 或 中止事务（即回滚事务）

    # 提交事务。当提交事务后，对数据库的修改是永久性的。
    COMMIT;

    # 回滚事务。即撤销正在进行的所有没有提交的修改
    ROLLBACK;

    # 将事务回滚到某个保存点。
    ROLLBACK TO [SAVEPOINT]

情景演示环境准备：

假设我们有一个电子商务应用，其中有 users、products 和 orders 三张表。

    CREATE DATABASE ecommerce;
    
    USE ecommerce;
    
    CREATE TABLE users
    (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)    NOT NULL,
    email    VARCHAR(100)   NOT NULL,
    balance  DECIMAL(10, 2) NOT NULL DEFAULT 0.00
    );
    
    CREATE TABLE products
    (
    id    INT AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(100)   NOT NULL,
    price DECIMAL(10, 2) NOT NULL
    );
    
    CREATE TABLE orders
    (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    user_id     INT,
    product_id  INT,
    quantity    INT            NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    order_date  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id),
    FOREIGN KEY (product_id) REFERENCES products (id)
    );

    INSERT INTO users (username, email, balance)
    VALUES ('Alice', 'alice@example.com', 100.00),
       ('Bob', 'bob@example.com', 50.00),
       ('Charlie', 'charlie@example.com', 75.00);
    
    INSERT INTO products (name, price)
    VALUES ('Laptop', 999.99),
       ('Phone', 499.99),
       ('Headphones', 149.99);
       
    INSERT INTO users (username, email, balance)
    VALUES ('Alice', 'alice@example.com', 100.00),
       ('Bob', 'bob@example.com', 50.00),
       ('Charlie', 'charlie@example.com', 75.00);
    
    INSERT INTO products (name, price)
    VALUES ('Laptop', 999.99),
       ('Phone', 499.99),
       ('Headphones', 149.99);       

2.1.1、情景演示1

当客户计划购买产品时，在客户的账户余额中扣除相应的费用，并在订单记录表（orders 表）中进行记录。若任何步骤出现故障，则需回滚事务。

开启事务：

    START TRANSACTION;

执行事务中的操作

检查用户的余额是否足够支付产品。假设用户ID为1的用户尝试购买产品

    SELECT balance FROM users WHERE id = 1;

扣除用户的余额。

    UPDATE users SET balance = balance - 499.99 WHERE id = 1;

插入订单记录。

    INSERT INTO orders (user_id, product_id, quantity, total_price) VALUES (1, 2, 1, 499.99);

我们再次查看相应的余额

    SELECT balance FROM users WHERE id = 1;

提交事务
如果所有操作成功执行，则提交事务：

    COMMIT;

回滚事务
如果某一步失败（例如用户的余额不足），则回滚事务：

    ROLLBACK;

    SELECT balance FROM users WHERE id = 1; 

2.1.2、情景演示2

当开启一个事务时，并已执行了一系列语句，在整个过程中突然发现某个语句存在问题，则不得不使用ROLLBACK语句将数据库的状态恢复到事务启动之前的状态。然而，在实际情况中，并非所有回滚都是必要的。因此MySQL引入了保存点（savepoint）的概念。通过在事务中设置多个保存点，在回滚时可以选择回到相应的保存点而不是回到初始状态。定义保存点的语法如下：

SAVEPOINT 保存点名称;

当我们希望回滚至某个特定保存点时，可以通过下述语句实现（其中涉及的相关参数如WORK和SAVEPOINT均为可选设置项）：

    ROLLBACK TO [SAVEPOINT] 保存点名称;

但是若rollback语句后方未紧跟保存点名称，则会直接恢复到事务起始阶段的状态

如果我们想删除某个保存点，可以使用这个语句：

    RELEASE SAVEPOINT 保存点名称;

我们继续使用之前的 ecommerce 数据库进行处理，并关注一名用户Alice（用户ID为1）的多个订单管理情况。当某个产品的下单出现故障时，请确保系统会重回到最近一次成功的操作记录而非重回到整个事务的开始位置

步骤1：开启事务并设置保存点

首先，启动事务：

    START TRANSACTION;

检查Alice的余额：

    SELECT balance FROM users WHERE id = 1;

• 结果：100.00（Alice的余额为100.00）

设置第一个保存点：

    SAVEPOINT before_first_purchase;

步骤2：执行第一个操作并回滚到保存点

Alice尝试购买一副价格为149.99的耳机（Headphones，产品ID为3）：

    UPDATE users SET balance = balance - 149.99 WHERE id = 1;

    INSERT INTO orders (user_id, product_id, quantity, total_price) VALUES (1, 3, 1, 149.99);

    SELECT balance FROM users WHERE id = 1;

当下如果运行成功的话

回滚到保存点：

    ROLLBACK TO before_first_purchase;

• 现在，Alice的余额恢复到之前的状态，即 100.00，并且这个购买操作被撤销。

    SELECT balance FROM users WHERE id = 1;

步骤3：继续进行其他操作并提交事务

现在Alice为了节省开支购买了一个性价比高的手机（Phone），其产品ID为2，价格为499.99元。我们继续创建一个新的存储点并进行操作：

    SAVEPOINT before_second_purchase;

由于资金不足以覆盖操作费用的操作会失败，则需恢复至前一步骤

    ROLLBACK TO before_second_purchase;

最后，Alice决定购买更便宜的产品（假设有一款新产品，产品ID为4，价格为49.99）：

    INSERT INTO products (name, price) VALUES ('Charger', 49.99);

    UPDATE users SET balance = balance - 49.99 WHERE id = 1;

    INSERT INTO orders (user_id, product_id, quantity, total_price) VALUES (1, 4, 1, 49.99);

• 此时，操作成功，Alice的余额为 50.01。

    SELECT balance FROM users WHERE id = 1;

3. 提交事务：

    COMMIT;

    SELECT balance FROM users WHERE id = 1;

2.2、隐式事务

MySQL中有一个系统变量autocommit ：

    SHOW VARIABLES LIKE 'autocommit';

当然，如果我们想关闭这种自动提交的功能，可以使用下边两种方法之一：

明确地使用START TRANSACTION 或 BEGIN 语句启动一个事务操作。这样，在当前事务完成之前不会激活自动提交功能。

把系统变量autocommit 的值设置为OFF ，就像这样：

    SET autocommit = OFF;

    SET autocommit = 0;

当我们在使用START TRANSACTION或BEGIN关键字启动一个事务操作时，默认情况下该事务操作序列将不会触发自动提交机制。然而，在执行完特定指令后系统会默认进行一次提交操作类似于我们在向数据库发送COMMIT指令的行为这种情况被称作显式的事务提交机制然而在某些特殊情况下执行完特定指令后系统会自行完成该事务的操作并触发不可控的提交过程这些导致显式或隐性地触发事务提交的关键指令包括：

2.2.1、执行DDL

说明或规定数据库对象的数据模型（Data Definition Language, 简称为 DDL）

所谓数据库实体即指完整的数据库架构体系其中包括具体的数据表视图存储过程等核心组件在用户进行 CREATE ALTER DROP 等操作作用于这些数据库实体时系统会执行自动提交机制确保数据变更的一致性和持久性

2.2.2、隐式使用或修改mysql数据库中的表

在执行诸如MODIFY USER、CREATE USER、DELETE USER、AUTHORIZE、RENAME USER、UNAUTHORIZE以及SET PASSWORD等SQL指令时，在这种情况下这些操作通常会自动导致前一串指令被直接提交到数据库中。

2.2.3、事务控制或关于锁定的语句

在对话过程中，在某个事务未被提交或回滚的情况下使用START TRANSACTION或BEGIN语句启动另一个事务时，则该前一事务会被隐式地提交。

2.2.4、加载数据的语句

当我们通过LOAD DATA语句进行批量数据导入操作时，并不会显式地触发提交事务的动作；相反地，在这种情况下系统会自动将该LOAD DATA语句及其相关联的数据操作视为一个完整的事务进行处理，并在完成所有操作后提交到数据库中。

2.2.5、关于MySQL复制的一些语句

在涉及START SLAVE、STOP SLAVE、RESET SLAVE以及CHANGE MASTER TO等关键语句的情况下，默认会提交与之相关的事务。

2.2.6、其它的一些语句

使用Analyze该表、Cache索引、Check该表、Flush操作以及Load索引至缓存等命令也会自动提交与之相关的事务处理

三、事务隔离级别

MySQL采用客户端／服务器架构设计，在同一台服务器上可能同时连接多个客户端。一旦某个客户端建立会话后即被视为一个 session。各个客户端在其独立的 session 中向服务器提交请求语句，在这种情况下单个请求语句可能属于某个事务的整体处理过程。理论上，在某个事务对特定数据进行操作时，其他事务必须等待该事务完成才能对该数据进行访问。然而这种做法可能导致性能下降明显，并且我们既希望保障事务隔离性又希望在处理大量并发访问同一数据时仍能保持较高效率水平之间找到最佳平衡点

概述：

事务隔离级别的问题源于多用户环境中对数据一致性和系统性能的权衡挑战。在一个数据库系统中，在线隔离性被定义为：当多个事务并发执行时不会受到其他操作的影响，并确保数据的一致性和正确性。然而，在这种严格要求下会导致系统的性能下降。

3.1、事务并发引发的问题

该系统中提到事务具有隔离性特性。理论上当一个事务对某数据进行读取操作时,为了保证一致性,所有关联的数据都会被锁定,此时其它相关联的事务必须等待队列处理。完成提交操作后,这些读取操作才能继续执行,从而使得整个并发操作被转换为严格的顺序执行模式。

但是串行化执行会导致显著的性能下降。为了既能维持一定程度的事务隔离以保证数据一致性，在处理多个并发事务访问同一数据时又能尽可能提高服务器的整体性能，在放弃这种隔离机制后可能会出现哪些潜在的数据问题？

问题演示的数据库准备：

    DROP TABLE IF EXISTS `accounts`;
    CREATE TABLE `accounts`  (
      `account_id` int NOT NULL,
      `account_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
      `balance` decimal(10, 2) NULL DEFAULT NULL,
      PRIMARY KEY (`account_id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
    
    INSERT INTO `accounts` VALUES (1, 'A', 500.00);
    INSERT INTO `accounts` VALUES (2, 'B', 500.00);
    INSERT INTO `accounts` VALUES (3, 'C', 500.00);
    
    SET FOREIGN_KEY_CHECKS = 1;

为了后续的演示清楚，我们统一会话的隔离界别：

    SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

3.1.1、脏读

当一个事务读取到了另外一个事务修改但未提交的数据，被称为脏读。

在事务A运行过程中，在处理数据资源时发生了修改行为，在此之后另一个交易（transaction）B进行了数据获取操作。当交易A未能完成提交流程并触发回滚操作（rollback）时，在这种情况下交易B所获取的数据即为不一致或脏数据（dirty data）。这种情况即被称为脏读（Dirty Read）现象。

    START TRANSACTION;

    UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
    -- 此时，A账户的余额变成了400，但还没有提交

    select * from accounts WHERE account_id = 1;

    START TRANSACTION;

    SELECT balance FROM accounts WHERE account_id = 1;
    -- 读取到A账户的余额是400（脏读，因为Session 1还没有提交事务）

    ROLLBACK;
    -- 回滚事务，A账户的余额恢复到500

    SELECT balance FROM accounts WHERE account_id = 1;

    ROLLBACK;

    SELECT balance FROM accounts WHERE account_id = 1;

3.1.2、不可重复读

在同一个业务流程中存在同一数据项的情况下，在进行两次查询时若结果出现差异，则称这种情况为无法实现一致性读取

事务B对数据资源进行了两次获取，在前后两个获取过程中事务A对数据进行了修改，导致事务B在前后两个获取过程中的数据结果不一致。

    START TRANSACTION;

    SELECT balance FROM accounts WHERE account_id = 1;
    -- 读取到A账户的余额是500

    START TRANSACTION;

    UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

    COMMIT;
    -- 提交事务，A账户的余额变成400

    SELECT balance FROM accounts WHERE account_id = 1;

    SELECT balance FROM accounts WHERE account_id = 1;

    COMMIT;

3.1.3、幻读

当执行事务操作时，在试图向当前被读取的数据集中插入新记录的过程中（或其他事务）可能会导致幻读现象出现。

在事务B前后两次调用同一数据范围时，在事务B执行两次的过程中事务A系统中新增了新的数据记录；这会导致事务B在第二次执行时无法看到第一次未处理的数据行。
在某些情况下幻读与不可重复.read有一定的相似性；然而幻.read则特别关注于能够捕获那些之前未被访问过的特定记录。

    START TRANSACTION;

    SELECT * FROM accounts WHERE balance = 500;
    -- 读取到两条记录，账户B和C

    START TRANSACTION;

    INSERT INTO accounts (account_id, account_name, balance) VALUES (4, 'D', 500);
    -- 提交事务，新增了一个余额为500的账户D

    COMMIT;

    SELECT * FROM accounts WHERE balance = 500;

    SELECT * FROM accounts WHERE balance = 500;

    COMMIT;

3.2、SQL标准中的四种隔离级别

之前我们对并发事务执行过程中常见的一些问题进行了概述。这些问题是按重要程度有高有低的。为了便于管理,我们需要对它们进行排序

    脏读 > 不可重复读 > 幻读

我们之前提到的放弃部分隔离特性，在这里体现为：设置一定数量的隔离等级，在这些等级中最低级别的隔离强度越弱，则可能导致严重问题的可能性越大。

SQL-92标准规范了4种隔离级别以应对脏读、幻读以及不可重复读等情况。这些隔离级别从高到低依次包括：可串行化（Serializable）、可重复读（Repeatable reads）、提交reads（Read committed）以及未提交reads（Read uncommitted）。

在SQL标准中明确规定了针对不同的隔离级别，并发事务可能出现的不同严重程度的问题，在具体情况具体分析时需特别注意。

READ UNCOMMITTED隔离级别下，可能发生脏读、不可重复读和幻读问题。

在READ COMMITTED隔离级别的条件下, 可能存在不可重复读与幻读的问题；然而, 在这种情况下不可能存在脏读问题.

当采用REPEATABLE READ隔离级别时，在这种情况下可能会存在幻读现象；然而，在这种情况下必须保证不会出现脏读或不可重复读的问题。

SERIALIZABLE隔离级别下，各种问题都不可以发生。

各不相同的隔离级别各自拥有独特的现象，并分别拥有不同的锁以及相关的并发机制。随着隔离级别越来越高，在数据库中的并发能力也随之下降。以下将详细阐述四种事务隔离级别与其对应的并发性能之间的关系：

3.3、设置MySQL中的隔离级别

查看隔离界别

    SHOW VARIABLES LIKE 'transaction_isolation';

    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

关于设置时使用GLOBAL或SESSION的影响：

使用GLOBAL 关键字（在全局范围影响）：

    SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

或者：

    SET GLOBAL TRANSACTION_ISOLATION = 'SERIALIZABLE';

则：

• 当前已经存在的会话无效
• 只对执行完该语句之后产生的会话起作用

使用SESSION 关键字（在会话范围影响）：

    SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

或者：

    SET SESSION TRANSACTION_ISOLATION = 'SERIALIZABLE';

则：

该方法对其会话中所有随后的操作均具有效应。
当在两个事务之间进行操作时，则会对随后的操作产生影响。
该指令在其间运行时将不会干扰到正在进行的任务。

小结：
数据库为系统设计了多种事务隔离级别。每个隔离级别的实现都伴随着特定的性能影响。高隔离级别能够显著提升数据一致性，并发处理能力会有所下降。