MySQL执行UPDATE时如何获取更新前后行数据，SELECT FOR UPDATE方案是否正确？

核心问题1：是否会两次扫描表 #

你的理解是正确的。
在age字段没有索引的场景下，SELECT ... FOR UPDATE会先全表扫描筛选出所有age>10的行并加排他锁，后续执行UPDATE时会再次全表扫描匹配条件的行执行更新，确实会触发两次全表扫描。如果age建有索引，两次查询都会走索引，但依然会做两次索引扫描+匹配行的读取操作。

核心问题2：原方案是否为正确实现 #

原方案功能上是可行的，SELECT ... FOR UPDATE的排他锁可以保证事务执行过程中其他会话无法修改你选中的行，避免出现数据不一致、幻读等问题，你拿到的待更新数据和实际更新的行完全一致。但这个方案存在明显缺陷：

• 若满足age>10的行数较多，事务持有锁的时间会很长（你需要在事务中间执行非MySQL的自定义逻辑），会严重阻塞其他业务对user表的写入操作，并发场景下极易出现锁等待、死锁问题
• 两次扫描表的性能开销更高，数据量大时效率很低

更规范的实现方案 #

方案1：MySQL 8.0.20+版本最优解（推荐） #

MySQL 8.0.20及以上版本支持UPDATE的RETURNING子句，可以在更新的同时直接返回被更新行的数据，全程只需要一次表扫描，不需要手动维护长事务，性能最高：

UPDATE user 
SET 待更新字段 = 新值 -- 替换为你的实际更新逻辑
WHERE age > 10
RETURNING *; -- 可以指定返回字段，比如RETURNING id, name, age

执行完该语句会直接返回所有被更新行的对应字段，你拿到返回结果后执行自定义处理逻辑即可，锁持有时间极短，没有长事务风险。

方案2：低版本MySQL兼容方案 #

如果你的MySQL版本低于8.0.20，不支持RETURNING语法，可以用分批短事务的方案优化：

1. 先一次性查询所有满足条件的行主键，存储在应用侧

SELECT id FROM user WHERE age > 10;

2. 将主键拆分成分批（比如每批100个），每批单独开启短事务处理：

START TRANSACTION;
-- 按主键批量查询待更新的完整数据，加锁
SELECT * FROM user WHERE id IN (当前批次主键列表) FOR UPDATE;
-- 执行你的自定义处理逻辑
-- 按主键批量更新
UPDATE user SET 待更新字段=新值 WHERE id IN (当前批次主键列表);
COMMIT;

该方案将长事务拆分为多个短事务，锁持有时间大幅降低，所有查询、更新都走索引，性能远优于原方案。

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