深入解析MySQL中默认的事务隔离级别与选择原因

-- 在 repeatable read 级别下：
-- 事务 a
start transaction;
select * from accounts where user_id = 1; -- 第一次查询

-- 事务 b（同时执行）
start transaction;
update accounts set balance = balance + 100 where user_id = 1;
commit;

-- 事务 a 再次查询
select * from accounts where user_id = 1; -- 结果与第一次相同（不可重复读被解决）
commit;

-- 主库执行（基于语句的复制）
start transaction;
insert into orders values (1, 'pending');
-- 此时其他事务修改了数据
update orders set status = 'shipped' where id = 1;
commit;

-- 从库重放 binlog 时，需要保证 update 语句看到的数据与主库一致
-- repeatable read 的快照机制保证了这一点

-- 事务 a
start transaction; -- 创建 read view

-- 此时数据：id=1, balance=1000
select balance from accounts where id = 1; -- 返回 1000

-- 事务 b 修改数据并提交
start transaction;
update accounts set balance = 2000 where id = 1;
commit;

-- 事务 a 再次读取（不可重复读被防止）
select balance from accounts where id = 1; -- 仍然返回 1000
commit;

-- 事务提交后读取最新数据
select balance from accounts where id = 1; -- 返回 2000

-- 使用 for update 或 lock in share mode 进行当前读
start transaction;
select * from accounts where id = 1 for update; -- 获取最新数据并加锁

-- 其他事务的 update 会被阻塞

-- 事务 a
start transaction;
select * from users where age > 20; -- 返回 5 条记录

-- 事务 b 插入新记录
start transaction;
insert into users (name, age) values ('bob', 25); -- 会被阻塞（next-key lock）
commit; -- 等待事务 a 释放锁

-- 事务 a 再次查询
select * from users where age > 20; -- 仍然返回 5 条记录
commit; -- 释放锁，事务 b 的插入才能执行

-- 查看全局隔离级别
select @@global.transaction_isolation;

-- 查看会话隔离级别
select @@session.transaction_isolation;

-- 查看当前连接的隔离级别
select @@transaction_isolation;

-- 输出示例：repeatable-read

-- 修改当前会话的隔离级别
set session transaction isolation level read committed;

-- 修改全局隔离级别（重启后生效）
set global transaction isolation level read committed;

-- 或在 my.cnf 中配置
[mysqld]
transaction-isolation = read-committed

-- 1. 高并发写入场景
set session transaction isolation level read committed;
start transaction;
-- 较少的锁冲突，更高的并发度

-- 2. 使用基于行的复制（row-based replication）
-- 需要配合修改配置
set global binlog_format = 'row';

-- 3. oracle 迁移到 mysql 的应用
-- 保持与 oracle 默认行为一致

-- 1. 金融交易系统（需要高度一致性）
start transaction;
-- 多次读取余额必须一致

-- 2. 报表系统（需要一致的数据视图）
start transaction;
-- 生成报表期间数据不应变化

-- 3. 使用基于语句的复制（默认）
-- 保证主从数据一致性

-- 长事务在 repeatable read 下会导致问题
start transaction;
select * from large_table; -- 创建快照

-- 长时间不提交...
-- 结果：undo 日志堆积，影响性能
-- 建议：设置事务超时
set session max_execution_time = 5000; -- 5秒超时

-- 监控长事务
select 
    trx_id,
    trx_started,
    timediff(now(), trx_started) as duration,
    trx_state
from information_schema.innodb_trx
order by trx_started;

-- 查看锁等待
select * from information_schema.innodb_lock_waits;

// spring boot 中配置隔离级别
@configuration
public class datasourceconfig {
    
    @bean
    public platformtransactionmanager transactionmanager(datasource datasource) {
        datasourcetransactionmanager tm = new datasourcetransactionmanager(datasource);
        tm.setdefaulttimeout(30); // 设置超时
        tm.setdefaultisolationlevel(transactiondefinition.isolation_repeatable_read);
        return tm;
    }
}

// 或在具体方法上指定
@transactional(isolation = isolation.read_committed, timeout = 10)
public void updateaccount(account account) {
    // ...
}