MySQL 事务隔离级别及实际业务应用场景

-- mysql事务隔离级别（从低到高）
-- 1. read uncommitted（读未提交）
-- 2. read committed（读已提交）
-- 3. repeatable read（可重复读）-- mysql默认级别
-- 4. serializable（串行化）

/**
 * 并发问题分类：
 * 1. 脏读（dirty read）：读取到其他事务未提交的数据
 * 2. 不可重复读（non-repeatable read）：同一事务内两次读取结果不一致
 * 3. 幻读（phantom read）：同一事务内两次查询结果集不一致
 * 4. 丢失更新（lost update）：两个事务更新同一数据，后提交的覆盖了先提交的
 */

-- 设置隔离级别为读未提交
set session transaction isolation level read uncommitted;
-- 示例：脏读问题演示
-- 事务a
start transaction;
update account set balance = balance - 100 where user_id = 1;
-- 此时 balance 已修改但未提交
-- 事务b（在另一个连接）
set session transaction isolation level read uncommitted;
start transaction;
select balance from account where user_id = 1; 
-- 会读取到事务a未提交的修改（脏读）
-- 如果事务a回滚，事务b读取的数据就是错误的

-- 设置隔离级别为读已提交
set session transaction isolation level read committed;
-- 示例：不可重复读问题演示
-- 事务a
start transaction;
select balance from account where user_id = 1;
-- 第一次读取：balance = 1000
-- 事务b
start transaction;
update account set balance = balance - 100 where user_id = 1;
commit; -- 提交修改
-- 事务a再次读取
select balance from account where user_id = 1;
-- 第二次读取：balance = 900（不可重复读）
commit;

-- mysql在read committed下的实现
-- 每行数据有隐藏字段：
-- db_trx_id：最后修改该记录的事务id
-- db_roll_ptr：回滚指针，指向undo log中的旧版本
-- db_row_id：行id（隐藏主键）
-- 事务可见性规则：
-- 1. 版本号小于当前事务id的记录
-- 2. 删除版本号未定义或大于当前事务id
-- 3. 在read committed下，每次查询都重新生成readview

-- mysql默认隔离级别
set session transaction isolation level repeatable read;
-- 示例：解决不可重复读
-- 事务a
start transaction;
select balance from account where user_id = 1;
-- 第一次读取：balance = 1000
-- 事务b
start transaction;
update account set balance = balance - 100 where user_id = 1;
commit;
-- 事务a再次读取
select balance from account where user_id = 1;
-- 第二次读取：balance = 1000（可重复读，读取的是快照）
commit;

-- 事务a：统计账户数量
start transaction;
select count(*) from account where balance > 0;
-- 返回：5
-- 事务b：插入新账户
start transaction;
insert into account(user_id, balance) values (6, 500);
commit;
-- 事务a：再次统计
select count(*) from account where balance > 0;
-- 在repeatable read下，返回仍然是：5（没有幻读）
-- 但如果执行update/insert，可能会看到"幻影行"

-- 事务a
start transaction;
-- 使用select ... for update添加间隙锁
select * from account where id > 100 for update;
-- 这会锁定id>100的所有记录和间隙
-- 事务b试图插入
insert into account(id, user_id) values (101, 6);
-- 会被阻塞，直到事务a提交
-- 查看当前锁信息
show engine innodb status;

-- 串行化隔离级别
set session transaction isolation level serializable;
-- 所有select语句都会隐式添加lock in share mode
-- 事务a
start transaction;
select balance from account where user_id = 1;
-- 自动加共享锁
-- 事务b
start transaction;
update account set balance = balance - 100 where user_id = 1;
-- 会被阻塞，直到事务a提交

-- innodb mvcc数据结构示例
create table account (
    id int primary key,
    user_id int,
    balance decimal(10,2),
    -- 隐藏字段
    -- db_trx_id: 6字节，最后修改事务id
    -- db_roll_ptr: 7字节，回滚指针
    -- db_row_id: 6字节，隐藏主键
    -- delete bit: 删除标记
);
-- readview创建时机：
-- read committed: 每次select都创建新的readview
-- repeatable read: 第一次select时创建readview，整个事务复用
-- 可见性判断算法：
-- 1. 如果db_trx_id < up_limit_id，可见（事务开始前已提交）
-- 2. 如果db_trx_id >= low_limit_id，不可见（事务开始后开始的）
-- 3. 如果db_trx_id在活跃事务列表中，不可见（未提交）
-- 4. 否则可见

-- 假设原始数据
-- id=1, balance=1000, db_trx_id=10
-- 事务20修改
update account set balance = 900 where id = 1;
-- 新版本：balance=900, db_trx_id=20, 回滚指针指向旧版本
-- 事务30修改
update account set balance = 800 where id = 1;
-- 新版本：balance=800, db_trx_id=30, 回滚指针指向事务20的版本
-- 版本链：
-- 当前版本（事务30） ← 事务20版本 ← 事务10版本

-- 问题场景：高并发下库存扣减
-- 错误做法（存在超卖风险）
start transaction;
select stock from product where id = 1;
-- 假设stock = 10
if stock > 0 then
    update product set stock = stock - 1 where id = 1;
end if;
commit;
-- 解决方案1：使用select ... for update（悲观锁）
start transaction;
-- 加行锁，阻止其他事务读取
select stock from product where id = 1 for update;
-- 此时其他事务的select ... for update会被阻塞
if stock > 0 then
    update product set stock = stock - 1 where id = 1;
end if;
commit;
-- 解决方案2：使用乐观锁（版本控制）
alter table product add version int default 0;
start transaction;
select stock, version from product where id = 1;
-- 假设stock=10, version=1
if stock > 0 then
    update product set 
        stock = stock - 1,
        version = version + 1 
    where id = 1 and version = 1;
    -- 如果影响行数为0，说明版本已变，需要重试
end if;
commit;
-- 解决方案3：直接update判断
start transaction;
update product set stock = stock - 1 
where id = 1 and stock > 0;
-- 返回影响行数，如果为0表示库存不足
commit;

-- 场景：a向b转账，需要保证原子性和一致性
-- 问题：并发转账可能导致余额错误
-- 解决方案：使用serializable隔离级别或精心设计的事务
set session transaction isolation level repeatable read;
start transaction;
-- 关键：按固定顺序加锁，避免死锁
-- 总是先锁id小的账户
select * from account 
where id in (1, 2) 
order by id 
for update;
-- 检查a账户余额
select balance from account where id = 1;
-- 执行转账
update account set balance = balance - 100 where id = 1;
update account set balance = balance + 100 where id = 2;
-- 记录流水
insert into transfer_log(from_id, to_id, amount) values (1, 2, 100);
commit;

-- 场景：生成财务报表时，数据被其他事务修改
-- 要求：统计期间数据必须一致
-- 解决方案1：使用repeatable read + 开始时间点
set session transaction isolation level repeatable read;
start transaction;
-- 记录开始时间
set @report_time = now();
-- 统计逻辑（所有查询看到的是同一时间点的快照）
select sum(balance) from account;
select count(*) from transfer_log where create_time < @report_time;
commit;
-- 解决方案2：使用备份或从库查询
-- 在从库上使用repeatable read，不影响主库性能
start transaction;
set session transaction isolation level repeatable read;
-- ... 统计查询
commit;

-- 场景：消息重复消费，需要保证幂等性
-- 问题：重复处理同一消息
create table message_consumed (
    id bigint primary key auto_increment,
    message_id varchar(64) unique,  -- 消息唯一id
    status tinyint default 0,
    consume_time datetime
);
-- 消费消息时的幂等处理
start transaction;
-- 方案1：先插入，利用唯一索引保证幂等
insert ignore into message_consumed(message_id, consume_time) 
values ('msg_123', now());
-- 如果插入成功（影响行数>0），则处理消息
if row_count() > 0 then
    -- 执行业务逻辑
    call process_business_logic('msg_123');
    update message_consumed 
    set status = 1 
    where message_id = 'msg_123';
end if;
commit;

-- 事务a
start transaction;
update account set balance = balance - 100 where id = 1;
-- 持有id=1的锁
-- 事务b（同时执行）
start transaction;
update account set balance = balance - 200 where id = 2;
-- 持有id=2的锁
-- 事务a继续
update account set balance = balance + 100 where id = 2;
-- 等待事务b释放id=2的锁
-- 事务b继续
update account set balance = balance + 200 where id = 1;
-- 等待事务a释放id=1的锁
-- 死锁发生！

-- 查看死锁日志
show engine innodb status;
-- 死锁日志示例：
-- latest detected deadlock
-- *** (1) transaction:
-- transaction 3100, active 2 sec starting index read
-- mysql tables in use 1, locked 1
-- lock wait 2 lock struct(s), heap size 1136, 1 row lock(s)
-- mysql thread id 10, os thread handle 1234, query id 100 updating
-- update account set balance = balance + 100 where id = 2
-- 预防死锁策略：
-- 1. 按相同顺序访问资源
-- 2. 减少事务执行时间
-- 3. 使用低隔离级别（read committed）
-- 4. 添加合理的索引，减少锁范围
-- 代码层面的解决方案
start transaction;
-- 总是按id顺序加锁
select * from account 
where id in (1, 2) 
order by id 
for update;
-- 执行更新操作
update account set balance = balance - 100 where id = 1;
update account set balance = balance + 100 where id = 2;
commit;

-- 间隙锁导致的死锁场景
-- 表结构
create table orders (
    id int primary key,
    user_id int,
    amount decimal(10,2),
    key idx_user_id (user_id)
);
-- 事务a
start transaction;
-- 对user_id=100加间隙锁（锁定100-200之间的间隙）
select * from orders where user_id = 150 for update;
-- 事务b
start transaction;
-- 对user_id=200加间隙锁（锁定100-200之间的间隙）
select * from orders where user_id = 180 for update;
-- 事务a尝试插入
insert into orders(id, user_id) values (1, 160);
-- 等待事务b的间隙锁
-- 事务b尝试插入
insert into orders(id, user_id) values (2, 170);
-- 等待事务a的间隙锁
-- 死锁！

-- 选择合适隔离级别的决策流程
/**
 * 决策树：
 * 1. 需要避免脏读？是 → 至少read committed
 * 2. 需要避免不可重复读？是 → 至少repeatable read
 * 3. 需要避免幻读？是 → serializable
 * 4. 并发性能要求高？是 → 考虑降低隔离级别
 * 5. 有明确的锁策略？是 → 可以使用较低隔离级别+手动加锁
 */
-- 各隔离级别适用场景总结：
-- read uncommitted: 统计类只读查询，允许脏数据
-- read committed: 大多数oltp系统，需要实时数据
-- repeatable read: 财务系统，对账系统，需要稳定视图
-- serializable: 银行核心交易，需要绝对一致性

-- 实践1：保持事务短小
-- 错误示例：长事务
start transaction;
-- 复杂业务逻辑
-- 网络调用
-- 文件操作
-- 大量计算
commit; -- 事务持续时间太长
-- 正确示例：拆分事务
-- 事务1：数据准备
start transaction;
insert into temp_data select ...;
commit;
-- 事务2：业务处理
start transaction;
update ...;
commit;
-- 实践2：避免在事务中执行select ... for update时扫描大量数据
-- 错误示例
start transaction;
select * from orders where create_time > '2023-01-01' for update;
-- 可能锁定大量行，导致性能问题
-- 正确示例：分批处理
declare done int default false;
declare batch_size int default 100;
declare offset int default 0;
while not done do
    start transaction;
    select * from orders 
    where create_time > '2023-01-01' 
    limit batch_size offset offset 
    for update;
    -- 处理逻辑
    commit;
    set offset = offset + batch_size;
    -- 检查是否完成
end while;

-- 监控当前事务
select * from information_schema.innodb_trx;
-- 监控锁信息
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;
-- 监控长事务
select 
    trx_id,
    trx_started,
    timestampdiff(second, trx_started, now()) as duration_seconds,
    trx_state,
    trx_query
from information_schema.innodb_trx
where timestampdiff(second, trx_started, now()) > 60  -- 超过60秒的事务
order by trx_started;
-- 设置长事务超时
set session innodb_lock_wait_timeout = 50;  -- 锁等待超时50秒
set session innodb_rollback_on_timeout = on; -- 超时自动回滚

-- 秒杀场景下的库存扣减优化
-- 表设计
create table seckill_stock (
    product_id bigint primary key,
    stock int not null,
    version int default 0,
    sale_date date
);
-- 方案1：使用乐观锁+重试机制
delimiter //
create procedure seckill_purchase(
    in p_product_id bigint,
    in p_user_id bigint,
    out p_result int
)
begin
    declare v_stock int;
    declare v_version int;
    declare retry_count int default 0;
    declare max_retry int default 3;
    purchase_retry: repeat
        start transaction;
        -- 查询当前库存和版本
        select stock, version into v_stock, v_version
        from seckill_stock 
        where product_id = p_product_id 
        for update;  -- 悲观锁，防止其他事务同时修改
        if v_stock <= 0 then
            rollback;
            set p_result = 0; -- 库存不足
            leave purchase_retry;
        end if;
        -- 更新库存
        update seckill_stock 
        set stock = stock - 1,
            version = version + 1
        where product_id = p_product_id 
          and version = v_version;
        -- 检查是否更新成功
        if row_count() = 1 then
            -- 创建订单
            insert into seckill_order(product_id, user_id, create_time)
            values (p_product_id, p_user_id, now());
            commit;
            set p_result = 1; -- 成功
            leave purchase_retry;
        else
            rollback;
            set retry_count = retry_count + 1;
            -- 等待随机时间后重试
            do sleep(rand() * 0.1);
        end if;
    until retry_count >= max_retry end repeat;
    if retry_count >= max_retry then
        set p_result = -1; -- 重试失败
    end if;
end//
delimiter ;

-- 对账系统需要数据一致性
set session transaction isolation level repeatable read;
start transaction;
-- 记录对账开始时间
set @reconcile_time = now();
-- 创建对账快照表
create temporary table reconcile_snapshot as
select 
    a.account_no,
    a.balance as db_balance,
    b.balance as external_balance
from account a
left join external_system b on a.account_no = b.account_no
where a.update_time <= @reconcile_time;
-- 执行对账逻辑
select 
    account_no,
    db_balance,
    external_balance,
    case 
        when abs(db_balance - external_balance) > 0.01 then 'mismatch'
        else 'match'
    end as status
from reconcile_snapshot;
-- 记录对账结果
insert into reconcile_log(reconcile_time, total_count, mismatch_count)
select 
    @reconcile_time,
    count(*),
    sum(case when abs(db_balance - external_balance) > 0.01 then 1 else 0 end)
from reconcile_snapshot;
commit;

-- 点赞功能，需要避免重复点赞
create table likes (
    id bigint primary key auto_increment,
    user_id bigint not null,
    post_id bigint not null,
    create_time datetime default current_timestamp,
    unique key uk_user_post (user_id, post_id)  -- 唯一约束防止重复
);
-- 点赞操作
start transaction;
-- 尝试插入，利用唯一约束保证幂等性
insert ignore into likes(user_id, post_id) 
values (123, 456);
-- 如果插入成功，更新帖子点赞数
if row_count() > 0 then
    update posts 
    set like_count = like_count + 1 
    where id = 456;
end if;
commit;
-- 取消点赞
start transaction;
delete from likes 
where user_id = 123 and post_id = 456;
-- 如果删除成功，更新帖子点赞数
if row_count() > 0 then
    update posts 
    set like_count = greatest(0, like_count - 1) 
    where id = 456;
end if;
commit;

-- mysql默认autocommit=1，每条语句都是一个事务
-- 可能导致意想不到的问题
-- 关闭自动提交
set autocommit = 0;
-- 显式控制事务
start transaction;
-- 业务逻辑
commit; -- 或 rollback;
-- 恢复自动提交
set autocommit = 1;

-- 以下语句会隐式提交当前事务：
-- 1. ddl语句（create, alter, drop等）
-- 2. 用户权限管理语句
-- 3. 锁表语句（lock tables, unlock tables）
-- 错误示例
start transaction;
update account set balance = balance - 100 where id = 1;
-- 这个语句会提交事务！
create index idx_balance on account(balance);
update account set balance = balance + 100 where id = 2;
-- 如果这里出错，第一个update已经提交，无法回滚！
commit;

-- 大事务可能导致的问题：
-- 1. 锁持有时间长，阻塞其他事务
-- 2. 产生大量undo log，占用磁盘
-- 3. 主从复制延迟
-- 4. 回滚时间长
-- 解决方案：拆分大事务
-- 原始大事务
start transaction;
-- 处理10万条数据
update large_table set status = 1 where condition;
commit; -- 可能执行几分钟
-- 拆分为小批次
set autocommit = 0;
set @rows_affected = 1;
while @rows_affected > 0 do
    start transaction;
    update large_table 
    set status = 1 
    where condition 
    and status = 0 
    limit 1000;
    set @rows_affected = row_count();
    commit;
    -- 短暂暂停，减少对系统影响
    do sleep(0.1);
end while;
set autocommit = 1;