MYSQL 高并发下，记录变动后余额怎么统计才能更准确？

[Aluhao]

-- 积分总表
CREATE TABLE `api_credits` (
`uid` bigint unsigned NOT NULL COMMENT '用户 ID',
`names` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户姓名',
`credits1` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '积分 1 余额',
`credits2` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '积分 2 余额',
`credits3` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '积分 3 余额',
`time` int NOT NULL DEFAULT '0' COMMENT '更新时间',
PRIMARY KEY (`uid`)
) ENGINE=InnoDB COMMENT='积分总表';

-- 积分记录表
CREATE TABLE `api_credits_log` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '记录 ID',
`uid` bigint unsigned NOT NULL DEFAULT '0' COMMENT '用户 ID',
`credits` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '积分变动',
`balance` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '变动后余额',
`cid` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '操作类型',
`time` int unsigned NOT NULL DEFAULT '0' COMMENT '记录时间',
PRIMARY KEY (`id`),
KEY `uid_time` (`uid`,`time`)
) ENGINE=InnoDB COMMENT='积分记录表';

消费 100 积分，向 积分总表 api_credits 减去用户总积分，并向积分记录表 api_credits_log 写入一条记录，
balance 用户余额计算是 api_credits 表中 credits1 - 100 （即 80000 - 100 = 79900 ）

消费事务会产生 SQL 数据：
SELECT * FROM `api_credits` WHERE `uid`='22' LIMIT 1
UPDATE `api_credits` SET `credits1`=`credits1`-'100' WHERE `uid`='22' AND `credits1`>='100'
INSERT INTO `api_credits_log` SET `uid`='22', `cid`='3', `credits`='100', `balance`='79900', `time`='1701001020'

但是在高并发场景下 SELECT 读出来的值不是最新，如，在同一时间段这个用户同时几条消费记录，这个变动后余额统计的就不是这么准确了，有没有什么好点的解决方案?

系统用了主从架构，读写分离，但是在这条事务中 SELECT 查的是主库

欢迎大家一起探讨一下。

[kanepan19]

余额和库存 经典的做法就 数据库事务操作
1 悲观锁 select for update
2 乐观锁 update check version

3 不启事务，用性能更好的 update set balance= balance - acoumt where balance - amount > 0 加延迟记录 资金日志（最重要的瞬时余额）

大致的性能 ： 1 和 2 tps 100 左右 3 tps 1000 +

热点账户等，真正高并发的，才去内存中计算余额， 这个存在内存和数据库的同步等复杂问题。

[nerkeler]

@kanepan19 3 mysql 互斥锁

[nerkeler]

和我现在做的东西差不多，我的是给系统的各个机构配发卡号卡密，我这边用的 mysql 互斥锁， 优先更新减少的操作，这个一旦成功代表操作正常，接着直接更新下面的操作，比如说你这个先消费就先处理这个库，如果减操作正常，那就直接加一条记录数据。

[jiayouzl]

事务!~

[dode]

再单独建一个流水表，只增

[siweipancc]

以前处理过这种问题，受限于无法套事务只能等待几十毫米再查询。正常来说套个事务，乐观式 update 再查询，顶多退出事务时抛错重试便是

[vacuitym]

加锁，然后先写 redis 缓存然后发顺序队列 mq 异步入库。

[jonsmith]

解决准确问题，上面也都提到了两种办法：select 和 update 语句换下位置，或者 select for update ，都需要事务。

如果再解决性能问题，单纯 MySQL 解决不了，并发很高需要 redis 缓存、消息队列等综合的措施。

建议是先用事务，保证余额不出错，再压测性能看情况。

[iseki]

MySQL 在默认的 RR 隔离等级下会产生 Lost Update 现象，会导致你的问题。
如果你一定要这么写，请考虑将事物隔离等级调整至 serializabel ，这是最简单粗暴的办法。MySQL 的这个隔离等级实现不良，性能可能比较差。
另一种办法：使用 SELECT...FOR UPDATE ，它会产生一个显示锁定，可以用于回避这个问题。
还有个取巧的办法：使用 UPDATE ... SET balance = balance - 100 这样的写法，MySQL 在这个情况下不会丢失更新，不会导致数据错误，只是如果你在前后 select 时，可能会发现 300 - 100 = 100 这样的诡异现象，MySQL 团队不认为这是个问题。

[iseki]

同样的问题在 PostgreSQL 中不存在，PostgreSQL 使用 SERIALIZABLE 隔离等级时，这种 select + update 的并发操作，并发事务中后来的 update 操作会报错回滚， 这就是为了避免 lost update ，同时 PostgreSQL 也不允许刚才说的那种取巧的办法，同样会报错回滚，因为 PostgreSQL 认为并发操作导致的这种反常是一个问题。

[iseki]

此外，我想发表一个暴论，互联网中相当一部分 Bug 和岗位都是那些喊着用分布式锁的程序员凭空创造出来的。

[iseki]

@iseki #30 纠正一个笔误，说的是 PostgreSQL 中同样采用 RR 隔离等级时，可序列化等级无需讨论，任何反常现象都是数据库 bug

[Masoud2023]

怎么总感觉加个事务就可以了

[kanepan19]

3 不启事务，用性能更好的 update set balance= balance - acoumt where balance - amount > 0 加延迟记录 资金日志（最重要的瞬时余额）

接上面问题，有问题， 不启事务，性能好，但是拿不到瞬时余额了。

[pkoukk]

先建一条积分记录，加一个状态字段，然后就可以用一条 update 解决了,大概写个意思
update api_credits as c, api_credits_log as log
set c.credits1=c.c.credits1-log.credits,
log.balance=c.credits1-log.credits,
log.status ='done'
where ...... and log.status='undone'

[Aluhao]

感谢大家的回复！

[nothingistrue]

接 #17 再说一些业务上的事。这篇要说的重点是：性能优化不是对业务透明的纯技术实现，好的性能优化往往判随着业务优化（即业务功能变更）。

先把那三个 SQL 转化成业务描述，这样更方便一些：
SELECT * FROM `api_credits` WHERE `uid`='22' LIMIT 1
——①、查询出指定 uid 的当前积分情况
UPDATE `api_credits` SET `credits1`=`credits1`-'100' WHERE `uid`='22' AND `credits1`>='100'
——②、对①查出来的积分，做积分扣减操作（原本的逻辑应该是「如果当前余额大于阈值，则计算最新余额后，更新为最新值」这种代码）
INSERT INTO `api_credits_log` SET `uid`='22', `cid`='3', `credits`='100', `balance`='79900', `time`='1701001020'
——③、对②所做的积分扣减做记录，需要记下变化后的余额

首先来说，在上面的场景中，第②步骤应该使用原本的代码逻辑，不该使用优化 SQL ，因为你已经做了第①步的查询，导致这种优化是无效的。② 这种优化方式，主要就是为了避开查询 SQL 上应用跟数据库之间的网络交互时间，那么你如果要用这种优化，就必须避开 ① 这一步。当你使用 update ... set col = col - num 这种 SQL 的时候，你需要避开任何相关查询 SQL ，通常你更应该用「一句」 SQL 完成整个业务操作。

然后，你之所以要做①，是因为③当中要记录余额。这时候你会发现，使用 「 update ... set col = col - num 」来做优化的性能要求， 记录余额的功能要求，是冲突的。如果你要就地修改，那么就无法同时获取余额值，包括修改前和修改后；如果你要获取修改后的余额值，那么就必须先将当前余额值或者修改后的余额值查询出来，不能单纯的就地修改。

最后就是要做选择的时候了，既然高并发性能要求跟记录余额的功能要求冲突，那就要做 2 选 1 。通常都会选择不记录余额，即余额变更记录，只记录变更事件、变更金额，不记录变更后以及变更前的余额。相比与高并发/快速扣减、不能超扣、事后可查每次的扣减记录这些核心业务，扣减记录上的余额展示，就只能算作边缘业务被抛弃了。这是有现实示例的：信用卡账单基本都这样；对于套餐类型的移动通话，你要去查通话详单，它的详单条目上也只会有通话时间，没有通话后的套餐剩余时间——如果你要精确对比，还得自己算；有些银行的借记卡消费提醒是只提醒消费多少不提醒消费后余额的。

[EasyProgramming]

你那边碰到的情况是不是：你的这段逻辑里面应该是有加锁的吧，比如根据用户 id 加了锁，是能够保证这段逻辑是串行执行的，但还是出现了并发问题，是不？

[EasyProgramming]

@EasyProgramming 认真看了下场景，发现和我之前碰到的问题并不一致；你这个问题，如果你的数据库隔离级别是 RR 的话，推荐了解下 MVCC 的机制以及当前读和快照读，应该就会有答案了

[liprais]

@nothingistrue 对于套餐类型的移动通话，你要去查通话详单，它的详单条目上也只会有通话时间，没有通话后的套餐剩余时间——如果你要精确对比，还得自己算
只是运营商不给你看罢了，信控系统十多年前就精确的知道你还有多少额度了
而且运营商的计费系统从一开始就是事件驱动的，只是他们不这么叫而已