余额/库存 并发扣款问题

场景1：数据库只有余额字段，表定义如下

CREATE TABLE `account_balance` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `account_id` bigint(11) NOT NULL COMMENT '账号id',
  `balance` decimal(16,5) NOT NULL COMMENT '账户余额',
  `gmt_created` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `created_by` varchar(64) DEFAULT NULL COMMENT '创建人',
  `gmt_modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `modified_by` varchar(64) DEFAULT NULL COMMENT '修改人',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_account_id` (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='账户余额表';

处理方式：

@Update("UPDATE account_balance SET balance = balance - #{fee} " +
            "WHERE account_id = #{accountId} AND balance - #{fee} >= 0 ")
    int doDeduct(@Param("accountId") Integer accountId, @Param("fee") BigDecimal fee);

判断返回结果=1，则扣费成功，否则扣费失败

场景2：数据库有余额、赠送金额字段，表定义如下

CREATE TABLE `account_balance` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `account_id` bigint(11) NOT NULL COMMENT '账号id',
  `balance` decimal(16,5) NOT NULL COMMENT '账户余额',
  `gift_balance` decimal(16,5) NOT NULL COMMENT '赠送余额',
  `version` bigint(20) NOT NULL DEFAULT '0' COMMENT '版本号',
  `gmt_created` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `created_by` varchar(64) DEFAULT NULL COMMENT '创建人',
  `gmt_modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `modified_by` varchar(64) DEFAULT NULL COMMENT '修改人',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_account_id` (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COMMENT='账户余额表';

要求先扣减赠送金额，赠送金额扣减完毕之后再扣减账户余额。

有如下几种场景
扣费前-账户余额扣费前-赠送余额扣费金额扣费后-账户余额扣费后-赠送余额803508438184570
解决方案

1、使用redis分布式锁，扣费前，先拿到锁，然后查询账户余额信息，判断是扣赠送金额，还是扣账户余额，还是先把赠送余额扣完、剩余部分再扣账户余额。这种方式可自行实现。

2、自定义乐观锁，在表中定义version字段，先查询账户余额信息，判断赠送余额和账户余额，实际扣费的时候，带上余额信息的version字段，如果version字段变了，则重新执行。大致代码如下：

    /**
     * 账户余额扣减
     *
     * @param accountId
     * @param money
     * @return
     */
    @Override
    public Boolean deductBalance(Long accountId, BigDecimal money) {
        log.info("deductBalance, accountId: {}, money: {}", accountId, money);

        LambdaQueryWrapper<AccountBalance> query = new LambdaQueryWrapper<>();
        query.eq(AccountBalance::getAccountId, accountId);

        while (true) {
            AccountBalance accountBalance = this.getOne(query);

            if (accountBalance.getGiftBalance().add(accountBalance.getBalance()).compareTo(money) < 0) {
                // 账户余额不足
                return false;
            }

            if (accountBalance.getGiftBalance().compareTo(money) >= 0) {
                // 赠送余额足够扣减
                int res = baseMapper.deductGiftBalanceByVersion(accountId, money, accountBalance.getVersion());
                if (res == 1) {
                    return true;
                }
            } else if (accountBalance.getGiftBalance().compareTo(BigDecimal.ZERO) > 0) {
                // 赠送金额大于0，但是少于扣减金额
                int res = baseMapper.deductBalanceByMix(accountId, money, accountBalance.getVersion());
                if (res == 1) {
                    return true;
                }
            } else {
                // 从账户余额扣款
                int res = baseMapper.deductBalanceByVersion(accountId, money, accountBalance.getVersion());
                if (res == 1) {
                    return true;
                }
            }
        }
    }

     /**
     * 扣减赠送余额
     *
     * @param accountId
     * @param amount
     * @return
     */
    @Update("<script>" +
            "update account_balance set gift_balance = gift_balance - #{amount}, version = version + 1 " +
            "where account_id = #{accountId} and gift_balance - #{amount} >= 0 and version = #{version} " +
            "</script>")
    int deductGiftBalanceByVersion(@Param("accountId") Long accountId, @Param("amount") BigDecimal amount, @Param("version") Long version);

     /**
     * 扣减余额(先把赠送金额扣减完，再扣账户余额)
     *
     * @param accountId
     * @param amount
     * @return
     */
    @Update("<script>" +
            "update account_balance set balance = balance + gift_balance - #{amount}, gift_balance = 0, version = version + 1 " +
            "where account_id = #{accountId} and balance + gift_balance - #{amount} >= 0 " +
            "and version = #{version} " +
            "</script>")
    int deductBalanceByMix(@Param("accountId") Long accountId, @Param("amount") BigDecimal amount, @Param("version") Long version);

    /**
     * 扣减余额
     *
     * @param accountId
     * @param amount
     * @return
     */
    @Update("<script>" +
            "update account_balance set balance = balance - #{amount}, version = version + 1 " +
            "where account_id = #{accountId} and balance - #{amount} >= 0 " +
            "and version = #{version} " +
            "</script>")
    int deductBalanceByVersion(@Param("accountId") Long accountId, @Param("amount") BigDecimal amount, @Param("version") Long version);

大家有没有更好的处理方式？