4

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

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

Example:

example of t_account table

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

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

I'm trying to get balance of 400 NOT 300

For Example:

  1. Starting Balance = 100
  2. 1st new post = 100, => balance =200
  3. 2nd new post = 200, => balance =400

Code Example:

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 Console Logs

enter image description here enter image description here

The last balance not right showing 300 instead of 400.

What is going wrong here?

Improve this question

2 Answers 2

Reset to default
3

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

From docs

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

oh my god you are my savior....i think the problem is in the node.js (concurrent process)....actually in the mysql. i don't know about "FOR UPDATE" before.. thank you verymuch
Happy to help!! Just to add more context that this is not just node async issue. Think about a scenario where you app is running on multiple servers and you can enter into similar race conditions.
Yeah, MySQL has a ton of terrible "out-of-the-box" settings. Defaults your Transaction level Giving READ HIGHER Priority than writes and Is why it was part of my answer. Don't forget to check & TRY to use SET @@GLOBAL.SQL_MODE = "TRADITIONAL"; that acts more appropriate and will NOT leave you confused. Default MySQL lets you insert NULLs into NOT NULL columns or provides you with a zero dates when you forgot to use datetime instead of timestamp or visa-versa.
1

Going off of your provided info, and not knowing more about NodeJS or your OS, Versions, setup etc. This is my thought process as what each issue I see.

Check The Basics

  1. As silly as this may actually be, there are so many unknown factors.

    • I see you are running this on your own computer,
    • If you run this 10+ times in a row, is the behaviour the same?
      • If the end result is different, Check processes running close.
    • If possible,
      • please do your basic computer check (reboot, close other programs)
      • if possible, check it on another computer too

    After that is out of the way check:

Check saveBalance Function

  1. Your saveBalance',async function Maybe the cause of the double SELECT...SELECT double INSERT...INSERT
    • My Gut tells me this is the issue based on the images, the order is wrong.

Check Database Settings

  1. If the saveBalance',async function is fine. I would check your Database settings as based on your MySQL Console Logs & that you are using localhost

    • Your image shows SELECT...SELECT...INSERT...INSERT...
    • But it should be SELECT...INSERT...**SELECT...**INSERT...

  2. This Might be due to an issue with your MySQL Database Settings.

SQL Code:

SELECT @@GLOBAL.TX_ISOLATION;

It also, might interest you to use START TRANSACTIONS; But be sure to enable multistatements in your config. and this is also directly tied to the TX_ISOLATION as well.

My example would modify your Query like so:

let SQL2="START TRANSACTION; INSERT INTO t_account(no,balance) VALUES('"+req.body.no+"',"+newBalance+"); COMMIT;";

Try Other Accepted Code as your own

Lastly, I also recommend reading 'Approach to multiple MySQL queries with Node.js - One should avoid the pyramid of doom'

I hope this helps.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.