MySQL：批量更新表行时避免行锁定的技术方案咨询

针对你每5分钟批量更新数百万条blogs表记录（update blogs set is_visible=1 where some conditions），同时不想阻塞其他字段写入的需求，这里有几个实用的方案，核心思路是缩短锁的持有时间和减少单次锁定的行数：

1. 分批小批量更新（最推荐） #

默认的全量更新会一次性锁定符合条件的所有行，直到整个事务提交，这会严重阻塞其他字段的更新。改成每次处理一小批数据，完成后立即提交事务，能大幅降低锁的影响。

实现方式： #

用主键（或唯一索引）分段处理，比如按id每次取1000条记录更新，循环直到没有符合条件的记录：

-- 初始化上次处理的id（第一次可以设为0）
SET @last_id = 0;

-- 循环处理，直到没有更新的记录
REPEAT
    UPDATE blogs 
    SET is_visible = 1 
    WHERE some conditions 
      AND id > @last_id 
      AND id <= @last_id + 1000; -- 每次处理1000条，可根据实际调整

    -- 更新上次处理的id
    SET @last_id = @last_id + 1000;
    
    -- 检查是否还有未处理的记录
    SELECT COUNT(*) INTO @remaining 
    FROM blogs 
    WHERE some conditions 
      AND id > @last_id;
UNTIL @remaining = 0 END REPEAT;

为什么有效： #

• 每次事务只锁定1000行，处理完成后立即提交，锁会被快速释放，其他字段的更新操作可以正常进行。
• 用主键分段避免了全表扫描，更新效率更高，锁等待时间更短。

2. 调整事务隔离级别（辅助优化） #

将批量更新进程的事务隔离级别设置为READ COMMITTED（读已提交），InnoDB在这个级别下会释放不再需要的锁，进一步减少锁的持有时间：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

结合分批更新使用，能最大化降低锁的影响。

注意事项 #

• 分批大小调整：如果1000条还是导致轻微阻塞，可以缩小到500条；如果系统负载低，也可以适当增大到2000-5000条，找到适合你业务的平衡点。
• 避免重复处理：确保some conditions在分批处理中不会重复匹配已经更新过的记录（比如把is_visible=0加入条件，避免重复更新）。
• 监控锁状态：可以通过INFORMATION_SCHEMA.INNODB_LOCKS和INFORMATION_SCHEMA.INNODB_LOCK_WAITS查看锁等待情况，实时调整分批策略。

因为你明确只有这个批量进程操作is_visible字段，所以不用担心并发更新该字段的冲突，以上方案完全适用。

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