并发交易场景下账户余额一致性处理方案咨询

问题背景 #

基于MariDB开发账户交易系统，account_transaction表结构如下：

原存入/取出操作通过INSERT内嵌子查询获取最新余额，但并发量超过50时出现竞态问题：比如余额1000时，两笔并发扣减100的交易最终resulting_amount均为900，导致余额不一致。

解决方案 #

1. 事务+行级锁（针对account_id） #

利用MariDB的SELECT ... FOR UPDATE语句锁定目标账户的最新交易记录，确保同一时间仅一个事务能修改该账户余额，避免并发冲突。

存入300的操作：

• 开启事务
• 锁定account_id=100的最新交易记录，无记录则返回0
• 计算新余额并插入交易记录
• 提交事务

对应的SQL代码：

START TRANSACTION;

-- 锁定目标账户的最新交易记录，无记录时默认余额为0
SET @latest_balance = COALESCE(
    (SELECT at.resulting_amount 
     FROM account_transaction at
     WHERE at.account_id = 100 
     ORDER BY at.date DESC, at.id DESC 
     LIMIT 1 FOR UPDATE),
    0
);

INSERT INTO account_transaction (account_id, date, value, resulting_amount)
VALUES (100, NOW(), 300, @latest_balance + 300);

COMMIT;

取出300的操作：

• 开启事务
• 锁定目标账户的最新交易记录，获取当前余额
• 校验余额充足后插入扣减交易记录，否则回滚事务
• 提交事务

对应的SQL代码：

START TRANSACTION;

SET @latest_balance = COALESCE(
    (SELECT at.resulting_amount 
     FROM account_transaction at
     WHERE at.account_id = 100 
     ORDER BY at.date DESC, at.id DESC 
     LIMIT 1 FOR UPDATE),
    0
);

-- 校验余额是否充足，避免透支
IF @latest_balance >= 300 THEN
    INSERT INTO account_transaction (account_id, date, value, resulting_amount)
    VALUES (100, NOW(), -300, @latest_balance - 300);
ELSE
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
END IF;

COMMIT;

2. 高并发优化：新增账户余额表 #

为提升并发性能并简化锁逻辑，可新增account_balance表专门存储账户当前余额：

CREATE TABLE account_balance (
    account_id INT PRIMARY KEY,
    current_balance DECIMAL(18,2) NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL DEFAULT NOW()
);

此时交易操作简化为：

-- 存入300
START TRANSACTION;

-- 若账户存在则更新余额，不存在则插入初始余额
INSERT INTO account_balance (account_id, current_balance)
VALUES (100, 300)
ON DUPLICATE KEY UPDATE current_balance = current_balance + 300;

-- 插入交易记录
INSERT INTO account_transaction (account_id, date, value, resulting_amount)
VALUES (100, NOW(), 300, (SELECT current_balance FROM account_balance WHERE account_id = 100));

COMMIT;

-- 取出300
START TRANSACTION;

-- 仅当余额充足时更新
UPDATE account_balance 
SET current_balance = current_balance - 300
WHERE account_id = 100 AND current_balance >= 300;

-- 检查更新行数，判断是否成功
IF ROW_COUNT() = 1 THEN
    INSERT INTO account_transaction (account_id, date, value, resulting_amount)
    VALUES (100, NOW(), -300, (SELECT current_balance FROM account_balance WHERE account_id = 100));
ELSE
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
END IF;

COMMIT;

这种方式下，INSERT ... ON DUPLICATE KEY UPDATE和UPDATE语句会自动锁定account_balance表中对应account_id的行，避免并发问题，同时余额查询效率更高，适合高并发场景。

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