先查后改-更新丢失问题

场景

需求背景

手动维护一个 Topic 池，用一张表，项目启动时从中挑出一个空闲的使用，使用中续约

实现目标

检查出一个满足要求的记录，然后更新它

为什么要分两步？因为 MySQL 不允许 UPDATE 语句修改子查询中引用的表

即：不能在子查询中读表 A，同时在主查询中更新表 A

原本方案

整个过程作为事务提交

# update_time 默认值为创建时间，排序可以使最久未使用的的先使用
select topic,instance_id from topic_pool where occupancy_time < now() order by update_time LIMIT 1

# 最后一个条件是为了确认这个 Topic 没有被其他人抢占
update topic_pool set instance_id = id and occupancy_time = occupancy_time + interval 300 second where occupancy_time < now()
and insance_id = 查出的 instance_id and topic = 查出的 topic;

一个乐观锁实现，第二次尝试更新的时候检查条件有没有发生变化，如果失败则捕获处理

问题

可以说更新丢失的问题我是预见了的，但是我的思路是捕获问题并告警，评审后意识到更好的思路应该是不要让问题发生

描述

首先这个问题不属于 脏读、幻读、不可重复读 中的任意一个

其次事务并不能保证同一时刻只有一个事务执行

这里属于是我搞混了，redis 只需要保证一组命令的原子性，同一时刻就只会有着一组命令执行
但是 MySQL 在同一时间会有多个事务同时执行，也就是 MySQL 是多线程并发处理请求的

# 实例 1 和 2 同时执行这段 SQL
1. 实例 1 读取拿到 topic1
2. 实例 2 读取拿到 topic1
3. 实例 1 更新了 topic1 记录
4. 实例 2 尝试更新 topic1 记录发现 topic1 记录发生了变更，于是更新失败

解决

单独的事务隔离级别 Serializable 不能解决问题

隔离级别 Serializable 本质上也是“读时加锁”的机制

1. 假设两个事务都查出 occupancy_time < now() 的同一行

2. 由于这行符合条件，两个事务都对查出来那一行加了读锁

3. 接下来两个事务都要做 UPDATE —— 会尝试升级为写锁

在无 FOR UPDATE 的情况下，InnoDB 并不能阻止读到一样的行，也不能阻止根据查询条件尝试 UPDATE 只有一个能成功的结果

代码层面加分布式锁

简单粗暴

行锁

悲观锁实现方式，SQL 修改为

# update_time 默认值为创建时间，排序可以使最久未使用的的先使用
select topic from topic_pool where occupancy_time < now() order by update_time LIMIT 1 for update;

# 最后一个条件是为了确认这个 Topic 没有被其他人抢占
update topic_pool set instance_id = id
and occupancy_time = occupancy_time + interval 300 second 
where topic = 查出的 topic;

第一句给查询出的目标行加行锁，其他事务若尝试执行会被阻塞到当前事务提交

但是存在两个严重的问题

1. select … for update 语句中 where 条件字段若是没有加索引，行锁会退化为表锁

• 也就是 occupancy_time，但是这里的时间会被频繁更新并不适合加索引

InnoDB 在此处尝试加行锁的前提是：能通过索引定位具体行

• 而且就算加上了索引，如果查询是范围的，InnoDB 在执行limit 1 for update时，仍会对可能返回记录的整个索引区间加锁（即 Next-Key Lock），和其他事务的锁范围可能重叠，从而引发阻塞甚至死锁
  limit 1并不会限制加锁范围
  范围加锁行为覆盖了整个事务过程，而不是中间的临时动作

-- 查出候选 id 后再单独按主键加 FOR UPDATE
SELECT id FROM topic_pool WHERE occupancy_time < now() ORDER BY update_time LIMIT 1;

-- 用主键精确加锁
SELECT * FROM topic_pool WHERE id = ? FOR UPDATE;

2. 行锁机制只对单数据库实例生效，如果存在多个数据库实例则会有分布式事务问题，而导致失效

最终结论

1. 并发量不大的情况下，不如直接给这段逻辑加分布式锁

2. 那么并发量大的情况下又该如何解决这个问题呢？

待定……