Nodejs模拟并发，尝试的两种解决方案
一、准备数据库表
创建商品库存表 db_stock ，插入一条数据
DROP TABLE IF EXISTS `db_stock`; CREATE TABLE `db_stock` ( `id` int(11) NOT NULL AUTO_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 NULL AUTO_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 * from db_stock; +----+----------+-----------------+------------------+ | id | goods_id | inventory_total | inventory_remain | +----+----------+-----------------+------------------+ | 1 | goods_01 | 100 | 100 | +----+----------+-----------------+------------------+ 1 row in set (0.00 sec) mysql> select * from db_checkout; Empty set (0.00 sec) 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<{msg: string, success: boolean}>}
*/
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 WHERE 1 = 1 AND 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, inventory_remain, new Date()]); // 更新库存信息 let updateStockSql = `UPDATE db_stock t SET inventory_remain = ? WHERE t.goods_id = ?`; // 更新库存 await tran.query(updateStockSql, [inventory_remain, goods_id]); result = { success: true, msg: '出库成功', } } await tran.commit(); return result; } catch (e) { await tran.rollback(); throw e; } } }; module.exports = model;
三、并发测试
总共请求20次,20个并发同时请求
C:\WINDOWS\system32>ab.exe -n 20 -c 20 http://localhost:3000/dm/testConcurrentOrder This is ApacheBench, Version 2.3 <$Revision: 1843412 $> Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/ Licensed to The Apache Software Foundation, http://www.apache.org/ Benchmarking localhost (be patient).....done Server Software: Server Hostname: localhost Server Port: 3000 Document Path: /dm/testConcurrentOrder Document Length: 228 bytes Concurrency Level: 20 Time taken for tests: 24.284 seconds Complete requests: 20 Failed requests: 0 Total transferred: 7420 bytes HTML transferred: 4560 bytes Requests per second: 0.82 [#/sec] (mean) Time per request: 24284.077 [ms] (mean) Time per request: 1214.204 [ms] (mean, across all concurrent requests) Transfer rate: 0.30 [Kbytes/sec] received Connection Times (ms) min mean[+/-sd] median max Connect: 0 0 0.4 0 1 Processing: 1590 2674 4696.9 1638 22629 Waiting: 1589 2673 4696.2 1637 22625 Total: 1590 2674 4696.9 1638 22629 Percentage of the requests served within a certain time (ms) 50% 1638 66% 1644 75% 1648 80% 1651 90% 1655 95% 22629 98% 22629 99% 22629 100% 22629 (longest request) C:\WINDOWS\system32>
查看并发请求后，数据库表的信息
mysql> select * from db_stock; +----+----------+-----------------+------------------+ | id | goods_id | inventory_total | inventory_remain | +----+----------+-----------------+------------------+ | 1 | goods_01 | 100 | 97 | +----+----------+-----------------+------------------+ 1 row in set (0.00 sec) mysql> select * from db_checkout; +----+----------+---------+------------------+---------------------+ | id | goods_id | opt_num | inventory_remain | create_time | +----+----------+---------+------------------+---------------------+ | 61 | goods_01 | 1 | 99 | 2019-10-09 17:31:01 | | 62 | goods_01 | 1 | 98 | 2019-10-09 17:31:01 | | 63 | goods_01 | 1 | 98 | 2019-10-09 17:31:01 | | 64 | goods_01 | 1 | 98 | 2019-10-09 17:31:01 | | 65 | goods_01 | 1 | 98 | 2019-10-09 17:31:01 | | 66 | goods_01 | 1 | 98 | 2019-10-09 17:31:01 | | 67 | goods_01 | 1 | 98 | 2019-10-09 17:31:01 | | 68 | goods_01 | 1 | 98 | 2019-10-09 17:31:01 | | 69 | goods_01 | 1 | 98 | 2019-10-09 17:31:01 | | 70 | goods_01 | 1 | 98 | 2019-10-09 17:31:01 | | 71 | goods_01 | 1 | 98 | 2019-10-09 17:31:01 | | 72 | goods_01 | 1 | 97 | 2019-10-09 17:31:01 | | 73 | goods_01 | 1 | 97 | 2019-10-09 17:31:01 | | 74 | goods_01 | 1 | 97 | 2019-10-09 17:31:01 | | 75 | goods_01 | 1 | 97 | 2019-10-09 17:31:01 | | 76 | goods_01 | 1 | 97 | 2019-10-09 17:31:01 | | 77 | goods_01 | 1 | 97 | 2019-10-09 17:31:01 | | 78 | goods_01 | 1 | 97 | 2019-10-09 17:31:01 | | 79 | goods_01 | 1 | 97 | 2019-10-09 17:31:01 | | 80 | goods_01 | 1 | 97 | 2019-10-09 17:31:01 | +----+----------+---------+------------------+---------------------+ 20 rows in set (0.00 sec) mysql>
结果：并发请求20次，由于代码和数据库没有控制并发，导致数据错误。
四：解决方案
1、加行锁
在查询剩余库存的时候调整sql为
// 查询剩余库存 let querySql = `SELECT t.inventory_total, t.inventory_remain FROM db_stock t WHERE 1 = 1 AND t.goods_id = ? for update`;
这个是基于数据库的行锁实现，对于单节点Mysql实现没有难度，测试结果就不放了。
2、使用redis分布式锁（基于Redlock的实现）
调整model.js
let db = require("../utils/mysql_util").db;
let moment = require("moment");
let {async_redis_client, redlock} = require("../utils/redis_util");
let model = {};
/**
* 测试并发
* @param reqParams
* @returns {Promise<{msg: string, success: boolean}>}
*/
model.testConcurrentOrder = async function (reqParams) {
// 之前本人有个误区，设置资源锁的key为 `goods_${inventory_remain}`，但是仍然有写入错误的数据。
// 思考了一下，觉得是很多请求同时进来，从加锁->释放锁相当于进入队列，前一个操作完成后释放了锁，后一个才要开始加锁。
// 假如有100个请求同时进来，其中50个请求在加锁之前查询到的库存都是100，提交事务的出库信息都是剩余库存99，那么有49条数据都会形成脏数据。
// 所以这里必须设置资源锁的key一致，只要有一个资源还没有释放锁，其他资源就不允许进行查询库存的操作，问题解决。
// 上锁的资源
let resource_lock_key = `redislock_goods`;
// 上锁资源5s，操作完成或者到时间后自动释放
let resource_ttl = 5 * 1000;
// 开始加锁
let lock = await redlock.lock(resource_lock_key, resource_ttl);
console.log(resource_lock_key, lock.value);
console.log(moment().format('YYYY-MM-DD HH:mm:ss:SSS'));
let initDB = !!reqParams.initDB || 0;// 是否初始化db
let goods_id = reqParams.goods_id || "goods_01";// 商品id
let opt_num = reqParams.opt_num || 1;// 出库数量
let result = {};
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)`);
result = {
success: true,
msg: '初始化DB成功',
};
} else {
let querySql = `SELECT
t.inventory_total,
t.inventory_remain
FROM
db_stock t
WHERE
1 = 1
AND t.goods_id = ?`;
let queryResult = await db.queryOne(querySql, [goods_id]);
if (!queryResult) {
result = {
success: false,
msg: `无法匹配商品id：${goods_id}`
}
} else {
// 当前库存
let inventory_remain = queryResult.inventory_remain;
// 等待设置的新库存
let inventory_remain_new = inventory_remain - 1;
let tran = await db.beginTransaction();
try {
// 新增出库记录
let checkoutSql = `INSERT INTO db_checkout(goods_id, opt_num, inventory_remain, create_time) VALUES (?, ?, ?, ?)`;
await tran.query(checkoutSql, [goods_id, opt_num, inventory_remain_new, new Date()]);
// 更新库存信息
let updateStockSql = `UPDATE db_stock t SET inventory_remain = ? WHERE t.goods_id = ?`;
// 更新库存
await tran.query(updateStockSql, [inventory_remain_new, goods_id]);
await tran.commit();
result = {
success: true,
msg: '出库成功',
}
} catch (e) {
await tran.rollback();
// 抛出异常之前，及时释放资源锁
await lock.unlock();
throw e;
}
}
}
if (lock) {
// 及时释放资源锁
await lock.unlock();
}
return result;
};
module.exports = model;
附上redis_util.js的代码
// Redis单节点 let date_util = require("../utils/date_util"); let RedLock = require("redlock"); // redis let redis = require("redis"); // async_redis let async_redis = require("async-redis"); // redis_option let redis_option = { //redis服务器的ip地址 host: '127.0.0.1', //redis服务器的端口 port: '6379', // 授权密码，修改redis.windows.conf requirepass 1234，密码错误报错: NOAUTH Authentication required password: '1234', // redis select $index，默认0 db: '0', // reply number => string 默认null string_numbers: null, // replay strings => buffer，默认false return_buffers: false, // 是否检测缓冲区，默认false detect_buffers: false, // 长链接，默认true socket_keepalive: true, // 长链接延迟时间，默认0 socket_initialdelay: 0, // 禁用ready检查，默认false no_ready_check: false, // 启用离线命令队列，默认true enable_offline_queue: true, // // @Deprecated 重连最大延迟，默认null // retry_max_delay: null, // // @Deprecated 连接超时时间，默认60*60*1000，默认1h // connect_timeout: 60 * 60 * 1000, // // @Deprecated 最大连接次数，默认0，设置为1将阻止任何重新连接尝试 // max_attempts: 0, // 默认false, 如果设置为true，则在重新建立连接后，将重试连接丢失时未执行的所有命令。如果使用状态更改命令（例如incr），请小心使用此命令。这在使用阻塞命令时特别有用。 retry_unfulfilled_commands: false, retry_strategy: function (options) { if (options.error && options.error.code === 'ECONNREFUSED') { // 拒绝连接 let errMsg = 'The server refused the connection'; console.log(date_util.format(), 'retry_strategy', errMsg); return new Error(errMsg); } // 默认 60*60*1000 if (options.total_retry_time > 60 * 60 * 1000) { // 超过最大重试时间 let errMsg = 'Retry time exhausted'; console.log(date_util.format(), 'retry_strategy', errMsg); return new Error(errMsg); } // 默认10 if (options.attempt > 10) { // 超过最大重试次数 let errMsg = 'maximum connection attempts exceeded'; console.log(date_util.format(), 'retry_strategy', errMsg); return new Error(errMsg); } // reconnect after return Math.min(options.attempt * 1000, 3000); } }; // redis_client let redis_client = redis.createClient(redis_option); // async_redis_client let async_redis_client = async_redis.createClient(redis_option); redis_client.on("error", function (err) { console.log(date_util.format(date_util.pattern().ymdhmsSSS), err.toString()); }); async_redis_client.on("error", function (err) { console.log(date_util.format(date_util.pattern().ymdhmsSSS), err.toString()); }); // NOTE: 这里只能使用redis_client let redlock = new RedLock([redis_client]); redlock.on('clientError', function (err) { console.error(date_util.format(date_util.pattern().ymdhmsSSS), 'A redis error has occurred:', err); }); module.exports = { redis_client, async_redis_client, redlock };
ab测试
C:\WINDOWS\system32>ab.exe -n 95 -c 95 http://localhost:3000/dm/testConcurrentOrder This is ApacheBench, Version 2.3 <$Revision: 1843412 $> Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/ Licensed to The Apache Software Foundation, http://www.apache.org/ Benchmarking localhost (be patient).....done Server Software: Server Hostname: localhost Server Port: 3000 Document Path: /dm/testConcurrentOrder Document Length: 46 bytes Concurrency Level: 95 Time taken for tests: 1.867 seconds Complete requests: 95 Failed requests: 0 Total transferred: 17860 bytes HTML transferred: 4370 bytes Requests per second: 50.88 [#/sec] (mean) Time per request: 1867.041 [ms] (mean) Time per request: 19.653 [ms] (mean, across all concurrent requests) Transfer rate: 9.34 [Kbytes/sec] received Connection Times (ms) min mean[+/-sd] median max Connect: 0 0 0.2 0 1 Processing: 29 932 437.8 909 1840 Waiting: 21 931 437.9 908 1839 Total: 29 932 437.8 909 1840 Percentage of the requests served within a certain time (ms) 50% 907 66% 1123 75% 1257 80% 1346 90% 1578 95% 1727 98% 1783 99% 1840 100% 1840 (longest request) C:\WINDOWS\system32>
Mysql查询结果：
mysql> select * from db_stock; +----+----------+-----------------+------------------+ | id | goods_id | inventory_total | inventory_remain | +----+----------+-----------------+------------------+ | 1 | goods_01 | 100 | 5 | +----+----------+-----------------+------------------+ 1 row in set (0.00 sec) mysql> select * from db_checkout; +----+----------+---------+------------------+---------------------+ | id | goods_id | opt_num | inventory_remain | create_time | +----+----------+---------+------------------+---------------------+ | 1 | goods_01 | 1 | 99 | 2019-10-10 18:36:15 | | 2 | goods_01 | 1 | 98 | 2019-10-10 18:36:15 | | 3 | goods_01 | 1 | 97 | 2019-10-10 18:36:15 | | 4 | goods_01 | 1 | 96 | 2019-10-10 18:36:15 | | 5 | goods_01 | 1 | 95 | 2019-10-10 18:36:15 | | 6 | goods_01 | 1 | 94 | 2019-10-10 18:36:15 | | 7 | goods_01 | 1 | 93 | 2019-10-10 18:36:15 | | 8 | goods_01 | 1 | 92 | 2019-10-10 18:36:15 | | 9 | goods_01 | 1 | 91 | 2019-10-10 18:36:15 | | 10 | goods_01 | 1 | 90 | 2019-10-10 18:36:15 | | 11 | goods_01 | 1 | 89 | 2019-10-10 18:36:15 | | 12 | goods_01 | 1 | 88 | 2019-10-10 18:36:15 | | 13 | goods_01 | 1 | 87 | 2019-10-10 18:36:15 | | 14 | goods_01 | 1 | 86 | 2019-10-10 18:36:15 | | 15 | goods_01 | 1 | 85 | 2019-10-10 18:36:15 | | 16 | goods_01 | 1 | 84 | 2019-10-10 18:36:15 | | 17 | goods_01 | 1 | 83 | 2019-10-10 18:36:15 | | 18 | goods_01 | 1 | 82 | 2019-10-10 18:36:15 | | 19 | goods_01 | 1 | 81 | 2019-10-10 18:36:15 | | 20 | goods_01 | 1 | 80 | 2019-10-10 18:36:15 | | 21 | goods_01 | 1 | 79 | 2019-10-10 18:36:15 | | 22 | goods_01 | 1 | 78 | 2019-10-10 18:36:15 | | 23 | goods_01 | 1 | 77 | 2019-10-10 18:36:15 | | 24 | goods_01 | 1 | 76 | 2019-10-10 18:36:15 | | 25 | goods_01 | 1 | 75 | 2019-10-10 18:36:15 | | 26 | goods_01 | 1 | 74 | 2019-10-10 18:36:15 | | 27 | goods_01 | 1 | 73 | 2019-10-10 18:36:15 | | 28 | goods_01 | 1 | 72 | 2019-10-10 18:36:15 | | 29 | goods_01 | 1 | 71 | 2019-10-10 18:36:15 | | 30 | goods_01 | 1 | 70 | 2019-10-10 18:36:15 | | 31 | goods_01 | 1 | 69 | 2019-10-10 18:36:15 | | 32 | goods_01 | 1 | 68 | 2019-10-10 18:36:15 | | 33 | goods_01 | 1 | 67 | 2019-10-10 18:36:15 | | 34 | goods_01 | 1 | 66 | 2019-10-10 18:36:15 | | 35 | goods_01 | 1 | 65 | 2019-10-10 18:36:15 | | 36 | goods_01 | 1 | 64 | 2019-10-10 18:36:15 | | 37 | goods_01 | 1 | 63 | 2019-10-10 18:36:15 | | 38 | goods_01 | 1 | 62 | 2019-10-10 18:36:15 | | 39 | goods_01 | 1 | 61 | 2019-10-10 18:36:15 | | 40 | goods_01 | 1 | 60 | 2019-10-10 18:36:15 | | 41 | goods_01 | 1 | 59 | 2019-10-10 18:36:15 | | 42 | goods_01 | 1 | 58 | 2019-10-10 18:36:15 | | 43 | goods_01 | 1 | 57 | 2019-10-10 18:36:15 | | 44 | goods_01 | 1 | 56 | 2019-10-10 18:36:15 | | 45 | goods_01 | 1 | 55 | 2019-10-10 18:36:15 | | 46 | goods_01 | 1 | 54 | 2019-10-10 18:36:16 | | 47 | goods_01 | 1 | 53 | 2019-10-10 18:36:16 | | 48 | goods_01 | 1 | 52 | 2019-10-10 18:36:16 | | 49 | goods_01 | 1 | 51 | 2019-10-10 18:36:16 | | 50 | goods_01 | 1 | 50 | 2019-10-10 18:36:16 | | 51 | goods_01 | 1 | 49 | 2019-10-10 18:36:16 | | 52 | goods_01 | 1 | 48 | 2019-10-10 18:36:16 | | 53 | goods_01 | 1 | 47 | 2019-10-10 18:36:16 | | 54 | goods_01 | 1 | 46 | 2019-10-10 18:36:16 | | 55 | goods_01 | 1 | 45 | 2019-10-10 18:36:16 | | 56 | goods_01 | 1 | 44 | 2019-10-10 18:36:16 | | 57 | goods_01 | 1 | 43 | 2019-10-10 18:36:16 | | 58 | goods_01 | 1 | 42 | 2019-10-10 18:36:16 | | 59 | goods_01 | 1 | 41 | 2019-10-10 18:36:16 | | 60 | goods_01 | 1 | 40 | 2019-10-10 18:36:16 | | 61 | goods_01 | 1 | 39 | 2019-10-10 18:36:16 | | 62 | goods_01 | 1 | 38 | 2019-10-10 18:36:16 | | 63 | goods_01 | 1 | 37 | 2019-10-10 18:36:16 | | 64 | goods_01 | 1 | 36 | 2019-10-10 18:36:16 | | 65 | goods_01 | 1 | 35 | 2019-10-10 18:36:16 | | 66 | goods_01 | 1 | 34 | 2019-10-10 18:36:16 | | 67 | goods_01 | 1 | 33 | 2019-10-10 18:36:16 | | 68 | goods_01 | 1 | 32 | 2019-10-10 18:36:16 | | 69 | goods_01 | 1 | 31 | 2019-10-10 18:36:16 | | 70 | goods_01 | 1 | 30 | 2019-10-10 18:36:16 | | 71 | goods_01 | 1 | 29 | 2019-10-10 18:36:16 | | 72 | goods_01 | 1 | 28 | 2019-10-10 18:36:16 | | 73 | goods_01 | 1 | 27 | 2019-10-10 18:36:16 | | 74 | goods_01 | 1 | 26 | 2019-10-10 18:36:16 | | 75 | goods_01 | 1 | 25 | 2019-10-10 18:36:16 | | 76 | goods_01 | 1 | 24 | 2019-10-10 18:36:16 | | 77 | goods_01 | 1 | 23 | 2019-10-10 18:36:16 | | 78 | goods_01 | 1 | 22 | 2019-10-10 18:36:16 | | 79 | goods_01 | 1 | 21 | 2019-10-10 18:36:16 | | 80 | goods_01 | 1 | 20 | 2019-10-10 18:36:16 | | 81 | goods_01 | 1 | 19 | 2019-10-10 18:36:16 | | 82 | goods_01 | 1 | 18 | 2019-10-10 18:36:16 | | 83 | goods_01 | 1 | 17 | 2019-10-10 18:36:16 | | 84 | goods_01 | 1 | 16 | 2019-10-10 18:36:16 | | 85 | goods_01 | 1 | 15 | 2019-10-10 18:36:16 | | 86 | goods_01 | 1 | 14 | 2019-10-10 18:36:16 | | 87 | goods_01 | 1 | 13 | 2019-10-10 18:36:16 | | 88 | goods_01 | 1 | 12 | 2019-10-10 18:36:16 | | 89 | goods_01 | 1 | 11 | 2019-10-10 18:36:16 | | 90 | goods_01 | 1 | 10 | 2019-10-10 18:36:16 | | 91 | goods_01 | 1 | 9 | 2019-10-10 18:36:16 | | 92 | goods_01 | 1 | 8 | 2019-10-10 18:36:16 | | 93 | goods_01 | 1 | 7 | 2019-10-10 18:36:16 | | 94 | goods_01 | 1 | 6 | 2019-10-10 18:36:16 | | 95 | goods_01 | 1 | 5 | 2019-10-10 18:36:16 | +----+----------+---------+------------------+---------------------+ 95 rows in set (0.00 sec) mysql>
