是否需要锁表以保证同一用户仅存在一个active状态的任务？

现有方案评估 #

你拟定的全表锁方案逻辑上可以实现需求，但实际业务中完全不推荐使用，存在两个严重问题：

• 性能极差：全表锁会阻塞所有用户的任务插入、更新操作，哪怕两个操作针对完全无关的用户也会互相等待，并发量稍高就会出现大面积请求超时，业务可用性极低。
• 稳定性差：手动加解锁的逻辑如果遇到异常（比如事务回滚失败、服务进程意外退出），很容易出现表锁未正常释放的问题，直接导致整个任务相关功能完全瘫痪。

推荐实现方案 #

根据你使用的数据库类型，可以选择以下两种更优的无全表锁方案：

方案1：数据库唯一约束（最推荐，实现成本最低） #

靠数据库本身的约束保证数据正确性，无需写复杂的业务锁逻辑，天然抗并发：

• 若使用PostgreSQL等支持部分唯一索引的数据库，无需修改表结构，直接建索引即可：

  CREATE UNIQUE INDEX idx_unique_active_task ON tasks (user_id) WHERE status = 'active';
  

  该索引仅对status = 'active'的任务做用户ID唯一性校验，非活跃任务不受任何限制。
• 若使用MySQL等不支持部分唯一索引的数据库，新增一个冗余字段active_flag：
  规则为：任务状态为active时active_flag值等于user_id，非活跃状态时值为NULL，再给active_flag字段创建唯一索引。利用MySQL唯一索引允许多个NULL值存在的特性，即可实现同一用户仅一个活跃任务的限制。

更新状态时直接执行SQL即可，不需要提前做存在性校验：如果执行触发唯一约束冲突，说明当前用户已经存在活跃任务，直接返回对应提示即可，完全避免并发问题。

方案2：行级排他锁（适合无法修改表结构加约束的场景） #

如果暂时不能调整表结构，可以把全表锁改成针对对应用户的行级锁，性能远高于全表锁：

1. 开启事务
2. 执行带排他锁的查询，锁定当前用户的所有任务行：

   SELECT 1 FROM tasks WHERE user_id = #{current_user_id} FOR UPDATE;
   

3. 校验当前用户是否存在active状态的任务，若已存在直接回滚事务返回错误
4. 若不存在，执行对应任务的状态更新操作
5. 提交事务

该方案只会锁定当前用户的任务行，不同用户的操作完全不冲突，并发性能比全表锁高几个数量级。

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