MySQL丢失更新问题解决方案求助：双会话并发转账数据不一致

丢失更新问题根源 #

你当前的执行逻辑属于典型的读-改-写并发冲突：默认可重复读隔离级别下，事务启动后的普通SELECT为快照读，读取的是事务启动时刻的历史数据，无法感知其他事务已提交的修改。两个事务都基于初始查询到的旧余额计算更新，后提交的事务会直接覆盖先提交事务的修改，导致最终结果不符合预期。

可行解决方案 #

方案1：直接将计算逻辑嵌入UPDATE语句（最简便） #

不需要提前查询余额存入变量，直接在UPDATE语句中完成余额计算，MySQL单条UPDATE操作是原子性的，会自动对操作行加排他锁，多个事务的更新会串行执行，天然避免丢失更新：

-- 会话A执行转账30的逻辑
START TRANSACTION;
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
UPDATE accounts SET balance = balance + 30 WHERE id = 2;
COMMIT;

-- 会话B执行转账20的逻辑
START TRANSACTION;
UPDATE accounts SET balance = balance - 20 WHERE id = 1;
UPDATE accounts SET balance = balance + 20 WHERE id = 2;
COMMIT;

方案2：悲观锁实现（SELECT ... FOR UPDATE） #

如果业务逻辑必须先查询余额再做后续判断，可以在查询时加排他行锁，阻塞其他事务对同一行的读写操作，直到当前事务提交释放锁：

-- 事务内查询时添加FOR UPDATE后缀
START TRANSACTION;
SELECT balance INTO @user1_balance FROM accounts WHERE id = 1 FOR UPDATE;
SELECT balance INTO @user2_balance FROM accounts WHERE id = 2 FOR UPDATE;

-- 后续基于查询到的余额执行更新操作
UPDATE accounts SET balance = @user1_balance - 30 WHERE id = 1;
UPDATE accounts SET balance = @user2_balance + 30 WHERE id = 2;
COMMIT;

此时先开启的事务加锁后，另一个事务的SELECT ... FOR UPDATE会进入阻塞状态，直到前一个事务提交释放锁后才能拿到最新余额执行更新，不会出现覆盖问题。

方案3：乐观锁实现（版本号校验） #

适合并发度不高的场景，通过新增版本号字段实现无锁并发控制，更新时校验版本号是否和查询时一致，不一致说明数据已被其他事务修改，需要重试：

1. 先给账户表新增版本号字段

ALTER TABLE accounts ADD COLUMN version INT NOT NULL DEFAULT 1;

2. 事务执行逻辑调整：

START TRANSACTION;
-- 查询余额同时查询当前版本号
SELECT balance, version INTO @user1_balance, @user1_v FROM accounts WHERE id = 1;
SELECT balance, version INTO @user2_balance, @user2_v FROM accounts WHERE id = 2;

-- 执行更新时校验版本号，同时更新版本号
UPDATE accounts 
SET balance = @user1_balance - 30, version = version + 1 
WHERE id = 1 AND version = @user1_v;

UPDATE accounts 
SET balance = @user2_balance + 30, version = version + 1 
WHERE id = 2 AND version = @user2_v;

-- 判断两条UPDATE的影响行数，如果任意一条为0说明数据已被修改，回滚事务并重试
COMMIT;

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