Node.js SQL race conditions

Question

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;
}
}

It's true that you can't avoid race conditions at that level. That's why SQL databases are transactional! — E_net4 - Mr Downvoter
@E_net4 how exactly would a transaction fix this though? Even if this function would have been encapsulated in a transaction, you would still be able to execute it async several times right? The problem would still remain — user2073973
The database can handle concurrent transactions without losing consistency. If you understand that transactions are ACID, then it's clear why simultaneous requests are not a problem. And it's not a particular problem with Node.js. Any other technology could perform concurrent queries to the database with enough processes. — E_net4 - Mr Downvoter
@user2073973, your question is a bit too theoretical. For the example you posted, ponury-kostek's solution is the right one. For something more complex, perhaps you would need to use table-locking, or transactions. Or you might have to slightly change the structure of your table to allow more atomic operations. There are solutions to what you describe but no generic one, it will depend on what you want to achieve. — laurent

ponury-kostek ponury-kostek · Accepted Answer · 2017-06-30T08:39:51

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.

Node.js SQL race conditions

2 Answers