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:
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:
Starting Balance = 100
1st new post = 100, => balance =200
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
The last balance not right showing 300 instead of 400.
What is going wrong here?
解决方案
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.