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 查的是主库

欢迎大家一起探讨一下。

[miaotaizi]

同一个用户还能高并发?

这是谁的问题

[kanepan19]

update 放上面，第一步就行锁了， 再 select 的余额就是对了。

UPDATE `api_credits` SET `credits1`=`credits1`-'100' WHERE `uid`='22' AND `credits1`>='100'
SELECT * FROM `api_credits` WHERE `uid`='22' LIMIT 1
INSERT INTO `api_credits_log` SET `uid`='22', `cid`='3', `credits`='100', `balance`='79900', `time`='1701001020'

[coderxy]

单用户加分布式锁？ 正常情况下单个用户的操作可以去串行处理

[Aluhao]

@miaotaizi 有 API 串接给用户用，用户又是对接它的用户用。

[Aluhao]

@coderxy 不是单用户，在当前系统，这个用户 ID 是 22 ，可以在用户它的系统，可能它的用户有很多，他们要操作积分变动都是走的 22 这个用户的总积分。

[looo]

1. 你这个查询：SELECT * FROM `api_credits` WHERE `uid`='22' LIMIT 1 肯定要限制住拿到的是没有更新过的，可以采用数据库行锁：SELECT * FROM `api_credits` WHERE `uid`='22' LIMIT 1 FOR UPDATE; 记得加要加事务。

2. 建议你以后更新数据有并发的，可以增加 WHERE 条件，条件就是更新的值必须是你查询出来的值，然后在去 SET 更新。

[looo]

MySQL 默认存储引擎（ InnoDB ） UPDATE 会默认加行锁🔐，有时候巧妙的去利用 UPDATE 来避免资源竞争。

[cxk0]

如果仅有增加不会减少，类似等级，可以用记录消化；
如果是有增有减，SQL 级别的 select for update;

[bitmin]

有个麻烦的做法

第一次插入 api_credits_log 表不记录 balance

另外做个任务队列串行更新 balance ，当前 balance 为上一条的 balance - 当前 credits

[k9982874]

1. 楼上已经说了加 where 条件，加行锁
2. 使用消息队列，保证 update 按顺序执行

[Aluhao]

@cxk0 有增加也有减少的记录。

[looo]

还有这种减的操作，一定要先减、多次校验然后在去做业务。

[Aluhao]

@bitmin 还有一个关键的问题就是用户 API 请求过来，需要把最新的余额 balance 返回过去。

[looo]

不建议引入第三方工具来解决，实在没办法在采取。你这个还没有达到高并发，属于同时竞争资源导致的。

[Aluhao]

@looo 感谢建议！

[leaves615]

配置事物隔离级别：read on uncommit

[Aluhao]

@leaves615 可以试试，但这级别并发安全性较差

[nothingistrue]

不要让数据库做业务的事，这事 mysql 干不了。



你的业务逻辑本事是有问题的，属于性能优化事故。
既然第一步查出来了，那么后面 UPDATE `api_credits` 跟 INSERT INTO `api_credits_log` 时候的 `balance` ，都要依赖查出来的值，不能一个用查出来的，一个用底层存储实时的——绝大多数事务隔离级别下，这俩不是一个值。

[bitmin]

@Aluhao #13 你最新的余额 balance 不是有其他表记吗，这个值不需要从 log 表取

[happyxhw101]

这其实是一个 update return 类似的问题，如果是 pg 直接 update xxx return api_credits 就可以了，如果是 mysql 那就是大家说的先 update 再 select ，反正先 select 再 update 并不是一个合理的方法

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

[asmile1993]

原来的操作逻辑有问题，你第一步是查询用户积分总额，然后更新积分总额，最后再将积分余额插入到积分记录表中，第一步只读查询并没有加锁，因此是可以并发的，这可能导致并发的线程读取到的余额是不一致的，又因为你积分总表的更新逻辑是正确的，这会造成积分记录表中记录的积分余额错乱，而积分总表中的余额又是正确的。

-- 步骤一：先变更，这样会锁住 `uid`='22' 这条记录，别的会话只能查询，不能变更
UPDATE `api_credits` SET `credits1`=`credits1`-'100' WHERE `uid`='22' AND `credits1`>='100'
-- 步骤二：拿到变更后的最新的积分余额
SELECT * FROM `api_credits` WHERE `uid`='22' LIMIT 1
-- 将步骤二中获取到的积分余额插入到积分记录表中
INSERT INTO `api_credits_log` SET `uid`='22', `cid`='3', `credits`='100', `balance`='79900', `time`='1701001020'

将这三步放入到同一个事务中

[xiaoHuaJia]

redis 配合 lua 脚本搞

[iseki]

当然，解决这个问题的最佳方法是 ——写个存储过程

[sunny1688]

第一步用 select for update ，因为 uid 是主键，可以使用到行锁，这样就串行了，不会造成余额错乱
或者先 update ，其它会话无法 update ，这样也是串行

[l4ever]

积分记录表不要用 mysql 搞, 换成时序数据库

[cavities]

其中 不建议用 mysql 有钱换 redis 都可以

[pengjay]

表里加一个版本号字段，先 select 出版本号 v ，update money=money-10 ,ver=ver+1 where ver =v and xxx

[leimu012]

学习一下

[tonyli]

引入 redis ， 把主表 credits1 加载到 redis ，读取 redis 的数据进行操作，log 正常记录，做个定时任务，同步 redis 里面的数据到 mysql ，改动最小，能保证高并发

[emeab]

尽量还是别引入其他组件. 除非实在是无法解决. 不然处理起来很麻烦

[hefish]

要不转岗试试。。。太伤脑筋了。。

[yufeng0681]

引入 redis ，优先读取 redis 数据，没命中，则读取数据库到 redis 内，进行操作（增删）；命中了，直接进行操作； 定时将 N 分钟不更新的数据更新到数据库内，并从 redis 清空； 保证 redis 不会数据量过大
当 redis 异常，直接操作数据库，并在返回结果中告知用户数据不及时，需要精确数据，请稍后查询 [用户体验方面可优化]
当 redis 从异常恢复到正常，优先将数据更新到数据库内 [保证数据一致性]

[crazyweeds]

单个用户 UPDATE 、DEL 、ADD 操作增加全局锁（ http 接口层面）？一个用户瞬时下多单本来就不合理。

[bianhui]

不明白你 select 的作用是什么，你消费时候不是应该只写吗。select 本来就是快照读，你就不要想着读到他正确的值，除非锁行。再说了你 update 也加了 credites1>100 了。你是事物执行完，直接 select 查询余额返回就行了，展示功能别和写库逻辑放在一起。单独接口都行。

[sprite82]

@Aluhao #4 这个难道不应该叫商户吗，用户这次比较容易误解😂

[gitdoit]

反正,别再引入新的组件了, 不然一个问题就会变成 N 个问题

[echoZero]

我更习惯用 UPDATE `api_credits` SET `credits1`=`credits1`-'100' WHERE `uid`='22' AND `credits1`='1000'

[zhuzhibin]

uid 主键索性等值查询，先 update 会产生行锁 block 吧，后面事务同 uid 更新会被锁，在第一个事务没有提交前。要么就悲观锁，要么就分布式锁解决

[allenzhangSB]

你需要的是更新并查询, 在 MySQL 中可以使用变量来记录更新后的值, 然后在更新语句后查询该变量值,
UPDATE `api_credits` SET `credits1`= @xxx := `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 @xxx;

[TUNGH]

@kanepan19 #21 你的第三条,如果不启用事务,那么其他地方报错,这个 update 就无法回滚

[kanepan19]

@TUNGH
是这样的， 就是性能和 一致性的取舍问题。