如何显式锁定MySQL表？多实例场景下批量处理优化需求

针对你多服务器实例下的任务分配需求，IsolationLevel.RepeatableRead并不能解决核心的并发抢任务问题——它只能保证当前事务内的读取一致性，但无法阻止其他节点读取相同的未处理行，会导致重复处理。以下是更优的实现方案，按推荐优先级排序：

1. 行级锁+原子更新（推荐） #

利用数据库原生的行级锁机制，只锁定需要处理的行而非全表，既保证任务不重复，又能最大化并发效率。核心是使用SELECT ... FOR UPDATE SKIP LOCKED语法（PostgreSQL、MySQL 8.0+、SQL Server 2019+等主流数据库均支持），让多个节点同时查询时自动跳过已被锁定的行。

正确流程（关键：事务只做锁和标记，耗时处理放在事务外） #

1. 开启数据库事务
2. 查询并锁定30条未处理的行：

   SELECT id FROM task_table WHERE status = 'pending' LIMIT 30 FOR UPDATE SKIP LOCKED;
   

3. 将这些行的状态更新为processing（标记为已被获取）：

   UPDATE task_table SET status = 'processing', process_node = '当前节点标识', update_time = NOW() 
   WHERE id IN (上述查询返回的ID列表);
   

4. 提交事务（立即释放锁，避免长时间占用）
5. 在事务外对这30行执行耗时的关联处理
6. 处理完成后，更新状态为completed；若处理失败，重置为pending（或标记为failed用于后续重试）

优势 #

• 锁粒度极小，仅锁定待处理行，不影响其他节点的任务获取，并发效率最高
• 数据库原生支持，无需额外依赖组件，可靠性强
• 事务耗时极短，不会因30-40秒的业务处理阻塞锁资源

2. 分布式锁+数据库标记（兼容老版本数据库） #

如果你的数据库不支持SKIP LOCKED（如MySQL 5.x），可以引入分布式锁（如Redis、ZooKeeper）来实现全局互斥，确保同一时间只有一个节点能读取并标记任务：

流程 #

1. 节点尝试获取分布式锁（设置合理超时时间，比如30秒，避免节点挂掉后锁无法释放）
2. 获取锁成功后，查询30条pending状态的行，更新为processing
3. 释放分布式锁
4. 执行耗时的关联处理
5. 更新任务状态为completed或failed

注意事项 #

• 必须处理锁超时：若节点在持有锁期间崩溃，锁需自动过期，避免任务无法被其他节点获取
• 锁粒度是全局的，同一时间只有一个节点能获取任务，并发效率低于行级锁方案

3. 全表锁（不推荐） #

使用数据库的全表锁（如MySQL的LOCK TABLES task_table WRITE），强制同一时间只有一个节点能操作表。但这种方案会完全阻塞其他节点对该表的所有操作，并发性能极差，仅适合极低并发或表操作极少的场景。

示例代码 #

LOCK TABLES task_table WRITE;
SELECT id FROM task_table WHERE status = 'pending' LIMIT 30;
UPDATE task_table SET status = 'processing' WHERE id IN (上述ID列表);
UNLOCK TABLES;

劣势 #

• 全表阻塞，其他节点的读写操作都会被挂起，严重影响系统整体性能
• 若节点持有锁期间崩溃，可能导致锁长时间无法释放，需手动干预

关键注意事项 #

• 任务失败重试：务必处理任务处理失败的情况，将状态重置为pending，避免任务丢失
• 超时清理：定期扫描processing状态超过阈值（如1小时）的任务，重置为pending，防止节点崩溃导致任务永久卡住

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