NodeJS如何处理对MySQL的并发请求 [英] NodeJS How To Handle Concurrent Request To MySQL

问题描述

我在使用NodeJS时遇到问题. 当我尝试发布/更新

I am having a problem with NodeJS. When I am trying to post/update

• One balance
• One account
• 从Two different users
发布
• at the same time.

• One balance
• One account
• posting from Two different users
• at the same time.

示例:

我有带有ExpressJS的API，用于将数据发布到t_account表中，如下所示.

I have API with ExpressJS for post data to the t_account table like this.

每次交易后，我需要先从max(id)获取最新余额.

I need to get latest balance first from max(id) after each transaction.

我正试图获得400 NOT 300的余额

I'm trying to get balance of 400 NOT 300

例如:

1. 期初余额= 100
2. 第一个新帖子= 100，=>余额= 200
3. 第二个新职位= 200，=>余额= 400

代码示例:

router.post('/saveBalance',async function (req, res) {
try {
    let SQL="SELECT balance FROM t_account WHERE id=(SELECT max(id) FROM t_account WHERE no='"+req.body.no+"')";
    let queryResult=await db.myexec(SQL);
    let newBalance=queryResult[0].balance+req.body.balance;
    let SQL2="INSERT INTO t_account(no,balance) VALUES('"+req.body.no+"',"+newBalance+")";
    let queryResult2=await db.myexec(SQL2);
    res.status(200).json(  {
        error:"false",
        code:"00",
        message:"Balance Bank is saved",
        })
} catch (error) {
    res.status(400).json(  {
        error:"true",
        code:"03",
        message:"Balance Bank failed to saved",
        })
 }})

问题是当两个用户同时发布数据时，我得到了不正确的余额.

The problem is when two user post data in the same time, I get the incorrect balance.

初始化交易的代码示例:

Code Example of Initializing my Transactions:

const axios = require('axios');
axios.post('http://localhost:3000/rbank/saveBalance/', 
 {
    "no":"11",
    "balance":100
  }
 )
axios.post('http://localhost:3000/rbank/saveBalance/', 
 {
    "no":"11",
    "balance":200
 }
)

MySQL控制台日志

最后一个余额不正确，显示300，而不是400.

The last balance not right showing 300 instead of 400.

这是怎么回事?

推荐答案

您需要锁定正在读取的行，以便在其他任何人都可以读取它之前更新此行.查看 innodb-locking-reads ，特别是for update

You need to lock the row that you are reading so that you can update this row before anyone else can read it. Look at innodb-locking-reads, specifically for update

来自文档

使用SELECT FOR UPDATE锁定更新行仅在以下情况下适用 自动提交被禁用(通过使用START开始事务 TRANSACTION或将autocommit设置为0.如果启用了autocommit， 符合规范的行未锁定.

Locking of rows for update using SELECT FOR UPDATE only applies when autocommit is disabled (either by beginning transaction with START TRANSACTION or by setting autocommit to 0. If autocommit is enabled, the rows matching the specification are not locked.

这篇关于NodeJS如何处理对MySQL的并发请求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！