mysql并发 node_Nodejs模拟并发，尝试的两种解决方案

一、准备数据库表

创建商品库存表 db_stock ，插入一条数据

DROP TABLE IF EXISTS`db_stock`;CREATE TABLE`db_stock` (

`id`int(11) NOT NULLAUTO_INCREMENT,

`goods_id`varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品id',

`inventory_total`int(11) NULL DEFAULT NULL COMMENT '总库存',

`inventory_remain`int(11) NULL DEFAULT NULL COMMENT '剩余库存',PRIMARY KEY(`id`) USING BTREE

) ENGINE= InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '记录实时库存表' ROW_FORMAT =Dynamic;--------------------------------Records of db_stock------------------------------

INSERT INTO `db_stock` VALUES (1, 'goods_01', 100, 100);SET FOREIGN_KEY_CHECKS = 1;

创建出库信息表 db_checkout

DROP TABLE IF EXISTS`db_checkout`;CREATE TABLE`db_checkout` (

`id`int(11) NOT NULLAUTO_INCREMENT,

`goods_id`varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品id',

`opt_num`int(11) NULL DEFAULT NULL COMMENT '操作数量',

`inventory_remain`int(11) NULL DEFAULT NULL COMMENT '剩余库存',

`create_time`datetime(0) NULL DEFAULT NULL COMMENT '创建时间',PRIMARY KEY(`id`) USING BTREE

) ENGINE= InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '出库记录表' ROW_FORMAT =Dynamic;SET FOREIGN_KEY_CHECKS = 1;

查看数据库

mysql> select * fromdb_stock;+----+----------+-----------------+------------------+

| id | goods_id | inventory_total | inventory_remain |

+----+----------+-----------------+------------------+

| 1 | goods_01 | 100 | 100 |

+----+----------+-----------------+------------------+

1 row in set (0.00sec)

mysql> select * fromdb_checkout;

Emptyset (0.00sec)

mysql>

二、准备Nodejs接口(基于Koa2)

接口说明：

url　　　　dm/testConcurrentOrder

method 　 get

data　　　initDB //首次初始化数据

goods_id //商品id

opt_num //出库数量

router.js

const router = require('koa-router')();

const dm_model= require("../models/dm_model");

router.preffix("/dm");/**

* 测试并发

* testConcurrentOrder*/router.get('/testConcurrentOrder', async function(ctx) {

let reqParams=ctx.reqParams;try{

ctx.body=await dm_model.testConcurrentOrder(reqParams);

}catch(e) {

ctx.body={

success:false,

msg: `出库异常: ${e.toString()}`,

}

}

});

module.exports= router;

model.js

let db = require("../utils/mysql_util").db;let moment = require("moment");

let model={};/**

* 测试并发

* @param reqParams

* @returns {Promise}*/model.testConcurrentOrder= async function(reqParams) {

let initDB= !!reqParams.initDB || 0;//是否初始化db

let goods_id= reqParams.goods_id || "goods_01";//商品id

let opt_num = reqParams.opt_num || 1;//出库数量

if(initDB) {//清除数据

await db.query(`TRUNCATE db_stock`);

await db.query(`TRUNCATE db_checkout`);//插入库存数据

await db.query(`INSERT INTO db_stock(goods_id, inventory_total, inventory_remain) VALUES ('${goods_id}', 100, 100)`);return{

success:true,

msg:'初始化DB成功',

}

}else{

let tran=await db.beginTransaction();

// 查询剩余库存

let querySql =`SELECT

t.inventory_total,

t.inventory_remain

FROM

db_stock t

WHERE1 = 1AND t.goods_id= ?`;try{

let result={};

let queryResult=await tran.queryOne(querySql, [goods_id]);if (!queryResult) {

result={

success:false,

msg: `无法匹配商品id：${goods_id}`

}

}else{

let inventory_remain= queryResult.inventory_remain - 1;//新增出库记录

let checkoutSql = `INSERT INTO db_checkout(goods_id, opt_num, inventory_remain, create_time) VALUES (?, ?, ?, ?)`;

await tran.query(checkoutSql, [goods_id, opt_num,