大行数UPDATE/DELETE事务的行锁获取时机咨询

不同主流数据库的行锁获取逻辑略有差异，但核心都是逐行获取锁，而非事务启动时一次性锁定所有目标行：

MySQL（InnoDB引擎） #

• InnoDB会按照执行计划扫描数据，每找到一行符合条件的记录，就立即为该行加上排他锁（X锁），直到所有符合条件的行处理完毕。
• 注意：如果你的语句没有合适的索引导致全表扫描，那么InnoDB会给扫描过的每一行（哪怕不符合条件的）都加锁，最终事务会持有所有被扫描行的锁，但这些锁是在扫描过程中逐步加上的，并非一开始就全部锁定。
• 所有锁会一直持有到事务提交或回滚，所以一次性操作1亿行的话，锁占用时间极长，容易引发锁等待超时、死锁，甚至因锁内存占用过高导致数据库异常。

PostgreSQL #

• PostgreSQL的逻辑类似，执行DELETE/UPDATE时会遍历符合条件的行，每处理一行就对该行加排他锁，锁同样会保持到事务结束。
• 即便是全表级的更新/删除，锁也是在处理过程中逐行添加的，不会在事务启动阶段一次性锁定所有行。

实用建议 #

• 绝对不要直接执行一次性操作1亿行的语句，风险极大。建议拆分成小批量操作，比如每次处理1万行并提交事务，循环执行直到完成：

  -- 示例：MySQL分批删除
  DELETE FROM your_table WHERE your_condition LIMIT 10000;
  

• 确保语句有合适的索引，避免全表扫描，缩小锁的覆盖范围。
• 尽量在业务低峰期操作，减少对正常业务的影响。

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