并发实际场景（保持余额操作的正确：数据库余额字段版）

场景：

一个人在一家银行办了一个账户，银行给了 一张卡（存取款）、一本存折（存取款）、一个网银（查询余额）

卡和存储不断存款和取款，网银不断查询余额。如何保持余额的正确。

 

数据库余额表：原本想用版本号来实现的，后面弃用version字段。

DROP TABLE IF EXISTS `t_test`;
CREATE TABLE `t_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account` decimal(11,2) DEFAULT NULL,
  `version` int(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_test
-- ----------------------------
INSERT INTO `t_test` VALUES ('1', '50.00', '1');

mapper.xml文件：仔细看两个sql的写法，这里是重点，请不要在java代码中进行余额的加减操作。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.taotao.mapper.TTestMapper" >
  <resultMap id="BaseResultMap" type="com.taotao.pojo.TTest" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="account" property="account" jdbcType="DECIMAL" />
    <result column="version" property="version" jdbcType="INTEGER" />
  </resultMap>

  <update id="updateAccountAdd" parameterType="com.taotao.pojo.TTest" >
    update t_test
    set account = account + #{newAccount,jdbcType=DECIMAL}
    where id = #{id,jdbcType=INTEGER}
  </update>

  <update id="updateAccountSub" parameterType="com.taotao.pojo.TTest" >
    update t_test
    set account = account - #{newAccount,jdbcType=DECIMAL}
    where id = #{id,jdbcType=INTEGER} and account >= #{newAccount,jdbcType=DECIMAL}
  </update>

</mapper>

dao暂时不贴出：

service：请在每个方法上加入事物和synchronized。

@Service
public class TestServiceImpl implements TestService {

    @Autowired
    private TTestMapper testMapper;

    /**
     * 存钱
     *
     * @param money
     */
    @Override
    @Transactional
    public synchronized BigDecimal addAcount(String name, int money) throws TransactionalException {

        TTest tTest = testMapper.selectByPrimaryKey(1);
        tTest.setNewAccount(new BigDecimal(money));

        int i = testMapper.updateAccountAdd(tTest);
        if (i == 0){
            System.out.println("添加余额失败！余额=" + tTest.getAccount());
            return new BigDecimal(money);
        }

        System.out.println(name + "...存入：" + money + "..." + Thread.currentThread().getName());

        return selectAcount(name);
    }

    /**
     * 取钱
     *
     * @param money
     */
    @Override
    @Transactional
    public synchronized BigDecimal subAcount(String name, int money) throws TransactionalException{

        TTest tTest = testMapper.selectByPrimaryKey(1);
        tTest.setNewAccount(new BigDecimal(money));
        int i = testMapper.updateAccountSub(tTest);
        if (i == 0){
            System.out.println("账户余额不足！余额=" + tTest.getAccount());
            return new BigDecimal(money);
        }

        System.out.println(name + "...取出：" + money + "..." + Thread.currentThread().getName());

        return selectAcount(name);
    }

    /**
     * 查询余额
     */
    @Override
    @Transactional
    public synchronized BigDecimal selectAcount(String name) throws TransactionalException{

        TTest tTest = testMapper.selectByPrimaryKey(1);

        System.out.println(name + "...余额：" + tTest.getAccount());

        return tTest.getAccount();
    }
}

controller:

@Controller
public class TestMysqlController {

    @Autowired
    private TestService testService;

    @RequestMapping(value="/cardAddAcountMysql")
    @ResponseBody
    public TaotaoResult<Integer> cardAddAcount() throws TransactionalException{
        TaotaoResult<Integer> result = new TaotaoResult<Integer>();
        result.setData("+100, 余额： " + testService.addAcount("card", 100));
        return  result;
    }

    @RequestMapping(value="/passbookAddAcountMysql")
    @ResponseBody
    public TaotaoResult<Integer> passbookAddAcount() throws TransactionalException{
        TaotaoResult<Integer> result = new TaotaoResult<Integer>();

        result.setData("+100, 余额： " + testService.addAcount("存折", 100));
        return  result;
    }

    @RequestMapping(value="/cardSubAcountMysql")
    @ResponseBody
    public TaotaoResult<Integer> cardSubAcount(){
        TaotaoResult<Integer> result = new TaotaoResult<Integer>();
        result.setData("-150, 余额： " + testService.subAcount("card", 150));
        return  result;
    }

    @RequestMapping(value="/passbookSubAcountMysql")
    @ResponseBody
    public TaotaoResult<Integer> passbookSubAcount() throws TransactionalException{
        TaotaoResult<Integer> result = new TaotaoResult<Integer>();

        result.setData("-200, 余额： " + testService.subAcount("存折", 200));
        return  result;
    }

    @RequestMapping(value="/selectAcountMysql")
    @ResponseBody
    public TaotaoResult<Integer> selectAcount() throws TransactionalException {
        TaotaoResult<Integer> result = new TaotaoResult<Integer>();

        result.setData(testService.selectAcount(""));

        return  result;
    }


}

执行结果：

card...余额：2850.00

card...取出：150...http-apr-8085-exec-38

card...余额：2700.00

存折...取出：200...http-apr-8085-exec-104

存折...余额：2500.00

存折...取出：200...http-apr-8085-exec-73

存折...余额：2300.00

存折...取出：200...http-apr-8085-exec-105

存折...余额：2100.00

存折...取出：200...http-apr-8085-exec-120

存折...余额：1900.00

存折...取出：200...http-apr-8085-exec-39

存折...余额：1700.00

存折...取出：200...http-apr-8085-exec-107

存折...余额：1500.00

card...取出：150...http-apr-8085-exec-108

card...余额：1350.00

card...取出：150...http-apr-8085-exec-116

card...余额：1200.00

card...取出：150...http-apr-8085-exec-117

card...余额：1050.00

存折...取出：200...http-apr-8085-exec-111

存折...余额：850.00

存折...取出：200...http-apr-8085-exec-119

存折...余额：650.00

存折...取出：200...http-apr-8085-exec-115

存折...余额：450.00

存折...取出：200...http-apr-8085-exec-123

存折...余额：250.00

存折...取出：200...http-apr-8085-exec-54

存折...余额：50.00

账户余额不足！余额=50.00

账户余额不足！余额=50.00

账户余额不足！余额=50.00

账户余额不足！余额=50.00

账户余额不足！余额=50.00

账户余额不足！余额=50.00

账户余额不足！余额=50.00

账户余额不足！余额=50.00

账户余额不足！余额=50.00

账户余额不足！余额=50.00

账户余额不足！余额=50.00

账户余额不足！余额=50.00

...余额：50.00

...余额：50.00

...余额：50.00

...余额：50.00

账户余额不足！余额=50.00

账户余额不足！余额=50.00

账户余额不足！余额=50.00

账户余额不足！余额=50.00

...余额：50.00

...余额：50.00

...余额：50.00

账户余额不足！余额=50.00

账户余额不足！余额=50.00

账户余额不足！余额=50.00

...余额：50.00

...余额：50.00

...余额：50.00

账户余额不足！余额=50.00

账户余额不足！余额=50.00

账户余额不足！余额=50.00

...余额：50.00

...余额：50.00

...余额：50.00

账户余额不足！余额=50.00

...余额：50.00

...余额：50.00

...余额：50.00

...余额：50.00

...余额：50.00

...余额：50.00

...余额：50.00

...余额：50.00

...余额：50.00

...余额：50.00

账户余额不足！余额=50.00

...余额：50.00

...余额：50.00

...余额：50.00

...余额：50.00

...余额：50.00

...余额：50.00

...余额：50.00

 

测试用例：

链接：https://pan.baidu.com/s/1YuH8FTu9SX4DxVYNaOL9Lg 密码：4vgr