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

-- 积分总表
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 查的是主库

欢迎大家一起探讨一下。

comment

null

default

not

63 条回复 • 2024-05-10 19:27:37 +08:00

miaotaizi

352 天前

同一个用户还能高并发?

这是谁的问题

kanepan19

352 天前

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

352 天前

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

Aluhao

352 天前

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

Aluhao

352 天前

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

looo

352 天前

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

352 天前

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

cxk0

352 天前

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

bitmin

352 天前

有个麻烦的做法

第一次插入 api_credits_log 表不记录 balance

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

k9982874

352 天前

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

Aluhao

352 天前

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

looo

352 天前

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

Aluhao

352 天前

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

looo

352 天前

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

Aluhao

352 天前

@looo 感谢建议！

leaves615

352 天前

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

Aluhao

352 天前

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

nothingistrue

352 天前

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

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

bitmin

352 天前

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

happyxhw101

352 天前

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

kanepan19

352 天前

余额和库存经典的做法就数据库事务操作
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

352 天前 via Android

@kanepan19 3 mysql 互斥锁

nerkeler

352 天前 via Android

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

jiayouzl

352 天前

事务!~

dode

352 天前

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

siweipancc

352 天前 via iPhone

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

vacuitym

352 天前

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

jonsmith

352 天前

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

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

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

iseki

352 天前

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

iseki

352 天前

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

iseki

352 天前

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

iseki

352 天前 via Android

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

Masoud2023

352 天前

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

kanepan19

352 天前

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

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

pkoukk

352 天前

先建一条积分记录，加一个状态字段，然后就可以用一条 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

352 天前

感谢大家的回复！

nothingistrue

352 天前

接 #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

352 天前

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

EasyProgramming

352 天前

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

liprais

352 天前

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

asmile1993

352 天前

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

-- 步骤一：先变更，这样会锁住 `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

352 天前

redis 配合 lua 脚本搞

iseki

352 天前 via Android

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

sunny1688

352 天前

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

l4ever

352 天前

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

cavities

352 天前

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

pengjay

352 天前

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

leimu012

352 天前

学习一下

tonyli

352 天前

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

2kCS5c0b0ITXE5k2

351 天前

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

hefish

351 天前

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

yufeng0681

351 天前

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

crazyweeds

351 天前

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

bianhui

351 天前

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

sprite82

351 天前

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

gitdoit

351 天前

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

echoZero

351 天前

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

zhuzhibin

351 天前 via iPhone

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

allenzhangSB

351 天前

你需要的是更新并查询, 在 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

351 天前

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

kanepan19

351 天前

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

pincoudeduanyin

195 天前

@allenzhangSB 老哥，🐂，这个方案很好，我也碰到这种问题了，但是我想问下，这样子 update ，应该和正常的 update ，性能上是没有区别的吧

allenzhangSB

195 天前

@pincoudeduanyin 性能上没区别, 要注意下这个变量的生命周期等, 具体可以再查下资料有哪些注意事项, 我也是很多年前用过, 有些细节忘记了