select for update skip locked 是队列消费的刚需，它使并发消费者跳过已被锁定的行，互不阻塞地获取互斥任务子集，需满足隔离级别≥read committed、for update加锁、表有主键或唯一索引三个条件。
SELECT FOR UPDATE SKIP LOCKED 是队列消费的刚需
传统“先查再更新”模式在并发消费时必然卡住：两个任务同时 SELECT * FROM queue WHERE status = 'pending' LIMIT 1，接着都去 UPDATE queue SET status = 'processing' WHERE id = ?，后执行的 UPDATE 会等前一个事务释放锁，甚至超时失败。MySQL 8.0 的 SKIP LOCKED 直接跳过已被锁定的行，让每个消费者拿到互不重叠的数据子集。
必须满足三个条件才生效：
– 使用 READ COMMITTED 或更高隔离级别
– 查询带 FOR UPDATE（不能是 LOCK IN SHARE MODE）
– 表有主键或唯一索引（否则会退化为表锁）
典型写法：SELECT id, payload FROM task_queue WHERE status = 'pending' ORDER BY id ASC LIMIT 10 FOR UPDATE SKIP LOCKED
为什么不能只靠索引优化来解决抢任务问题
即使给 status 加了索引，当大量任务状态集中更新时，InnoDB 仍会因 next-key lock 锁住间隙，导致后续查询被阻塞——这不是索引缺失的问题，而是锁范围不可控。比如 WHERE status = 'pending' 可能锁住 (pending, processing) 这个区间，新插入的 pending 任务也会被拦住。
真正有效的约束方式是：
– 把消费逻辑从“按状态查”改为“按时间+状态+分片ID查”，例如加 AND created_at 缩小扫描范围<br>– 队列表必须有自增主键 <code>id，且消费时强制 ORDER BY id ASC，避免不同事务加锁顺序不一致引发死锁
– 禁用 SELECT ... FOR UPDATE 不带 LIMIT，否则可能锁全表
事务内只做状态标记，其余全部移出
很多团队把消息解析、HTTP 调用、日志写入全塞进同一个事务里，结果一个外部接口超时，整个事务持锁 30 秒，下游所有消费者干等。这完全违背了“快进快出”原则。
正确拆分方式：
– 事务内只做两件事：用 SELECT ... FOR UPDATE SKIP LOCKED 拿到任务 ID 列表，再用一条 UPDATE task_queue SET status = 'processing', worker_id = ? WHERE id IN (...) 标记状态
– 任务实际处理（如调第三方 API）放在事务外，成功后再发一次轻量 UPDATE 改为 'done'，失败则改回 'pending' 并加重试计数
– 所有非 DB 操作必须设超时（如 HTTP client timeout ≤ 8s），避免拖垮整个消费链路
别忽略隐式锁升级和连接泄漏风险
当单次消费取太多行（比如 LIMIT 1000），InnoDB 可能触发锁升级：从行锁自动转为页锁甚至表锁，尤其在 buffer pool 不足时。同时，如果应用没正确关闭数据库连接，或消费逻辑抛异常未 rollback，那些 FOR UPDATE 锁会一直挂着，直到连接超时（默认 wait_timeout=28800 秒），期间所有新消费请求都被堵死。
防御性做法：
– 单次拉取控制在 10–100 行之间，根据任务平均耗时动态调整
– 消费代码必须用 try/finally 或 context manager 保证 COMMIT 或 ROLLBACK 执行
– 定期查 SELECT * FROM performance_schema.data_lock_waits，看是否有长时间未释放的锁
– 在应用层记录每次消费的 worker_id 和开始时间，超时未完成的主动触发补偿清理
