MVCC 与事务隔离级别

-- 查看当前会话隔离级别
SELECT @@transaction_isolation;

-- 设置会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 事务A:转账操作
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; -- 扣款
-- 此时事务A还未提交

-- 事务B:查询余额(READ UNCOMMITTED级别)
SELECT balance FROM accounts WHERE user_id = 1; 
-- 读到了900(未提交的值)

-- 事务A:回滚
ROLLBACK; -- 实际余额还是1000

-- 事务B基于错误的余额做业务决策,导致问题

-- 事务A:查询余额两次
BEGIN;
SELECT balance FROM accounts WHERE user_id = 1; -- 第一次读到1000

-- 事务B:更新并提交
BEGIN;
UPDATE accounts SET balance = 900 WHERE user_id = 1;
COMMIT;

-- 事务A:再次查询同一行
SELECT balance FROM accounts WHERE user_id = 1; -- 第二次读到900
COMMIT;

-- 事务A:统计订单
BEGIN;
SELECT COUNT(*) FROM orders WHERE user_id = 1; -- 第一次:10条

-- 事务B:插入新订单并提交
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (1, 100);
COMMIT;

-- 事务A:再次统计
SELECT COUNT(*) FROM orders WHERE user_id = 1; -- 第二次:11条
COMMIT;

┌─────────────────────────────────────────┐
│          MVCC 核心组件关系              │
├─────────────────────────────────────────┤
│                                         │
│  ┌──────────┐      ┌──────────┐       │
│  │ 聚簇索引  │─────▶│ 隐藏字段  │       │
│  │  (数据行) │      │ - DB_TRX_ID│       │
│  └──────────┘      │ - DB_ROLL_PT│      │
│       │            │ - DB_ROW_ID│       │
│       │            └──────────┘       │
│       ▼                   │            │
│  ┌──────────┐             │            │
│  │ Undo Log │◀────────────┘            │
│  │  (版本链) │                          │
│  └──────────┘                          │
│       │                                │
│       ▼                                │
│  ┌──────────┐                          │
│  │Read View │                          │
│  │(可见性判断)│                         │
│  └──────────┘                          │
│                                         │
└─────────────────────────────────────────┘

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

INSERT INTO users VALUES (1, '张三', 25);

┌────┬──────┬─────┬───────────┬──────────────┬────────────┐
│ id │ name │ age │ DB_TRX_ID │ DB_ROLL_PTR  │ DB_ROW_ID  │
├────┼──────┼─────┼───────────┼──────────────┼────────────┤
│ 1  │ 张三 │ 25  │  1001     │   NULL       │  (无需)    │
└────┴──────┴─────┴───────────┴──────────────┴────────────┘

-- 初始插入(事务ID=1001)
INSERT INTO users VALUES (1, '张三', 25);

-- 第一次更新(事务ID=1002)
UPDATE users SET age = 26 WHERE id = 1;

-- 第二次更新(事务ID=1003)
UPDATE users SET name = '李四', age = 27 WHERE id = 1;

当前数据行(最新版本)
┌────┬──────┬─────┬───────────┬──────────────┐
│ id │ name │ age │ DB_TRX_ID │ DB_ROLL_PTR  │
│ 1  │ 李四 │ 27  │  1003     │      ↓       │
└────┴──────┴─────┴───────────┴──────┼───────┘
                                     │
        ┌────────────────────────────┘
        ▼
Undo Log (版本1)
┌────┬──────┬─────┬───────────┬──────────────┐
│ id │ name │ age │ DB_TRX_ID │ DB_ROLL_PTR  │
│ 1  │ 张三 │ 26  │  1002     │      ↓       │
└────┴──────┴─────┴───────────┴──────┼───────┘
                                     │
        ┌────────────────────────────┘
        ▼
Undo Log (版本0)
┌────┬──────┬─────┬───────────┬──────────────┐
│ id │ name │ age │ DB_TRX_ID │ DB_ROLL_PTR  │
│ 1  │ 张三 │ 25  │  1001     │    NULL      │
└────┴──────┴─────┴───────────┴──────────────┘

-- 长事务开始
BEGIN;
SELECT * FROM users WHERE id = 1; -- 事务ID=2000

-- 其他事务持续更新(产生大量Undo Log)
-- UPDATE、INSERT、DELETE操作...

-- 长事务持续运行(不提交也不回滚)
-- 所有Undo Log都无法清理,导致:
-- 1. Undo Log空间膨胀
-- 2. 版本链过长,查询性能下降
-- 3. 系统整体性能下降

if (DB_TRX_ID == creator_trx_id) {
    // 自己修改的,可见
    return VISIBLE;
}

if (DB_TRX_ID < min_trx_id) {
    // 在Read View生成前已提交,可见
    return VISIBLE;
}

if (DB_TRX_ID >= max_trx_id) {
    // 在Read View生成后才开始,可见
    return VISIBLE;
}

if (m_ids.contains(DB_TRX_ID)) {
    // 还未提交,不可见
    return NOT_VISIBLE;
}

// 在Read View生成时已提交
return VISIBLE;

-- 事务A (RC级别)
BEGIN; -- 事务ID=100
SELECT * FROM users WHERE id = 1; -- 生成Read View-1

-- 事务B
BEGIN;
UPDATE users SET age = 30 WHERE id = 1; -- 事务ID=101
COMMIT;

-- 事务A再次查询
SELECT * FROM users WHERE id = 1; -- 生成Read View-2(能看到age=30)
COMMIT;

-- 事务A (RR级别)
BEGIN; -- 事务ID=100
SELECT * FROM users WHERE id = 1; -- 生成Read View-1

-- 事务B
BEGIN;
UPDATE users SET age = 30 WHERE id = 1; -- 事务ID=101
COMMIT;

-- 事务A再次查询
SELECT * FROM users WHERE id = 1; -- 使用Read View-1(看不到age=30)
COMMIT;

开始查询
    │
    ▼
生成/复用Read View
    │
    ▼
读取数据行(DB_TRX_ID)
    │
    ▼
判断可见性
    │
    ├─▶ DB_TRX_ID == creator_trx_id? ──▶ YES ──▶ 可见
    │            │
    │            NO
    │            ▼
    ├─▶ DB_TRX_ID < min_trx_id? ──────▶ YES ──▶ 可见
    │            │
    │            NO
    │            ▼
    ├─▶ DB_TRX_ID >= max_trx_id? ─────▶ YES ──▶ 可见
    │            │
    │            NO
    │            ▼
    ├─▶ DB_TRX_ID in m_ids? ──────────▶ YES ──▶ 不可见
    │            │
    │            NO
    │            ▼
    └─▶ 可见(已提交) ──────────────────▶ 返回该版本
    
    如果不可见,通过DB_ROLL_PTR找上一版本,重复判断

┌─────────────────────────────────────────────┐
│          Redo Log 工作流程                  │
├─────────────────────────────────────────────┤
│                                             │
│  1. 事务提交                                │
│     │                                       │
│     ▼                                       │
│  2. 写入Redo Log Buffer(内存)              │
│     │                                       │
│     ▼                                       │
│  3. 刷写到Redo Log File(磁盘)              │
│     │                                       │
│     ▼                                       │
│  4. 返回事务提交成功                        │
│     │                                       │
│     ▼                                       │
│  5. 后台异步刷写脏页到数据文件              │
│                                             │
└─────────────────────────────────────────────┘

-- 生产环境,数据安全优先
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

-- 性能优先,可容忍少量数据丢失
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

BEGIN; -- 事务ID=1000
INSERT INTO users (id, name, age) VALUES (1, '张三', 25);
COMMIT;

-- 初始数据:事务ID=1000, name='张三', age=25
BEGIN; -- 事务ID=1001
UPDATE users SET age = 26 WHERE id = 1;
COMMIT;

-- 数据状态:
-- 最新版本:事务ID=1001, age=26
-- Undo Log:事务ID=1000, age=25

-- 场景1:事务ID=1002查询(RC级别)
BEGIN;
SELECT * FROM users WHERE id = 1; -- 生成Read View
-- Read View: m_ids=[1002], min=1002, max=1003
-- DB_TRX_ID=1001 < min=1002,可见
-- 返回:age=26
COMMIT;

-- 场景2:事务ID=1000查询(RR级别,Read View在1001提交前生成)
BEGIN; -- 事务ID=1000
SELECT * FROM users WHERE id = 1; -- 生成Read View
-- Read View: m_ids=[1000], min=1000, max=1001
-- DB_TRX_ID=1001 >= max=1001,但在Read View生成后
-- 判断不可见,通过DB_ROLL_PTR找Undo Log
-- Undo Log: DB_TRX_ID=1000 == creator_trx_id,可见
-- 返回:age=25
COMMIT;

BEGIN; -- 事务ID=1002
DELETE FROM users WHERE id = 1;
COMMIT;

-- 普通SELECT都是一致性读
SELECT * FROM orders WHERE id = 1001;
SELECT * FROM orders WHERE status = 'pending';

-- 共享锁(S锁)
SELECT * FROM orders WHERE id = 1001 LOCK IN SHARE MODE;
SELECT * FROM orders WHERE id = 1001 FOR SHARE; -- MySQL 8.0+

-- 排他锁(X锁)
SELECT * FROM orders WHERE id = 1001 FOR UPDATE;

-- UPDATE和DELETE也是当前读
UPDATE orders SET status = 'completed' WHERE id = 1001;
DELETE FROM orders WHERE id = 1001;

-- 事务A
SELECT * FROM orders WHERE id = 1001; -- 一致性读,快速返回

-- 事务B
SELECT * FROM orders WHERE id = 1001 FOR UPDATE; -- 当前读,等待锁

-- 事务A
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 一致性读,读到1000
-- 业务处理...
UPDATE accounts SET balance = 900 WHERE id = 1; -- 当前读,更新
COMMIT;

-- 事务B(同时进行)
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 读到1000
UPDATE accounts SET balance = 800 WHERE id = 1; -- 覆盖事务A的修改
COMMIT;

-- 正确做法
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- 当前读+锁
-- 业务处理...
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- 事务A (RC级别)
BEGIN; -- 事务ID=1000
SELECT * FROM users WHERE id = 1; 
-- Read View-1: m_ids=[1000], min=1000, max=1001
-- 假设读到: age=25

-- 事务B
BEGIN;
UPDATE users SET age = 30 WHERE id = 1; -- 事务ID=1001
COMMIT;

-- 事务A再次查询
SELECT * FROM users WHERE id = 1;
-- Read View-2: m_ids=[1000], min=1000, max=1002
-- DB_TRX_ID=1001 不在m_ids,且已提交,可见
-- 读到: age=30(不可重复读)
COMMIT;

-- 事务A (RR级别)
BEGIN; -- 事务ID=1000
SELECT * FROM users WHERE id = 1;
-- Read View: m_ids=[1000], min=1000, max=1001
-- 假设读到: age=25

-- 事务B
BEGIN;
UPDATE users SET age = 30 WHERE id = 1; -- 事务ID=1001
COMMIT;

-- 事务A再次查询
SELECT * FROM users WHERE id = 1;
-- 使用Read View: m_ids=[1000], min=1000, max=1001
-- DB_TRX_ID=1001 >= max=1001,不在活跃列表
-- 但通过Undo Log找旧版本: age=25
-- 保证可重复读
COMMIT;

-- 事务A (SERIALIZABLE级别)
BEGIN;
SELECT * FROM users WHERE id = 1;
-- 实际执行: SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE
-- 加共享锁,阻塞其他事务的UPDATE

-- 事务B
BEGIN;
UPDATE users SET age = 30 WHERE id = 1; -- 等待事务A释放锁
COMMIT;

-- 初始状态:订单1001, status='pending', version=1

-- 线程A
BEGIN;
SELECT status FROM orders WHERE id = 1001; -- 一致性读,pending
UPDATE orders SET status = 'processing' WHERE id = 1001; -- 当前读+更新
COMMIT;

-- 线程B(并发)
BEGIN;
SELECT status FROM orders WHERE id = 1001; -- 一致性读,pending
UPDATE orders SET status = 'cancelled' WHERE id = 1001; -- 覆盖线程A的修改
COMMIT;

-- 方案1:使用当前读+排他锁
BEGIN;
SELECT status FROM orders WHERE id = 1001 FOR UPDATE; -- 当前读+锁
UPDATE orders SET status = 'processing' WHERE id = 1001;
COMMIT;

-- 方案2:乐观锁(版本号)
BEGIN;
SELECT status, version FROM orders WHERE id = 1001;
UPDATE orders SET status = 'processing', version = version + 1 
WHERE id = 1001 AND version = 1; -- 乐观锁检查
-- affected_rows = 0 则表示版本冲突,重试
COMMIT;

-- 方案3:状态机检查
UPDATE orders SET status = 'processing' 
WHERE id = 1001 AND status = 'pending'; -- 利用WHERE条件检查
-- affected_rows = 0 则表示状态已变更,不符合预期

-- 商品1001, stock=10

-- 用户A
BEGIN;
SELECT stock FROM products WHERE id = 1001; -- 一致性读,stock=10
UPDATE products SET stock = stock - 1 WHERE id = 1001; -- 当前读
COMMIT;

-- 用户B(并发)
BEGIN;
SELECT stock FROM products WHERE id = 1001; -- 一致性读,stock=10
UPDATE products SET stock = stock - 1 WHERE id = 1001;
COMMIT;

-- 实际卖出2件,但库存扣减前都看到stock=10,可能超卖

-- 方案1:使用SELECT FOR UPDATE
BEGIN;
SELECT stock FROM products WHERE id = 1001 FOR UPDATE; -- 当前读+锁
-- stock=10,判断>0
UPDATE products SET stock = stock - 1 WHERE id = 1001;
COMMIT;

-- 方案2:乐观锁+版本号
BEGIN;
SELECT stock, version FROM products WHERE id = 1001; -- 一致性读
-- stock=10, version=5
UPDATE products SET stock = stock - 1, version = 6 
WHERE id = 1001 AND version = 5;
-- affected_rows = 1则成功,=0则重试
COMMIT;

-- 方案3:利用UPDATE原子性
UPDATE products SET stock = stock - 1 
WHERE id = 1001 AND stock > 0;
-- affected_rows = 1则成功,=0则库存不足

-- 事务A(长事务)
BEGIN; -- 事务ID=1000
SELECT * FROM orders WHERE create_time > '2024-01-01';
-- 导出数据,运行2小时...

-- 事务B,C,D...持续更新orders表
-- UPDATE orders SET status = 'completed' WHERE ...
-- 产生大量Undo Log

-- 问题:
-- 1. Undo Log无法清理,空间持续增长
-- 2. 其他查询需要遍历长版本链,性能下降
-- 3. 系统整体变慢

-- 方案1:将长事务拆分
-- 每10000条记录提交一次
BEGIN;
SELECT * FROM orders WHERE create_time > '2024-01-01' LIMIT 10000 OFFSET 0;
-- 处理数据...
COMMIT;

BEGIN;
SELECT * FROM orders WHERE create_time > '2024-01-01' LIMIT 10000 OFFSET 10000;
-- 处理数据...
COMMIT;

-- 方案2:使用只读事务+短事务
SET SESSION TRANSACTION READ ONLY;
BEGIN;
SELECT * FROM orders WHERE create_time > '2024-01-01';
COMMIT;

-- 方案3:使用历史表或数据仓库
-- 将需要导出的数据复制到历史表
CREATE TABLE orders_export AS 
SELECT * FROM orders WHERE create_time > '2024-01-01';
-- 然后从orders_export导出

-- 事务A (RR级别)
BEGIN;
SELECT COUNT(*) FROM orders WHERE user_id = 1; -- 10条

-- 事务B
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (1, 100);
COMMIT;

-- 事务A再次统计
SELECT COUNT(*) FROM orders WHERE user_id = 1; -- 10条(MySQL的RR防止了幻读)
-- 但如果使用当前读:
SELECT COUNT(*) FROM orders WHERE user_id = 1 FOR UPDATE; -- 11条
COMMIT;

-- 如果需要看到最新数据,使用当前读
BEGIN;
SELECT COUNT(*) FROM orders WHERE user_id = 1 FOR UPDATE; -- 当前读+锁
-- 业务处理...
COMMIT;

-- 或者使用RC隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT COUNT(*) FROM orders WHERE user_id = 1; -- 能看到已提交的新记录
COMMIT;

-- 场景:读取余额后扣款
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- 加锁
-- balance=1000
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- 查看锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 查看锁信息
SELECT * FROM performance_schema.data_locks WHERE OBJECT_NAME = 'accounts';

-- 查看活跃事务
SELECT * FROM information_schema.INNODB_TRX;

-- 事务A (RR级别)
BEGIN;
SELECT * FROM users WHERE id = 1; -- 一致性读,age=25

-- 事务B
UPDATE users SET age = 30 WHERE id = 1;
COMMIT;

-- 事务A使用当前读
SELECT * FROM users WHERE id = 1 FOR UPDATE; -- 当前读,age=30
COMMIT;

-- 查看Undo Log信息
SHOW ENGINE INNODB STATUS\G

-- 查看事务信息
SELECT * FROM information_schema.INNODB_TRX;

-- 查看Undo表空间
SELECT * FROM information_schema.INNODB_TABLESPACES 
WHERE NAME LIKE '%undo%';

-- 查看长事务
SELECT trx_id, trx_state, trx_started, 
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration
FROM information_schema.INNODB_TRX
HAVING duration > 60; -- 运行超过60秒的事务

-- 1. 设置事务超时
SET SESSION innodb_lock_wait_timeout = 10;
SET SESSION MAX_EXECUTION_TIME = 60000; -- 60秒超时

-- 2. 应用层控制事务范围
-- 避免在事务中进行耗时操作
BEGIN;
-- 只做数据库操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 不要在事务中调用外部API、发送邮件等

-- 3. 使用只读事务
SET SESSION TRANSACTION READ ONLY;
BEGIN;
SELECT * FROM orders WHERE create_time > '2024-01-01';
COMMIT;

-- 4. 监控和告警
-- 定期查询长事务并告警

-- 不推荐:事务过长
BEGIN;
SELECT * FROM orders WHERE id = 1;
-- 业务处理(耗时操作)
UPDATE orders SET status = 'completed' WHERE id = 1;
COMMIT;

-- 推荐:事务尽可能短
-- 先在事务外处理业务逻辑
-- 仅在事务中做数据库操作
BEGIN;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
UPDATE orders SET status = 'completed' WHERE id = 1;
COMMIT;

-- 无索引:需要扫描全表,遍历所有版本链
UPDATE orders SET status = 'completed' WHERE order_no = 'ORD001';

-- 有索引:快速定位,减少版本链遍历
CREATE INDEX idx_order_no ON orders(order_no);
UPDATE orders SET status = 'completed' WHERE order_no = 'ORD001';

-- 热点数据:所有用户更新同一行
UPDATE config SET value = value + 1 WHERE key = 'counter';

-- 优化:分片计数器
UPDATE counter_shard_1 SET value = value + 1 WHERE id = 1;
UPDATE counter_shard_2 SET value = value + 1 WHERE id = 2;
-- 最后聚合统计

-- MySQL 8.0+
-- 查看Undo表空间
SHOW VARIABLES LIKE 'innodb_undo_tablespaces';

-- 创建独立的Undo表空间
CREATE UNDO TABLESPACE undo_01 ADD DATAFILE 'undo_01.ibu';

-- 设置最大Undo Log大小
SET GLOBAL innodb_max_undo_log_size = 1073741824; -- 1GB

-- 启用Undo Log自动截断
SET GLOBAL innodb_undo_log_truncate = ON;

-- 读多写少:使用RR级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 写多读少:考虑RC级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 不推荐:跨多个表的长事务
BEGIN;
UPDATE orders SET status = 'completed' WHERE id = 1;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 100;
UPDATE payments SET status = 'success' WHERE order_id = 1;
COMMIT;

-- 推荐:拆分为多个短事务
BEGIN;
UPDATE orders SET status = 'completed' WHERE id = 1;
COMMIT;

BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 100;
COMMIT;

-- 使用分布式事务保证一致性(如需要)

-- 方案1:悲观锁
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE id = 1;

-- 方案2:乐观锁
UPDATE products SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND version = 5;

-- 方案3:原子更新
UPDATE products SET stock = stock - 1 
WHERE id = 1 AND stock > 0;