如何避免并行插入冲突？场景A与B的并发解决方案咨询

问题场景 #

• 场景A：可无条件向表A插入行
• 场景B：需先执行SELECT * FROM A WHERE col = 2读取所有匹配行，在应用端完成计算后，决定是否插入新行

当场景B完成读取但尚未插入新行时，若场景A插入了一条col=2的新行，会导致场景B基于旧数据计算出错误结果，最终插入无效行。

你尝试过的思路与遇到的困境：

• 认为Serializable隔离级别无效，因为计算逻辑在应用端而非SQL层面
• col并非外键，无法通过锁定其他行阻止并行插入
• 试过在场景B插入后、提交前重新读取，但场景A仍可能在重读后、提交前插入数据
• 想到的方案是按col对行做枚举，新增唯一索引(col, num)实现乐观锁，但这样场景A也必须先读表再插入
• 更新补充：若场景A插入前先读取表，即便无额外枚举列，Serializable隔离级别也可生效

可行解决方案 #

1. 调整场景A逻辑+Serializable隔离级别 #

按照你更新的思路，强制场景A插入前先执行SELECT * FROM A WHERE col = 2（哪怕不需要用到查询结果），同时将事务隔离级别设为Serializable。

Serializable会通过范围锁锁定col=2的行范围，要么阻止场景B在读取后、场景A插入前的并行操作，要么在检测到冲突时触发事务回滚，从机制上保证数据一致性。这种方案无需修改表结构，仅需调整场景A的逻辑和事务配置。

2. 将应用端计算迁移到SQL层面（推荐） #

如果你的计算逻辑可以用SQL实现，直接用INSERT ... SELECT的原子化操作完成，示例如下：

INSERT INTO A (col, other_cols)
SELECT 2, [根据查询结果计算出的字段值]
FROM (SELECT COUNT(*) AS cnt FROM A WHERE col = 2) AS t
WHERE [基于cnt的计算条件];

整个读取+计算+插入操作在数据库事务内原子执行，Serializable隔离级别可以完全避免并发插入带来的时间窗口问题，从根源上消除不一致风险。

3. 基于虚拟行的悲观锁方案 #

在表A中预先插入一行标记为虚拟行的记录（比如col=2, is_virtual=1），场景B执行查询时锁定这行虚拟行：

SELECT * FROM A WHERE col = 2 FOR UPDATE;

这样场景A插入col=2的行前，必须等待场景B释放锁，从而避免并行插入干扰场景B的计算。这种方案需要新增一个标记列，且需要维护虚拟行的存在。

4. 无需场景A改逻辑的乐观锁增强 #

给表A新增一个按col分组递增的version列，场景B读取时同时获取当前col=2的最大版本号：

SELECT *, MAX(version) OVER (PARTITION BY col) AS current_version FROM A WHERE col = 2;

插入时带上该版本号做校验：

INSERT INTO A (col, other_cols, version)
VALUES (2, [计算后的值], [current_version + 1])
WHERE (SELECT MAX(version) FROM A WHERE col = 2) = [current_version];

如果插入操作返回行数为0，说明有并发插入，场景B需重新读取并计算。这种方案下场景A仍可无条件插入，但需要保证version值按col分组递增（可通过序列或触发器实现）。

内容的提问来源于stack exchange，提问作者dmzkrsk