colorfulstone
MySQL的ACID是怎么实现的

1. 数据库数据进行更改操作流程：

  • 数据库大部分的操作（比如Update、Delete、insert)在事务没有提交前，修改的数据页都是在内存缓冲区buffer pool进行的；
  • 修改记录会被写入到redolog和undolog中
  • 执行事务提交时，redolog内容会被强制刷盘持久化到磁盘，但数据页仍在内存缓冲区，之后由后台进程异步持久化到磁盘

2. Redolog和undolog作用

Redolog主要作用：是确保事务的持久性，保证已提交的数据不丢失

Redolog应用时机：事务提交后

Redolog工作原理：数据首先在内存缓冲区执行更新操作 -》相应的操作会被记录到Redolog中 -》执行事务提交时，Redolog中日志先持久化到磁盘，数据页可以延迟刷盘；正常情况下，数据库会有异步进程将脏页（脏页就是数据已提交但还没有刷盘的数据页）进行刷盘；数据库宕机重启后，由于脏页丢失，此时会根据redolog中SLN1和磁盘中LSN2对比，将大于LSN2小于等于LSN1数据进行刷盘操作，从而避免数据丢失问题

Redolog关键特性：崩溃恢复，数据库宕机重启后，对于已提交事务的数据，因为数据页会比Redolog刷盘之后进行，会有可能将已提交但没有持久化到磁盘的数据丢失，此时会检查Redolog中LSN（日志序列号）和磁盘中LSN，将没有刷盘的数据进行恢复

Redolog使用场景：

　　防止数据丢失：只要事务提交，redolog就以落盘，即使数据还没有落盘数据库宕机也不会造成数据丢失

　　提升性能：

Undolog主要作用：确保事务的原子性，它主要是记录数据修改前状态，保证在事务提交前，数据库崩溃可以通过undolog进行数据恢复

undolog应用时机：事务提交前

Undolog工作原理：

　　当执行Insert操作时，Undolog会记录对应delete操作；当执行Delete操作时，undolog会记录对应insert操作；当执行Update操作时，undolog会记录update(原值)操作

　　在需要回滚时，就会按照undolog中记录进行操作

　　MVCC机制：当对某一条数据进行更新操作过程中还未提交过程中，如果没有多版本控制MVCC此时会阻塞，此时InnoDB会通过undolog中版本链找到该数据的历史版本

-- 事务A
BEGIN;
UPDATE accounts SET balance = 900 WHERE id = 1;
-- 未提交

-- 事务B（另一个会话）
SELECT balance FROM accounts WHERE id = 1;
-- 读取到1000（通过Undo Log找到修改前的版本）

　　

Undolog工应用场景：

　　回滚：

　　快照读：高并发场景下，保证读操作不会被写操作阻塞，且能看到一致性的数据视图。如，你在查询一张大表时，其他事务在更新这张表，你看到的依然是查询开始时的数据状态。

Undolog数据结构：

Undo Log Segment:
┌─────────────────────────────────────┐
│ Undo Log Record for Transaction 123 │
├─────────────────────────────────────┤
│ Table: user                         │
│ Row ID: 1                           │
│ Before: {money: 500}                │ ← 用于回滚的关键数据
│ After:  {money: 400}                │
│ Operation: UPDATE                   │
│ Transaction ID: 123                 │
│ Roll Pointer: ...                   │
└─────────────────────────────────────┘

　　

3 既然在事务提交前数据库崩溃，这些数据没有持久化到磁盘，那又为什么需要使用到undolog进行数据恢复呢

根据第2节内容，undolog是在数据修改后事务提交前发生宕机场景下，进行数据恢复。那由于事务没有提交，所以修改只发生在内存缓冲区而磁盘中数据实际上还没有修改。此时，如果不通过undolog恢复会造成一下问题：

 a)内存缓冲区和磁盘数据不一致：由于数据还未提交，所以修改操作只在内存缓冲区进行

 b)数据库宕机重启后，会根据redolog和磁盘lsn进行对比进行刷盘操作，由于redolog中lsn是递增的，如果在事务1未提交后，又有一个事务2进行了事务提交，此时redolog中lsn就会更新到事务2，并且事务2更新的lsn是要比事务1大的，从而造成事务1未提交的数据也会被刷新到磁盘

4. MySQL ACID是如何实现的

原子性（A）事务中的操作要么全执行，要么全不执行（比如转账：扣钱 + 加钱必须同时成功 / 失败）

一致性（C）事务执行前后，数据库的完整性约束（如主键唯一、金额总和不变）保持不变

隔离性（I）多个并发事务之间相互隔离，互不干扰（避免脏读、不可重复读、幻读）

持久性（D）事务提交后，修改永久生效，即使数据库崩溃也不会丢失

原子性实现：undolog

-- 事务执行过程示例
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 步骤1
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 步骤2
-- 如果此处系统崩溃...
COMMIT;  -- 或 ROLLBACK;

实现原理：

数据修改前：先将原始数据复制到Undo Log

事务提交前：所有修改在内存中进行

事务提交时：写入Redo Log（两阶段提交）

事务回滚时：从Undo Log恢复原始数据

系统崩溃恢复：检查未提交事务，使用Undo Log回滚

　　

永久性实现 redolog

数据首先在内存缓冲区执行更新操作 -》相应的操作会被记录到Redolog中 -》执行事务提交时，Redolog中日志先持久化到磁盘，数据页可以延迟刷盘，就算数据库宕机重启后，会根据redolog中SLN1和磁盘中LSN2对比，将大于LSN2小于等于LSN1数据进行刷盘操作，从而避免数据丢失问题

隔离性实现 -- 锁机制+MVCC

-- MySQL支持的隔离级别
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 未提交读
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 提交读（默认）
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 可重复读（InnoDB默认）
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 序列化

锁机制：

  • 表级锁：锁住单行数据（InnoDB 支持，MyISAM 不支持），比如执行 UPDATE user SET money=100 WHERE id=1 时，只锁住 id=1 的行，其他行可正常操作
  • 行级锁：锁住整张表（MyISAM 默认，InnoDB 也可手动加），性能差，仅适用于批量操作；
  • 间隙锁：锁住数据之间的 “间隙”（比如 id=1、3 之间的间隙），防止幻读（比如插入 id=2 的数据）。
    • 　　 
      -- 事务A
BEGIN;
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
-- 假设返回：id=5(age=25)

-- 如果没有间隙锁，事务B可以：
INSERT INTO users (id, age) VALUES (6, 26);  -- age=26在20-30之间！
-- 事务A再次查询会看到新记录 → 幻读！

-- 有间隙锁：锁定(20, 30)这个间隙
-- 事务B的INSERT会被阻塞　　
  • 排他锁（写锁）：独占访问权，其他事务不能读也不能写
    • 　　 
      -- 自动加排他锁
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 自动在id=1的记录上加X锁
-- 其他事务不能读（脏读除外）也不能写这条记录　　

      SELECT * FROM orders WHERE user_id = 100 FOR UPDATE;
  • 共享锁：允许多个事务同时读取同一数据
    • 　　 
      -- 事务1：获取共享锁（读锁）
BEGIN;
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- 可以继续持有锁...

-- 事务2：也可以获取共享锁
BEGIN;
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- ✅ 成功，共享锁兼容

-- 事务3：尝试获取排他锁
BEGIN;
UPDATE users SET name = 'Alice' WHERE id = 1;
-- ❌ 等待，直到事务1和2释放锁

      　　

MVCC（多版本并发控制）：解决 “读 - 写冲突”

MVCC 是 InnoDB 实现「读未提交、读已提交、可重复读」隔离级别的核心，让读操作不用加锁，提升并发性能：

  • 核心原理：
    1. 每行数据除了存储实际值，还会记录「创建版本号」（事务 ID）和「删除版本号」（事务 ID）；
    2. 事务读取数据时，只读取 “版本号小于等于当前事务 ID” 且 “删除版本号未定义 / 大于当前事务 ID” 的数据版本；
    3. 事务修改数据时，不会直接覆盖原数据，而是生成一个新的数据版本（保留旧版本，供其他事务读取）
  • 作用：比如事务 A 修改数据时，事务 B 仍能读取修改前的旧版本，避免 “脏读”，同时不用加锁，提升并发效率。

一致性实现：数据完整性约束+MySQL约束处理机制

数据库层面

-- 1. 实体完整性（Entity Integrity）
CREATE TABLE users (
    id INT PRIMARY KEY,           -- 主键约束
    username VARCHAR(50) UNIQUE,  -- 唯一约束
    age INT NOT NULL              -- 非空约束
);

-- 2. 参照完整性（Referential Integrity）
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE            -- 级联删除
    ON UPDATE RESTRICT           -- 限制更新
);

-- 3. 域完整性（Domain Integrity）
CREATE TABLE products (
    id INT PRIMARY KEY,
    price DECIMAL(10,2) CHECK (price > 0),  -- 检查约束
    status ENUM('ACTIVE', 'INACTIVE') DEFAULT 'ACTIVE',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. 用户定义完整性（User-defined Integrity）
DELIMITER $$
CREATE TRIGGER check_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 3000 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '薪资不能低于3000';
    END IF;
END$$
DELIMITER ;

　　

 

posted on 2026-01-13 20:25  colorfulworld  阅读(3)  评论(0)    收藏  举报

博客园  ©  2004-2026
浙公网安备 33010602011771号 浙ICP备2021040463号-3 Powered By博客园