MySQL基础事务机制第9章揭秘

MySQL基础事务处理的第九篇章剖析

标题：[MySQL入门]MySQL（9）事务机制概览
@墨白不码错

文章目录

• 一、认识事务
  • 1、多线程访问数据库引发的状况
  • 2、通过事务机制限制CURD操作
  • 3、事务的四大特性
  • 4、支持事务的引擎
• 二、事务的提交与autocommit设置
• 三、事务的隔离性与隔离级别
  • (1) 查看当前隔离级别
  • (2) 设置隔离级别
  • （3） 事务隔离级别的作用
  • （4）MySQL InnoDB的四种隔离级别
  • (1) 读未提交（Read Uncommitted）
  • (2) 读已提交（Read Committed）
  • (3) 可重复读（Repeatable Read）
  • (4) 串行化（Serializable）
  • （5）各隔离级别对比表
• 四、事务的一致性
  • • 1. AID特性和一致性的关联
  • • (1) 原子性（Atomicity）
    • (2) 隔离性（Isolation）
    • (3) 持久性（Durability）
    • (4) 应用层面的逻辑（用户的协同）

一、认识事务

1、多线程访问数据库引发的状况

MySQL作为网络服务，必然存在多个客户端同时访问服务器的情形：设想有一个抢票系统，两个客户端同时抢票，其中一个客户端完成抢票操作后，数据库尚未及时更新，此时另一客户端又进行了抢票，导致一张票被重复售卖，这显然不符合正常逻辑！因此，必须对数据库的CURD操作实施特定限制，以此解决此类问题。

2、通过事务机制限制CURD操作

事务由一组具有逻辑关联性的DML语句构成，这些语句要么全部成功执行，要么全部失败回滚，形成一个整体。事务常用于处理操作量大、复杂度高的数据。例如，要删除一位被开除员工的所有历史信息及相关联信息，需多条MySQL语句协同完成，这些操作共同构成一个事务。

然而，同一时刻并非仅有一个事务在运行，而是有多个事务并发执行。若大量事务在无保护的情况下访问同一表数据，必然引发问题。因为事务由多条SQL语句组成，若事务执行中途MySQL客户端异常退出，已执行一半的事务该如何处理？

3、事务的四大特性

MySQL规定，一个完整的事务不仅是简单SQL语句的组合，还需满足以下四大特性：

原子性（Atomicity，也可称为不可分割性）：
一个事务（transaction）内的所有操作，要么完全执行完毕，要么完全不执行，不会停留在中间状态。若事务执行过程中出现错误（比如客户端异常退出），已执行一半的事务会被回滚至事务开始前的状态，仿佛该事务从未执行过。

一致性（Consistency）：
在事务开启前和结束后，数据库的完整性均未遭到破坏。

隔离性（Isolation，也称作独立性）：
数据库支持多个并发事务同时对数据进行读写与修改，隔离性能够避免多个事务并发执行时因交叉执行而致使数据不一致。事务隔离涵盖不同级别，包含读未提交（Read uncommitted）、读已提交（read committed）、可重复读（repeatable read）以及串行化（Serializable）。

持久性（Durability）：
事务处理完成后，对数据的改动会永久保存，即便系统发生故障也不会丢失。

此外，事务本质上是为应用层服务的。因为我们并非直接访问数据库，而是通过上层各种语言来访问数据库，所以多条SQL语句封装而成的事务能够简化上层的编程模型与逻辑。

4、支持事务的引擎

使用指令查询：

mysql> show engines\G;
*************************** 1. row ***************************
          Engine: ndbcluster
         Support: NO
         Comment: Clustered, fault-tolerant tables
    Transactions: NULL
              XA: NULL
      Savepoints: NULL
*************************** 2. row ***************************
          Engine: MEMORY
         Support: YES
         Comment: Hash based, stored in memory, useful for temporary tables
    Transactions: NO
              XA: NO
      Savepoints: NO
*************************** 3. row ***************************
          Engine: InnoDB
         Support: DEFAULT
         Comment: Supports transactions, row-level locking, and foreign keys
    Transactions: YES
              XA: YES
      Savepoints: YES
*************************** 4. row ***************************
          Engine: PERFORMANCE_SCHEMA
         Support: YES
         Comment: Performance Schema
    Transactions: NO
              XA: NO
      Savepoints: NO
*************************** 5. row ***************************
          Engine: MyISAM
         Support: YES
         Comment: MyISAM storage engine
    Transactions: NO
              XA: NO
      Savepoints: NO
*************************** 6. row ***************************
          Engine: FEDERATED
         Support: NO
         Comment: Federated MySQL storage engine
    Transactions: NULL
              XA: NULL
      Savepoints: NULL
*************************** 7. row ***************************
          Engine: ndbinfo
         Support: NO
         Comment: MySQL Cluster system information storage engine
    Transactions: NULL
              XA: NULL
      Savepoints: NULL
*************************** 8. row ***************************
          Engine: MRG_MYISAM
         Support: YES
         Comment: Collection of identical MyISAM tables
    Transactions: NO
              XA: NO
      Savepoints: NO
*************************** 9. row ***************************
          Engine: BLACKHOLE
         Support: YES
         Comment: /dev/null storage engine (anything you write to it disappears)
    Transactions: NO
              XA: NO
      Savepoints: NO
*************************** 10. row ***************************
          Engine: CSV
         Support: YES
         Comment: CSV storage engine
    Transactions: NO
              XA: NO
      Savepoints: NO
*************************** 11. row ***************************
          Engine: ARCHIVE
         Support: YES
         Comment: Archive storage engine
    Transactions: NO
              XA: NO
      Savepoints: NO
11 rows in set (0.01 sec)

从查询结果可知，目前仅有InnoDB引擎支持事务机制。

二、事务的提交与autocommit设置

事务的提交（commit）方式存在两种：
自动提交模式；
手动提交模式；
autocommit变量用于设定是否开启自动提交。
查看autocommit变量：

show variables like 'autocommit';

设置自动提交状态：

SET AUTOCOMMIT=1; #SET AUTOCOMMIT=1 开启自动提交功能
SET AUTOCOMMIT=0; #SET AUTOCOMMIT=0 关闭自动提交功能

若要开启一个事务，需使用以下指令：

begin;
#或者
start transaction;

若要提交一个事务，需使用指令：

commit;

这两个指令之间的SQL语句即为该事务的具体内容。

为便于后续演示，创建如下表结构：

create table if not exists account(
    id int primary key,
    name varchar(50) not null default '',
    blance decimal(10,2) not null default 0.0
    )ENGINE=InnoDB DEFAULT CHARSET=UTF8;

此时暂时将全局隔离级别设置为读未提交（以便通过另一客户端查看表中数据）：

set global transaction isolation level READ UNCOMMITTED;

查看隔离级别：

SELECT @@global.transaction_isolation AS '全局隔离级别', @@session.transaction_isolation AS '当前会话隔离级别';

重启MySQL，同步当前会话与全局的隔离级别后查看：

• 特性一 ：若事务通过begin/start transation方式开启，则必须通过commit提交才能持久化，这与autocommit设置无关（默认情况下autocommit为ON开启状态，具体作用后文讲解）。

若有两个客户端同时访问同一张表，且同时开启两个事务，其中一个客户端事务执行至一半时崩溃，MySQL会自动回滚。

何为回滚？
当开启一个事务后，在事务执行过程中，可创建保存点，此保存点可类比为游戏中的存档点，若对最新操作不满意，可回滚（读取存档点）：

savepoint s1; #创建一个保存点s1

首先，通过begin；开启一个事务。
然后，向已创建的表中插入数据：

此时设置一个savepoint s1；

接着进行一些增删改操作（例如新插入一条数据）：

若发现新进行的操作出现错误且不想要该结果，可回滚（读取存档点s1）:

rollback to s1;

结果：

于是，新插入的“haha”数据被删除。这便是回滚。

回归特性一的演示：
验证：未commit，客户端崩溃，MySQL自动回滚[因通过手动开启，autocommit不会自动提交] ：
开启两个事务，左侧插入一条新数据lisi，右侧可看到新插入的数据：

左侧事务的客户端被ctrl+\发送abort信号，进程被杀死，右侧客户端发现新插入的lisi被MySQL自动回滚。

验证：commit后，客户端崩溃，MySQL数据不受影响，已持久化[因通过手动开启，autocommit不会自动提交]

开启两个事务，左侧客户端插入数据jimmay，commit后被信号杀死，右侧查询可找到新插入的数据：

验证：未手动通过begin;/start transaction;开启事务，autocommit会影响提交。

在autocommit=1；（开启）情况下：
对于未手动通过begin;/start transaction;开启事务的情况，一条SQL语句即为一个事务，因为每条语句都会被封装为一个事务。
即使一条语句执行后客户端直接崩溃，该语句仍会被持久化：

在autocommit=0；（开启）情况下：
需手动commit，无论执行多少操作，若在客户端退出前未commit，则所有操作均会被回滚：

若commit，所做操作会被持久化：

至此，可总结如下：

• 事务通过begin/start transaction开启，需手动commit提交才能持久化，不受autocommit设置影响。
• 事务可通过设置savepoint手动回滚；操作异常时，MySQL会自动回滚。
• InnoDB每条SQL语言默认封装成事务，自动提交。（select有特殊情况，因MySQL有MVCC）。
• 从上述演示，可见事务本身的原子性(回滚)、持久性(commit)。

隔离性体现在何处？

三、事务的隔离性与隔离级别

数据库中，为保证事务执行过程中尽可能不受干扰，存在重要特性：隔离性 。
数据库中，允许事务受不同程度干扰，存在重要特性：隔离级别 。

(1) 查看当前隔离级别

SHOW VARIABLES LIKE 'transaction_isolation';
-- 输出示例：transaction_isolation = REPEATABLE-READ

(2) 设置隔离级别

-- 全局设置（重启后生效）
SET GLOBAL TRANSACTION ISOLATION LEVEL+隔离级别值;
-- 当前会话设置
SET SESSION TRANSACTION ISOLATION LEVEL+隔离级别值;

MySQL InnoDB存储引擎支持四种标准事务隔离级别，通过不同锁机制和 多版本并发控制（MVCC） 实现事务间数据可见性与并发控制。

（3） 事务隔离级别的作用

事务隔离级别定义了多个并发事务间的数据可见性规则，核心目标是解决以下问题：

• 脏读（Dirty Read） ：读取到其他事务未提交的数据。
• 不可重复读（Non-Repeatable Read） ：同一事务中多次读取同一数据，结果不一致（其他事务修改了该行）。
• 幻读（Phantom Read） ：同一事务中，因其他事务插入或删除符合查询条件的行，导致多次相同查询的结果集行数不一致。

（4）MySQL InnoDB的四种隔离级别

按隔离级别从低到高排序：

(1) 读未提交（Read Uncommitted）

• 特点 ：所有事务均可看到其他事务未提交的数据
  执行结果（可能读到“脏数据”）相当于无任何隔离性。

• 问题 ：存在 脏读、不可重复读、幻读 。

• 实现方式 ：几乎不加锁，依赖最低限度的锁机制。
• 使用场景 ：极少使用，仅在需最高并发且不关心数据一致性的场景。

(2) 读已提交（Read Committed）

• 特点 ：一个事务只能看到其他已提交事务所做的改变。
• 解决的问题 ：避免脏读。
• 遗留问题 ：存在 不可重复读、幻读 。
• 实现方式 ：
  • 锁机制 ：使用 行级锁（Record Locks） ，写操作锁定当前行。
  • MVCC ：每次 SELECT 生成独立快照（一致性视图），基于当前已提交数据。
• 使用场景 ：适用于大多数OLTP系统（如Oracle默认级别）。

(3) 可重复读（Repeatable Read）

• 特点 ：MySQL默认隔离级别，同一事务中多次读取同一数据结果一致。
• 解决的问题 ：避免脏读、不可重复读。
• 遗留问题 ：仍可能发生 幻读 （但InnoDB通过 间隙锁 基本消除）。
• 实现方式 ：
  • 锁机制 ：使用 记录锁（Record Locks） + 间隙锁（Gap Locks） （合称 临键锁 Next-Key Locks ）。
  • MVCC ：事务首次 SELECT 生成一致性视图，后续读取沿用该视图。
• 默认级别 ：InnoDB的默认隔离级别。
• 幻读的解决 ：

  -- 事务A
  BEGIN;
  SELECT * FROM users WHERE age > 20; -- 假设返回3条记录
  
  -- 事务B
  INSERT INTO users (id, age) VALUES (4, 25); -- 提交
  
  -- 事务A再次查询
  SELECT * FROM users WHERE age > 20; -- 在可重复读下，仍返回3条记录（避免幻读）
  

(4) 串行化（Serializable）

*