详解MySQL的隔离级别及其工作原理

前言

在《MySQL 的数据库事务、ACID 特性以及实践案例》一文中，我们简单介绍了 MySQL 的数据库事务和 ACID 特性，这一篇文章从另外一个视角，从更细的事务隔离级别来分析 MySQL 在事务方面的工作原理。

在 20 世纪 80 年代初，计算机科学家 Andreas Reuter 和 Theo Harder 创造了 ACID 这一术语，用以描述一组与数据库事务相关的属性，这些属性旨在确保数据的可靠存储与完整性。

几乎所有现代数据库系统都是围绕 ACID 合规性构建的。通过遵守这些基本原则，无论是小型项目管理应用还是大型银行系统，企业都能放心地信任数据库中的数据。隔离级别及其相关概念是使 MySQL 能够满足 ACID 保证的核心要素。

本文将分解如何通过隔离级别来维护数据一致性，让多个客户端能够同时操作单一数据库。

什么是 MySQL 的隔离级别？

MySQL 的隔离级别是四种模式中的一种，用于控制 MySQL 会话在执行并发事务时应如何表现。

隔离级别直接与 ACID 数据库的隔离性要求相关，该要求规定事务的执行方式不应影响其他事务。根据对会话设置的隔离级别，MySQL 使用各种锁机制来管理事务在任何时间点对哪些数据有访问权限。这也决定了在并发事务运行时可能出现的各种异常（违例）。

简单了解了 MySQL 隔离级别的定义及其优势后，我们将进一步看看不同的隔离级别、事务、违例、锁及它们之间的关联。

什么是 ACID 合规性？

在深入探讨 MySQL 如何保持数据一致性之前，先简要说明一下组成 ACID 的概念：

原子性 (Atomicity)

原子性意味着多个 SQL 操作可以作为一个单元执行（称为事务）。

MySQL 中的事务用于作为一个整体执行一个或多个语句，以满足 ACID 数据库的原子性要求。在事务中执行的语句在事务提交之前不会对数据库造成实际更改。如果事务中出现问题，可以回滚任何更改，从而防止提交后对数据造成问题。

默认情况下，MySQL 的自动提交开启，这表示单独发出的语句会被自动提交，无需手动启动事务。

一致性 (Consistency)

一致性确保数据库能够可靠地从一个状态迁移到另一个状态，避免任何不良影响或数据损坏。这通过约束、级联效果、触发器等功能来实现。

隔离性 (Isolation)

隔离性规定事务必须独立、有序地执行，因为通常一个数据库会连接多个客户端和线程，并执行并发事务。MySQL 的隔离级别直接与 ACID 合规性的这个部分相关。

持久性 (Durability)

持久性意味着一旦事务完成，数据可以通过持久存储来抵御系统故障和中断。例如，Memcached 将所有数据存储在内存中，因此不满足持久性要求。

简单示例

我们使用一个在线 CD 商城作为例子，它销售一种古老的音频载体——“光盘”。

该数据库架构包括以下四个实体：

• products：包含每种 CD 的库存，包括名称、艺术家及可用数量。
• unit_status：列出每种 CD 的当前状态，包括数量是否可用、是否已被预订或待发货。
• customer_transactions：记录销售交易的时间点。
• customers：记录所有购买过 CD 的客户信息。

以下是数据库的结构图：

隔离性违例

事务隔离性违例指的是事务并发执行时可能出现的各种读操作异常，隔离级别旨在避免这些异常。根据选择的 MySQL 隔离级别（后文将逐一介绍），可能会允许一个或多个违例，以满足数据库性能及查询一致性的需求。

让我们看看常见的三种违例：

脏读 (Dirty Read)

脏读指一个事务中的查询读取到另一个未提交事务的更改数据。

例如，两名客户希望购买同一张 CD，此时该 CD 有两张可用。第一位客户买下两张 CD，该操作要求将两张 CD 从状态available改为sold_not_shipped，并减少可用库存数量。而与此同时，第二位客户查看库存状态，系统仍显示有两张 CD 可供购买。

相关事务如下：

start transaction;
update unit_status set quantity = quantity + 2 where product_id = 20 and status = 'sold_not_shipped';
update unit_status set quantity = quantity - 2 where product_id = 20 and status = 'available';
commit;

如果在上述事务提交之前执行以下查询，则结果将显示仍有两张库存可用：

select quantity from unit_status where product_id = 20 and status = 'available';

从表面上看，这似乎不是问题，因为数据库反映了unit_status表中的两个记录修改。但如果事务最终回滚而非提交，则之前返回的数据将会不准确，因为更改已被撤销，但客户看到的信息却没有更新。

不可重复读 (Non-repeatable Read)

不可重复读指一个事务中的多次查询读取同一记录时，结果因另一个事务修改了数据而不同。

例如，商店老板两次查询同一 CD 的可用数量，但因客户购买了 CD，查询结果前后不一致：

start transaction;

-- 第一次查询，返回 15
select quantity from unit_status where product_id = 20 and status = 'available';

-- 第二次查询，返回 13（因另一个事务导致）
select quantity from unit_status where product_id = 20 and status = 'available';

commit;

导致不可重复读的是以下客户购买事务：

start transaction;

update unit_status set quantity = quantity + 2 where product_id = 20 and status = 'sold_not_shipped';
update unit_status set quantity = quantity - 2 where product_id = 20 and status = 'available';

commit;

幻读 (Phantom Read)

幻读指同一事务中的两个查询返回的记录数不同，因为另一事务在第一事务完成之前插入了新记录。

例如，商店老板希望统计所有产品的数量，执行以下事务：

start transaction;

-- 第一次查询，返回 100
select count(*) from products;

-- 第二次查询，返回 101（因另一事务插入新记录）
select count(*) from products;

commit;

与此同时，员工收到新 CD 并将其加入系统，使用以下事务：

start transaction;

insert into products set album = 'The Battle Of Los Angeles', artist = 'Rage Against The Machine', release_year = 1999, cost = 1500;

commit;

因为unit_status表中新增的记录导致前后查询结果不同。

锁与隔离的关系

事务隔离通过使用各种锁机制来实现。这些锁标记可以应用于表中的记录或整个表，以防止数据被读取或修改。

MySQL 常使用两类锁：共享锁（Shared Lock）和排他锁（Exclusive Lock）。

共享锁

共享锁允许事务读取数据但防止数据更改。多个事务可以同时创建共享锁，数据修改需要释放所有共享锁后才能进行。

排他锁

排他锁允许事务独占读取或写入数据。如果其他事务试图读取或修改该行数据，则会被阻止。这对防止违例非常有用，特别是预期数据在事务中将被更新时。

间隙锁 (Gap Lock)

间隙锁是专门用于防止幻读的锁。它会锁定查询条件匹配数据附近的空间，以防止插入新行改变查询结果。

例如，商店老板想批量更新 1999 年发行的 CD：

start transaction;
select * from products where release_year = 1999 for update;
update products set cost = 800 where release_year = 1999;
commit;

如果员工试图插入 1999 年的新 CD，MySQL 会让插入等待：

insert into products (album, artist, release_year, cost)
        values ('The Battle Of Los Angeles', 'Rage Against The Machine', 1999, 1500);

MySQL 的四种隔离级别

四种隔离级别分别是：

Read Uncommitted

最低隔离级别，允许所有违例（如脏读）。适用于性能优先于数据一致性的场景（例如社交媒体的点赞数查询）。

Read Committed

防止脏读。每次查询使用最新提交的数据快照。本隔离级别仍允许不可重复读和幻读违例。

Repeatable Read

默认隔离级别，通过 MVCC 确保事务中的查询固定在一个事务 ID 生成的快照中，防止所有三种违例。

Serializable

防止所有违例，同时对服务器性能影响最大，隐式为所有查询添加共享锁。

如何设置 MySQL 隔离级别

可以通过以下语句在会话级别设置隔离级别：

set transaction isolation level read uncommitted;
set session transaction isolation level read committed; -- 'session' 是可选的

管理员权限可设置全局默认隔离级别：

set global transaction isolation level serializable;

还可通过 MySQL 启动参数或配置文件设置：

[mysqld]
transaction-isolation = REPEATABLE-READ

结论

MySQL 通过多个概念的结合，确保其符合 ACID 要求，成为可靠和可信赖的数据库。隔离级别能够根据具体需求精确调节安全性与性能的平衡。

文章来源公众号：师兄奇谈

以上关于详解MySQL的隔离级别及其工作原理的文章就介绍到这了，更多相关内容请搜索码云笔记以前的文章或继续浏览下面的相关文章，希望大家以后多多支持码云笔记。