MySQL 8.0中同名实体active字段并发更新冲突问题求助

针对MySQL 8.0下的并发问题，有两种无需新增表的可靠解决思路：

思路一：事务+行级锁（SELECT ... FOR UPDATE） #

把整个"查询激活记录→更新为非激活→插入新激活记录"的流程放进同一个事务，同时用SELECT ... FOR UPDATE锁定查询到的激活行，彻底避免并发漏查的问题。

具体SQL示例 #

START TRANSACTION;

-- 锁定当前name下所有激活状态的记录，阻塞其他事务的同条件查询
SELECT id FROM entity WHERE name = 'name' AND active = TRUE FOR UPDATE;

-- 将锁定的记录设为非激活
UPDATE entity SET active = FALSE WHERE name = 'name' AND active = TRUE;

-- 插入新的激活记录
INSERT INTO entity (name, active) VALUES ('name', TRUE);

COMMIT;

原理说明 #

• 必须使用InnoDB引擎（MySQL 8.0默认引擎），它支持事务和行级锁。
• SELECT ... FOR UPDATE会锁定匹配的行，其他并发事务执行相同的查询时会等待当前事务提交/回滚，确保不会出现"两个事务都查不到激活记录，同时插入激活行"的情况。
• 事务保证了三个操作的原子性，要么全部成功，要么全部回滚。

思路二：利用唯一约束强制业务规则 #

通过创建条件唯一索引，让数据库层面直接保证"同一个name只能有一条active=TRUE的记录"，从根源上杜绝并发问题。

创建条件唯一索引（MySQL 8.0支持） #

-- 当active为TRUE时，name必须唯一；active为FALSE时不触发约束
CREATE UNIQUE INDEX idx_active_name ON entity (name, CASE WHEN active THEN 1 ELSE NULL END);

配套的事务操作 #

START TRANSACTION;

-- 先将同名激活记录设为非激活
UPDATE entity SET active = FALSE WHERE name = 'name' AND active = TRUE;

-- 插入新激活记录，若此时仍有同名激活行（并发场景），数据库会抛出唯一键冲突错误
INSERT INTO entity (name, active) VALUES ('name', TRUE);

COMMIT;

原理说明 #

• MySQL的唯一索引会忽略NULL值，所以当active=FALSE时，CASE语句返回NULL，不会触发唯一约束；当active=TRUE时，CASE返回1，此时name+1的组合必须唯一。
• 若并发场景下另一个事务先插入了激活行，当前事务的INSERT会报错，程序只需捕获错误并重试整个事务即可。

其他数据库的处理方式 #

• PostgreSQL：支持更直观的部分唯一索引，直接指定约束生效的条件：

  CREATE UNIQUE INDEX idx_active_name ON entity(name) WHERE active = TRUE;
  

  同时也支持SELECT ... FOR UPDATE的行级锁方案。
• SQL Server：提供过滤唯一索引实现类似逻辑：

  CREATE UNIQUE INDEX idx_active_name ON entity(name) WHERE active = 1;
  

  行级锁可通过SELECT ... WITH (UPDLOCK)实现。
• Oracle：12c及以上版本支持条件唯一约束，也可通过函数索引实现，行级锁同样支持SELECT ... FOR UPDATE。

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