金钱业务数据库事务相关要点记录

发表于

表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE TABLE `user_balance` (
`user_id` varchar(128) NOT NULL DEFAULT '' COMMENT '用户ID',
`coin` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '余额',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`user_id`),
KEY `idx_updatetime` (`update_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='账户余额表';


CREATE TABLE `user_balance_log` (
`log_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '流水id',
`order_id` varchar(128) NOT NULL DEFAULT '' COMMENT '业务订单ID',
`user_id` varchar(128) NOT NULL DEFAULT '' COMMENT '用户ID',
`type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '1：加，2：减',
`coin` bigint(20) NOT NULL DEFAULT '0' COMMENT '变更金额',
`coin_after` bigint(20) NOT NULL DEFAULT '0' COMMENT '变更后的金额',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`log_id`),
KEY `idx_createtime` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='账户余额表流水表';

INSERT INTO `user_balance`(`user_id`, `coin`, `create_time`, `update_time`) VALUES ('kxw', 20000000, '2024-10-23 00:00:00', '2024-10-23 00:00:00');

UPDATE `user_balance` SET `coin`= `coin` - 1000 WHERE `user_id` = 'kxw' AND `coin` >= 1000;

操作用户余额时注意

  1. 可重复读（记录扣费后余额快照）(select coin 后记录到user_balance_log表的coin_after)
  2. 乐观锁（扣费冲突）（set coin - 1000 where coin >= 1000）
  3. 业务幂等（不同业务使用相应的订单表）
  4. 事务（用户余额表user_balance、日志流水表user_balance_log、业务订单表绑定）
  5. canal 监听 binlog 识别业务异常 (对比 binlog监听的user_balance表coin变化量 和 user_balance_log的 coin记录总量 是否一致)

流水表只需要记录coin_after

事务过程

事务1 事务2
BEGIN;
BEGIN;
SELECT * FROM user_balance WHERE user_id = ‘kxw’; (coin=20000000)
UPDATE user_balance SET coin= coin - 1000 WHERE user_id = ‘kxw’ AND coin >= 1000;
SELECT * FROM user_balance WHERE user_id = ‘kxw’; (coin=20000000)
UPDATE user_balance SET coin= coin - 1000 WHERE user_id = ‘kxw’ AND coin >= 1000;
SELECT * FROM user_balance WHERE user_id = ‘kxw’; (coin=19999000)
COMMIT;
SELECT * FROM user_balance WHERE user_id = ‘kxw’; (coin=19998000)
COMMIT;
  • 流水表只需要记录coin_after（变更后的余额）即可，因为变更前的余额，可能因为并发导致不准确，除非开启事务后，使用select for update来查询，而不是用普通的快照读