MySQL双会话操作账户余额死锁及金额计算错误问题求助

余额计算错误 #

• 所有会话中使用的@user1_balance、@user2_balance变量，均是事务启动初期读取的快照值，不会随后续表数据的更新自动更新。比如你初始两个账户余额都是100，两个会话读取后变量存储的值均为100，后续即使账户余额发生了变更，更新操作仍然使用最初存储的旧值计算，自然会得到不符合预期的结果。
• 后续会话1执行UPDATE accounts SET balance = @user2_balance + 30 WHERE id = 2;时，用的还是初始的@user2_balance=100，计算得到130，而非基于实时余额90计算的120，就是该问题的直接体现。

死锁及锁逻辑错误 #

• 不合理使用表级读锁：会话2执行LOCK TABLES accounts read加表级读锁后，会直接阻塞所有其他会话对accounts表的写操作，会导致会话1的更新操作直接被挂起，和你要实现的并发转账逻辑完全冲突。
• 事务逻辑混乱：LOCK TABLES操作会隐式提交之前未提交的事务，你后续在会话2中重复执行START TRANSACTION、混用表锁和事务的操作，完全打乱了事务的原子性边界。
• 行更新顺序不一致：两个会话更新账户行的顺序相反（会话1先更新id=1再更新id=2，会话2先更新id=2再更新id=1），满足死锁的循环等待条件，是你之前遇到死锁的核心原因。

1. 废弃表级锁LOCK TABLES，使用InnoDB行级锁实现并发控制，查询余额时加排他锁避免脏读：

-- 转账事务固定先操作id小的账户，再操作id大的账户，统一顺序避免循环等待
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 = balance - 30 WHERE id = 1;
UPDATE accounts SET balance = balance + 30 WHERE id = 2;
COMMIT;

2. 不要提前读取余额存储到变量中搁置使用，尽量直接在UPDATE语句中做字段运算，或者加锁读取后立刻完成更新操作，避免使用过期值。
3. 所有涉及多账户更新的事务，统一按照id从小到大的顺序更新行，从根源上消除死锁的循环等待条件。
4. 单个转账操作的转出、转入逻辑要放在同一个事务中完成，不要拆分到多个会话/多个事务中执行，保证操作的原子性。

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