MySQL事务隔离级别RR的原理与工作过程解析

-- 查看InnoDB行格式的内部结构
SHOW TABLE STATUS LIKE 'your_table_name'\G
 
-- 通过information_schema查看事务信息
SELECT * FROM information_schema.innodb_trx\G

// 简化版ReadView结构（源码层面）
struct ReadView {
    trx_id_t    low_limit_id;    // 高水位：大于等于此ID的事务不可见
    trx_id_t    up_limit_id;     // 低水位：小于此ID的事务可见
    trx_id_t    creator_trx_id;  // 创建该ReadView的事务ID
    ids_t       trx_ids;         // 活跃事务ID列表
};

对于一个数据行版本：
1. 如果 trx_id < up_limit_id → 可见（事务已提交）
2. 如果 trx_id >= low_limit_id → 不可见（事务在未来启动）
3. 如果 trx_id 在 trx_ids 列表中 → 不可见（事务仍在运行）
4. 如果 trx_id == creator_trx_id → 可见（自己修改的数据）

-- 会话A：事务1
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 读取到1000
 
-- 会话B：事务2
START TRANSACTION;
UPDATE accounts SET balance = 800 WHERE id = 1;
COMMIT;
 
-- 会话A：事务1继续
SELECT balance FROM accounts WHERE id = 1;  -- 仍然读取到1000（可重复读）
COMMIT;

-- 创建测试表
CREATE TABLE test_gap (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    INDEX idx_age (age)
);
 
-- 插入测试数据
INSERT INTO test_gap VALUES (1, 'Alice', 20), (3, 'Bob', 25), (5, 'Charlie', 30);
 
-- 会话A：加间隙锁
START TRANSACTION;
SELECT * FROM test_gap WHERE age = 25 FOR UPDATE;  -- 锁定age=25的记录及周围间隙
 
-- 会话B：尝试插入
INSERT INTO test_gap VALUES (4, 'David', 24);  -- 被阻塞（24在20-25间隙中）
INSERT INTO test_gap VALUES (6, 'Eve', 26);    -- 被阻塞（26在25-30间隙中）

Next-Key Lock = Record Lock + Gap Lock

-- 不良索引设计导致的性能问题
-- 假设表中没有合适的索引
SELECT * FROM large_table WHERE status = 'pending' FOR UPDATE;
-- 结果：全表扫描，所有记录都被加Next-Key锁
 
-- 优化后的索引设计
CREATE INDEX idx_status ON large_table(status, created_at);
SELECT * FROM large_table WHERE status = 'pending' FOR UPDATE;
-- 结果：只锁定符合条件的记录及相关间隙

-- 会话A
START TRANSACTION;
SELECT * FROM test_gap WHERE id = 3 FOR UPDATE;  -- 持有id=3的记录锁
 
-- 会话B  
START TRANSACTION;
SELECT * FROM test_gap WHERE id = 5 FOR UPDATE;  -- 持有id=5的记录锁
 
-- 会话A继续
SELECT * FROM test_gap WHERE id = 5 FOR UPDATE;  -- 等待会话B释放id=5的锁
 
-- 会话B继续
SELECT * FROM test_gap WHERE id = 3 FOR UPDATE;  -- 等待会话A释放id=3的锁
-- 结果：死锁发生，InnoDB选择回滚其中一个事务

-- 测试表结构
CREATE TABLE consistency_test (
    id INT PRIMARY KEY,
    value INT,
    version INT
);
 
INSERT INTO consistency_test VALUES (1, 100, 1);

时间轴：
T1: 会话A START TRANSACTION
T2: 会话B START TRANSACTION  
T3: 会话A SELECT value FROM consistency_test WHERE id = 1;  -- RR:100, RC:100
T4: 会话B UPDATE consistency_test SET value = 200, version = 2 WHERE id = 1;
T5: 会话B COMMIT;
T6: 会话A SELECT value FROM consistency_test WHERE id = 1;  -- RR:100, RC:200
T7: 会话A UPDATE consistency_test SET value = value + 50 WHERE id = 1;
T8: 会话A COMMIT;

// 简化版源码分析（storage/innobase/read/read0read.cc）
void ReadView::prepare(trx_id_t id) {
    ut_ad(mutex_own(&view_mutex));
    
    // 设置创建者事务ID
    m_creator_trx_id = id;
    
    // 获取当前最大事务ID+1作为高水位
    m_low_limit_no = trx_sys_get_max_trx_id();
    
    // 获取当前最小活跃事务ID作为低水位
    m_up_limit_no = trx_sys_get_min_trx_id();
    
    // 复制当前活跃事务列表
    trx_sys_get_active_trx_ids(&m_ids);
}

// 简化版可见性判断函数
bool ReadView::changes_visible(trx_id_t trx_id, const table_name_t& name) {
    // 如果是自己的事务，总是可见
    if (trx_id == m_creator_trx_id) {
        return true;
    }
    
    // 如果小于低水位，说明事务已提交，可见
    if (trx_id < m_up_limit_no) {
        return true;
    }
    
    // 如果大于等于高水位，说明事务在ReadView之后启动，不可见
    if (trx_id >= m_low_limit_no) {
        return false;
    }
    
    // 检查是否在活跃事务列表中
    return !m_ids.contains(trx_id);
}

-- 查看当前活跃事务
SELECT 
    trx_id,
    trx_state,
    trx_started,
    trx_requested_lock_id,
    trx_wait_started,
    trx_weight
FROM information_schema.innodb_trx\G
 
-- 查看锁等待情况
SELECT 
    requesting_trx_id,
    requested_lock_id,
    blocking_trx_id,
    blocking_lock_id,
    wait_age_secs
FROM information_schema.innodb_lock_waits\G
 
-- 查看详细的锁信息
SELECT 
    lock_id,
    lock_trx_id,
    lock_mode,
    lock_type,
    lock_table,
    lock_index,
    lock_space,
    lock_page,
    lock_rec
FROM information_schema.innodb_locks\G

-- 启用事务监控
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%transaction%';
 
-- 查看事务统计信息
SELECT 
    trx_id,
    trx_started,
    trx_isolation_level,
    trx_read_only,
    trx_autocommit,
    trx_lock_memory_bytes,
    trx_rows_locked,
    trx_rows_modified
FROM performance_schema.events_transactions_current\G

-- 查看最近的死锁信息
SHOW ENGINE INNODB STATUS\G
 
-- 重点关注LATEST DETECTED DEADLOCK部分
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-01-15 10:30:45 0x7f8b8c008700
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 100, OS thread handle 123145678901234, query id 500 localhost root updating
UPDATE accounts SET balance = balance - 100 WHERE id = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 3 n bits 72 index PRIMARY of table `test`.`accounts` trx id 12345 lock_mode X waiting

-- 原则1：为WHERE条件创建复合索引
CREATE INDEX idx_user_status_date ON users(status, created_at, user_id);
 
-- 原则2：避免在索引列上使用函数
-- 不推荐
SELECT * FROM orders WHERE DATE(create_time) = '2024-01-15' FOR UPDATE;
 
-- 推荐
SELECT * FROM orders WHERE create_time >= '2024-01-15' AND create_time < '2024-01-16' FOR UPDATE;

-- 模式1：短事务原则
START TRANSACTION;
-- 业务逻辑
UPDATE inventory SET stock = stock - 1 WHERE product_id = 1001 AND stock > 0;
-- 立即提交，减少锁持有时间
COMMIT;
 
-- 模式2：分批处理
-- 不推荐：一次性处理大量数据
UPDATE large_table SET status = 'processed' WHERE status = 'pending';
 
-- 推荐：分批处理，减少锁竞争
REPEAT
    UPDATE large_table SET status = 'processed' 
    WHERE status = 'pending' 
    LIMIT 1000;
    COMMIT;
    DO SLEEP(0.1);  -- 给其他事务机会
UNTIL ROW_COUNT() = 0 END REPEAT;

-- 创建事务超时监控
CREATE EVENT monitor_long_transactions
ON SCHEDULE EVERY 1 MINUTE
DO
    SELECT COUNT(*) INTO @long_trx_count
    FROM information_schema.innodb_trx 
    WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 300;
    
    IF @long_trx_count > 0 THEN
        -- 发送告警通知
        INSERT INTO alert_log(message, alert_time) 
        VALUES (CONCAT('发现', @long_trx_count, '个长事务'), NOW());
    END IF;