本页内容受版权保护 · 已添加水印 · 禁止任何形式转载
事务与并发（ACID / 隔离级别 / 锁 / MVCC） · ACID 与隔离级别

如何在会话级别 / 全局设置隔离级别？SET TRANSACTION ISOLATION LEVEL 语法？

How to set the isolation level at the session/global level? SET TRANSACTION ISOLATION LEVEL syntax

答案语言

考察要点

这道题面试官想看你是否扎实掌握了数据库事务的 ACID 四大特性之一的隔离性（Isolation）。具体来说，考察你对以下几点的理解和实践能力：

  1. SQL 标准与 MySQL 实现：是否清楚 SQL-92 标准定义的四种隔离级别，以及 MySQL (InnoDB) 的默认级别和其特殊实现（如通过 MVCC 和 Gap Lock 防止幻读）。
  2. 作用域与语法：是否能准确区分 GLOBAL、SESSION 以及事务级别的设置，并写出正确的 SQL 语法。
  3. 权衡与选型：是否理解不同隔离级别在数据一致性、并发性能和锁开销之间的权衡，并能在特定业务场景下做出合理解释和选择。

核心概念

事务隔离级别用于定义一个事务中的修改，在何种程度上对其他并发事务是可见的。MySQL/InnoDB 引擎支持 SQL-92 标准的四种隔离级别。

1. 四种隔离级别与解决的问题

隔离级别脏读 (Dirty Read)不可重复读 (Non-Repeatable Read)幻读 (Phantom Read)
读未提交 (READ UNCOMMITTED)可能可能可能
读已提交 (READ COMMITTED)❌ 不会可能可能
可重复读 (REPEATABLE READ)❌ 不会❌ 不会❌ 基本不会¹
可串行化 (SERIALIZABLE)❌ 不会❌ 不会❌ 不会
  • 脏读：一个事务读取到另一个事务未提交的数据。
  • 不可重复读：在同一事务内，两次读取同一行数据，得到的结果不同。侧重于 UPDATE。
  • 幻读：在同一事务内，两次执行相同范围的查询，得到的记录数量不同。侧重于 INSERT 和 DELETE。

¹ 注意：MySQL 的 InnoDB 引擎在 REPEATABLE READ 级别下，通过多版本并发控制（MVCC）和间隙锁（Gap Lock）技术，很大程度上解决了幻读问题，已经接近于 SERIALIZABLE 的一致性，但性能远超后者。这是 MySQL 的一个重要特性，也是面试高频考点。

2. 设置隔离级别的语法与作用域

MySQL 提供了三种作用域来设置隔离级别：GLOBAL, SESSION, 和单次事务。

DDL 示例：

sql
 1CREATE TABLE `account` (
 2  `id` INT PRIMARY KEY,
 3  `balance` DECIMAL(10, 2)
 4) ENGINE=InnoDB;

a. 全局级别 (GLOBAL)

  • 作用：修改数据库的全局默认隔离级别。只对执行该语句之后新建立的连接生效，不影响已存在的连接。
  • 权限：需要 SUPER 或 SYSTEM_VARIABLES_ADMIN 权限。
  • 语法：
    sql
     1-- 设置全局隔离级别为 READ COMMITTED
     2SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

b. 会话级别 (SESSION)

  • 作用：修改当前连接（会话）的隔离级别。该设置在当前连接关闭前一直有效。这是最常用的方式。
  • 语法：
    sql
     1-- 设置当前会话隔离级别为 READ COMMITTED
     2SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
     3

     4-- SESSION 关键字可以省略，效果相同
     5SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

c. 下一个事务级别 (Transaction-specific)

  • 作用：只对下一个即将开始的事务生效。事务结束后，隔离级别会恢复为会话级别。
  • 语法：
    sql
     1-- 只为下一个事务设置隔离级别
     2SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
     3

     4START TRANSACTION;
     5-- ... 此处执行的 SQL 将在 SERIALIZABLE 级别下运行 ...
     6COMMIT;
     7

     8-- 在此之后开始的新事务，将恢复为之前的会话级别（如 REPEATABLE READ）
     9START TRANSACTION;
    10-- ...
    在 MySQL 8.0.3 及更高版本中，也可以在 START TRANSACTION 语句中直接指定：
    sql
     1-- MySQL 8.0.3+
     2START TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, READ WRITE;
     3-- ...
     4COMMIT;

3. 查看当前隔离级别

可以使用系统变量来查询。

sql
 1-- 查看当前会话隔离级别
 2SELECT @@SESSION.transaction_isolation;
 3-- 或者 (@@tx_isolation 在 MySQL 8.0 中已废弃，但仍可用)
 4SELECT @@tx_isolation;
 5

 6-- 查看全局隔离级别
 7SELECT @@GLOBAL.transaction_isolation;

常见低分回答

  1. 混淆 GLOBAL 和 SESSION 的作用范围。错误地认为 SET GLOBAL ... 会立即影响所有当前正在运行的会话。正确答案是：只影响新建立的连接。
  2. 语法错误。例如，使用 SET ISOLATION LEVEL = 'REPEATABLE READ'（使用了等号）或 SET TRANSACTION LEVEL ...（漏了 ISOLATION 关键字）。这是与其他数据库（如 PostgreSQL SET TRANSACTION ISOLATION LEVEL ... 或 Oracle SET TRANSACTION ISOLATION LEVEL ...;）语法混淆的表现。
  3. 认为 SET TRANSACTION ... 会永久改变会话级别。错误地认为执行一次 SET TRANSACTION ISOLATION LEVEL ... 后，当前会话的所有后续事务都会使用该级别。正确答案是：它只对下一个事务生效。
  4. 不清楚 MySQL 的默认值。当被问到 MySQL 默认隔离级别时，回答成 READ COMMITTED。这是 Oracle、PostgreSQL 等数据库的默认值，但 MySQL (InnoDB) 的默认值是 REPEATABLE READ。
  5. 随意建议修改全局级别。在解决某个特定业务的并发问题时，直接建议 SET GLOBAL ...，这暴露了候选人缺乏生产环境经验。修改全局配置是高危操作，会影响所有连接到该实例的应用，应优先考虑在代码中为特定业务场景设置会话级别。

工程实践与优化

  • 最小权限原则：始终优先使用范围最小的设置。绝大多数场景下，应在应用代码的数据库连接或事务管理处，通过 SET SESSION TRANSACTION ISOLATION LEVEL ... 来精细化控制，而不是粗暴地修改全局 GLOBAL 配置。
  • 性能与一致性的权衡：
    • 高并发、低一致性要求场景：例如，更新文章阅读数、非精确的统计看板等，可以考虑将隔离级别降为 READ COMMITTED。这可以减少间隙锁（Gap Lock）的使用，降低死锁概率，提升并发性能。
    • 金融级、数据强一致性场景：如转账、库存扣减，必须保证数据准确性。应使用默认的 REPEATABLE READ，甚至在必要时使用 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE 等悲观锁来确保数据在事务处理过程中的绝对一致。
  • 排查死锁：当业务中出现由间隙锁导致的死锁时，一个常见的优化思路就是分析业务逻辑，看是否能安全地将相关事务的隔离级别降为 READ COMMITTED。
  • 查看执行计划：虽然 EXPLAIN 不直接显示隔离级别信息，但隔离级别会影响锁的行为，从而影响执行计划。例如，在 REPEATABLE READ 下，一个范围更新可能会因为间隙锁而阻塞更多操作。
  • 监控工具：使用 SHOW ENGINE INNODB STATUS 可以查看最近的死锁日志，其中包含了事务信息、持有的锁和等待的锁，有助于分析隔离级别和锁策略是否合理。information_schema.innodb_trx 表可以查看当前正在运行的事务及其隔离级别。

常见追问与延伸题目

  1. 追问：为什么 MySQL 的默认隔离级别是 REPEATABLE READ，而很多其他主流数据库（如 Oracle, PostgreSQL）默认是 READ COMMITTED？

    • 回答要点：这主要是历史原因，与 MySQL 早期的主从复制技术有关。在早期，MySQL 主要依赖基于语句的复制（Statement-Based Replication, SBR）。SBR 会将主库执行的 SQL 语句原封不动地在从库上重新执行。如果使用 READ COMMITTED 级别，主库上的一条语句（如 UPDATE ... WHERE condition）在执行时所依赖的数据集合，可能与从库在稍后执行该语句时看到的数据集合不同（因为并发事务的提交），这会导致主从数据不一致。而 REPEATABLE READ 提供了更强的一致性保证，确保一个事务中读取到的数据是一致的，从而保证了 SBR 的正确性。虽然现在**基于行的复制（Row-Based Replication, RBR）**已成为默认且被推荐的方式，它不存在这个问题，但 REPEATABLE READ 这个默认值被保留了下来。

  2. 追问：如果我把隔离级别从 REPEATABLE READ 降到 READ COMMITTED，我的应用可能会遇到什么新问题？请举例说明。

    • 回答要点：会遇到不可重复读和幻读问题。
      • 不可重复读示例：一个财务审核流程。事务A首先读取订单号为 O100 的金额为 1000 元，然后进入人工审核步骤。此时，事务B修改了该订单金额为 800 元并提交。事务A在人工审核通过后，再次读取订单 O100 的金额，发现变成了 800 元，与初始读取不一致，可能导致后续逻辑错误。
      • 幻读示例：一个统计报表任务。事务A首先执行 SELECT COUNT(*) FROM sales WHERE month = '2023-10'，得到结果 50 条。接着，事务A开始进行复杂的计算。在此期间，事务B插入了一条新的 10 月份销售记录并提交。事务A计算完毕后，为了做最终校验，再次执行 SELECT COUNT(*) FROM sales WHERE month = '2023-10'，得到结果 51 条。这个“幻影”般多出来的记录，就可能导致报表总数与明细对不上的问题。

  3. 追问：在 REPEATABLE READ 级别下，MySQL 是如何防止幻读的？完全杜绝了吗？

    • 回答要点：MySQL (InnoDB) 主要通过两种机制结合来防止幻读：
      1. MVCC (多版本并发控制)：对于普通的 SELECT 查询，InnoDB 会通过 MVCC 创建一个一致性视图（Read View）。事务启动时会记录当前活跃的事务 ID 列表，后续查询只能看到不晚于这个时间点已提交的数据，从而保证了在事务执行期间，其他事务所做的 INSERT、UPDATE、DELETE 对当前事务的普通查询是不可见的，实现了“可重复读”。
      2. Next-Key Lock (临键锁)：对于 SELECT ... FOR UPDATE、UPDATE、DELETE 等当前读操作，InnoDB 会使用锁来保证一致性。它使用的 Next-Key Lock 是**记录锁（Record Lock）和间隙锁（Gap Lock）**的结合体。它不仅会锁定查询到的记录本身，还会锁定记录前的那个“间隙”，防止其他事务在这个间隙中插入新数据，从而防止了幻读。
      • 是否完全杜绝：在绝大多数情况下是的。但在某些极端并发场景下，所谓的“幻读”现象仍可能发生，但这通常与用户对幻读的定义和事务的复杂交互有关，对于标准的幻读定义，Next-Key Lock 已经提供了非常强大的保护。面试时可以回答“在标准SQL场景下，通过MVCC和Next-Key Lock基本杜绝了幻读”。
← Previous
RR（可重复读）是否解决了幻读？InnoDB 是怎么用 next-key lock 处理幻读的？
Next →
什么是快照读和当前读？SELECT 普通查询是哪种？SELECT ... FOR UPDATE / LOCK IN SHARE MODE？