mysql在多人协作平台中的任务分配系统设计

任务表必须设version字段实现乐观锁，每次更新需校验并递增version；assignee_id外键须配ON UPDATE CAScadE；status用enum严格定义；分配操作须用事务+行锁（select … for UPDATE）确保原子性。

任务表必须带 version 字段防并发覆盖

多人同时编辑同一个任务（比如修改 status 或 assignee_id）时，不加控制会导致后写入者覆盖前写入者的变更。mysql 本身不提供乐观锁语法，得靠 version 字段 + WHERE version = ? 实现。

建表时加 version int default 0，每次更新都递增：

UPDATE tasks  SET status = 'in_progress', assignee_id = 123, version = version + 1  WHERE id = 456 AND version = 2;

执行后检查 ROW_COUNT() 是否为 1；为 0 表示已被别人抢先更新，应用层需重试或提示冲突。

• version 必须是整数类型，不能用 timestamp 或 UUID 模拟，否则无法原子递增
• 所有 UPDATE 都要带上 version 条件，漏掉一次就等于留了并发漏洞
• 首次插入时 version 设为 0，不是 NULL

assignee_id 外键必须配 ON UPDATE CASCADE

协作平台里用户 ID 可能因同步、迁移或主键调整而变化。如果 tasks.assignee_id 是外键但没设级联更新，用户改 ID 后任务就指向一个不存在的用户，查出来是 NULL 或报错，且无法回溯原始归属。

建表或修改外键时明确指定：

ALTER TABLE tasks  ADD CONSTRaiNT fk_assignee  FOREIGN KEY (assignee_id) REFERENCES users(id)  ON UPDATE CASCADE  ON delete SET NULL;

• ON DELETE SET NULL 比 CASCADE 更安全：用户注销不应自动删任务记录
• 确认 MySQL 引擎是 InnoDB，MyISAM 不支持外键
• 已有数据的表加外键前，先检查 assignee_id 是否全在 users.id 中，否则 ADD CONSTRAINT 会失败

任务状态流转要用 ENUM 而非字符串自由输入

放任前端传任意 status 值（如 'doing'、'do_ing'、'in progress '）会导致状态值碎片化，后续统计、筛选、状态机校验全部失效。

直接定义严格枚举：

status ENUM('pending', 'assigned', 'in_progress', 'reviewing', 'done', 'blocked') DEFAULT 'pending'

• ENUM 在存储和索引上比 VARCHAR 更省空间，查询也略快
• 新增状态必须 DDL 修改表结构，强迫团队对齐状态语义，避免“悄悄加个新状态”导致逻辑错乱
• 应用层不要依赖 ENUM 序号（如 status = 2），始终用字符串值比较
• 如果未来状态可能频繁扩展，再考虑拆出 task_statuses 码表，但初期别过度设计

分配操作必须走事务 + 行锁，不能只靠 WHERE 条件

常见错误是写个“先查再更”的逻辑：

SELECT * FROM tasks WHERE status = 'pending' ORDER BY created_at LIMIT 1;
UPDATE tasks SET status = 'assigned', assignee_id = 123 WHERE id = ?;

这中间存在竞态窗口：两个进程查到同一任务，先后 UPDATE，第二个会成功但业务上不该被分配两次。

正确做法是单条带行锁的 UPDATE：

UPDATE tasks  SET status = 'assigned', assignee_id = 123, version = version + 1  WHERE status = 'pending' AND id = (   SELECT id FROM (     SELECT id FROM tasks WHERE status = 'pending' ORDER BY created_at LIMIT 1   ) AS tmp )  AND version = (SELECT version FROM tasks WHERE id = ?);

更稳妥的是用 SELECT ... FOR UPDATE 显式加锁：

BEGIN;
SELECT id, version FROM tasks WHERE status = 'pending' ORDER BY created_at LIMIT 1 FOR UPDATE;
-- 拿到 id 和 version 后执行带 version 校验的 UPDATE
UPDATE tasks SET status = 'assigned', assignee_id = 123, version = version + 1 WHERE id = ? AND version = ?;
COMMIT;

• FOR UPDATE 只在事务中生效，且只能用于 InnoDB 表
• 锁的是索引行，确保 status 字段上有索引，否则会锁整张表
• 事务粒度要短，别在事务里调外部 API 或做耗时计算，否则锁持有太久拖垮并发

实际最难的不是字段怎么设，而是所有人——前端、后端、dba——都得理解并遵守那几条规则。比如 version 字段一旦漏判影响行数，或者有人绕过 ORM 直接写裸 UPDATE，整个并发控制就形同虚设。