InnoDB事务交织、原子性及日志索引事务一致性问题咨询

咱们先把你描述的场景理清楚，方便后续讨论：
这是一个周期性将日志分组到LogIndex的假想日志系统：

• 同一时间只有一个Active状态的LogIndex可以接收新日志
• 有个定时任务，会把最近1小时没有新日志的LogIndex从Active转为Closed状态

对应的MySQL InnoDB表结构如下：

1. LogIndex表：Id, State（枚举值：Active|Closed）
2. Log表：Id, LogIndexId, LogLine, LogTimestamp

现在存在两个并行执行的事务场景：

事务1（插入新日志） #

-- 查询当前Active状态的LogIndex，加共享锁
SELECT Id FROM LogIndex WHERE State='Active' LOCK IN SHARE MODE;
-- 插入日志到对应LogIndex
INSERT INTO Log VALUES(Id, <LogIndex.Id>, LogLine, LogTimestamp);

事务2（定时检查并关闭超时的LogIndex） #

-- 查询当前Active状态的LogIndex，加共享锁
SELECT Id FROM LogIndex WHERE State='Active' LOCK IN SHARE MODE;
-- 查询该LogIndex下最新的日志时间
SELECT LogTimestamp FROM Log WHERE LogIndexId='<someLogId>' ORDER BY LogTimestamp DESC LIMIT 1;
-- 如果最新日志超过1小时，则执行更新
UPDATE LogIndex SET State='Closed' WHERE Id='<someLogId>';

接下来针对你的两个问题逐一解答：

Q1：使用LOCK IN SHARE MODE的查询，事务内的语句还会被交织执行吗？ #

肯定会的。LOCK IN SHARE MODE只是给查询到的LogIndex行加上共享锁（S锁），它的作用仅仅是阻止其他事务对这些行加排他锁（X锁）做修改，但完全不会阻止其他事务读取这些行（甚至加共享锁），也不会改变InnoDB默认的事务执行逻辑——不同事务里的语句该交错执行还是会交错，因为共享锁之间是兼容的，两个事务都能拿到同一个LogIndex行的S锁，各自的后续语句自然可以穿插着跑。

Q2：不想用SELECT ... LOCK FOR UPDATE这类排他锁的前提下，怎么序列化事务，避免那两种不一致场景？ #

先明确下你提到的两个问题场景：

场景一：日志插入到已被关闭的LogIndex #

1. t1：事务1拿到Active状态LogIndex的S锁
2. t2：事务2也拿到同一个LogIndex的S锁
3. t3：事务2查到该LogIndex的最新日志已超时
4. t4：事务2把该LogIndex改为Closed
5. t5：事务1把日志插入到已经被关闭的LogIndex里

场景二：超时关闭了刚有新日志的LogIndex #

1. t1：事务1拿到Active状态LogIndex的S锁
2. t2：事务2也拿到同一个LogIndex的S锁
3. t3：事务2查到最新日志已超时
4. t4：事务1插入了一条新日志到该LogIndex
5. t5：事务2把LogIndex改为Closed，但此时刚有新日志插入

因为当前用的是REPEATABLE READ隔离级别，事务2在Query2里看到的是事务启动时的快照（一致性读），所以即使事务4插入了新日志，事务2也看不到，这才导致了场景二的问题。而场景一的问题是因为事务1插入日志不需要对LogIndex加锁，所以即使LogIndex已经被关闭，插入操作依然能成功。

不用排他锁的话，可以从这几个方向解决：

1. 更新时增加实时校验条件，避免错误关闭

事务2不要直接执行UPDATE，而是把“最新日志是否超时”作为更新的条件之一，利用InnoDB的当前读特性，在更新时重新检查最新日志时间：

-- 把Query2查到的最新时间换成实时查询
UPDATE LogIndex 
SET State='Closed' 
WHERE Id='<someLogId>' 
  AND State='Active'
  AND (SELECT MAX(LogTimestamp) FROM Log WHERE LogIndexId='<someLogId>') < NOW() - INTERVAL 1 HOUR;

这样即使事务2之前读到的是旧快照，更新时会实时读取最新的日志时间，如果此时事务1已经插入了新日志并提交，这个UPDATE会返回0行受影响，事务2就不会错误地关闭LogIndex了。

2. 给Log表建联合索引，优化校验性能

上面的子查询如果没有索引会很慢，建议给Log表建一个联合索引：

CREATE INDEX idx_log_index_time ON Log(LogIndexId, LogTimestamp DESC);

这样MAX(LogTimestamp)的查询会直接走索引，性能不会有问题。

3. 插入日志时校验LogIndex状态，避免插入到已关闭的索引

事务1不要直接执行INSERT，而是把插入和状态校验合并成一个语句：

INSERT INTO Log(Id, LogIndexId, LogLine, LogTimestamp)
SELECT <new_log_id>, <log_index_id>, <log_line>, <log_timestamp>
FROM LogIndex
WHERE Id=<log_index_id> AND State='Active';

如果插入前LogIndex已经被关闭，这个INSERT语句会返回0行插入，事务1就可以知道需要重新获取新的Active LogIndex（比如创建一个新的）再执行插入。

4. 利用单语句原子性，避免中间状态

InnoDB的单个SQL语句是原子性的，不管是插入还是更新，把校验和操作合并成一个语句，就能避免中间状态的问题，不需要依赖锁来序列化事务。

另外补充一点：如果用LOCK IN SHARE MODE，事务2执行UPDATE时需要把S锁升级为X锁，这时候会等待所有其他持有S锁的事务（比如事务1）提交或回滚，容易出现锁等待超时。而用上面条件更新的方式，不需要等待，直接做实时校验，能快速判断是否应该更新，体验会更好。

备注：内容来源于stack exchange，提问作者sonam