CODEWITHSUNDEEP
sqlnode.jsrace-condition
user2073973
user2073973

Reputation: 584

Node.js SQL race conditions

I would like to know how you can prevent race conditions in NodeJS when doing IO. I have been reading a bit, and everybody insists that race conditions are impossible because NodeJS is single threaded.

Lets look at the following pseudo code:

async decreaseUserBalance(userId, amount) {
  const currentBalance = await sql.queryScalar('SELECT balance FROM user WHERE userid=?', [userId]);
  await sql.query('UPDATE user SET balance = ? WHERE userid = ?', [currentBalance - amount, userId]);
}

Lets say the database node is connected to is under heavy load, and takes some time to complete every request, and this function gets called every time a user clicks a buy button on some item.

What would happen when the user starts spamming this button or makes a bot to send the buy request? In my understanding, the first request will perform the SELECT query, and resume the execution. Now, since the user is spamming the button, next requests come in, executing the same function. Now you have several SELECT statements waiting to be completed. Now, a couple of select statements finish execution and perform the callback, thus now a couple callbacks are executing the UPDATE statement, all with the same balance. This would mean that if the starting balance was 5, all of them would decrease var amount from the last known balance. So basically it doesn't matter how often you execute this query simultaneously, the first bunch of requests will all get the same value from the SELECT query and update it to some bogus.

Sorry if my explanation is a bit vague, but I believe this is a very real problem, that I doubt many people take into consideration.

So to solve this, does Node support something like mutexes? From what I've read MongoDB doesn't support table locking, so locking would only be an option to SQL.

EDIT:

I know I could have done this example in 1 query, and that would have solved it, but lets say that wouldn't be possible. How would you solve this then?

EDIT 2:

Okay, lets take a look at this example:

  async tryBuyItem(userId, itemId, price) {

//Do we still have enough balance?

const currentBalance = await sql.queryScalar('SELECT balance FROM user WHERE userid=?', [userId]);

if (currentBalance >= price) {
  await sql.query('UPDATE user SET balance = balance - ? WHERE userid = ?', [price, userId]);
  await sql.query('INSERT INTO user_items (userid, itemid) VALUES (?, ?)', [userId, itemId]);

  return true;
} else {
  return false;
}
}

Upvotes: 2

Views: 747

Answers (2)

Nilasis Sen
Nilasis Sen

Reputation: 309

if its a payment kind of situation where you have to update and insert many document and your server will get many hits in few mins,either you can use a var q = async.queue(function (task, callback) { function(){} }, 5); process or put the whole function into a async.waterfall module which execute one by one step.

https://caolan.github.io/async/docs.html#waterfall

Upvotes: 1

ponury-kostek
ponury-kostek

Reputation: 8070

You can simply do

sql.query('UPDATE user SET balance = balance - ? WHERE userid = ?', [amount, userId]);

and here is no race condition and no transaction used.

Upvotes: 1

Related Questions