How does an incoming request to a nodejs server get handled when the event loop is waiting for a DB operation

Question

I have a route in my API, as an example lets call it /users/:userId/updateBalance. This route will fetch the users current balance, add whatever comes from the request and then update the balance with the newly calculated balance. A request like this comes into the server for a specific user every 30 minutes, so until recently, I thought a concurrency issue to be impossible.

What ended up happening is that somewhere, a sent request failed and was only sent again 30 minutes later, about within a second of the other request. The result was that, as I can see it in the database, both of these requests fetched the same balance from the DB and both added their respective amounts. Essentially, the second request actually read a stale balance, as normally it should execute after request 1 has executed.

To give a numerical example for some more clarity, lets say request 1 was to add $2 to the balance, and request 2 was to add $5, and the user had a balance of $10. If the requests act in parallel, the users balance would end at either $12 or $15 depending on whether request 1 or request 2 finished first respectively, because both requests fetch a balance of $10 from the DB. However, obviously the expected behaviour is that we want request 1 to execute, update the users balance to $12, and then request 2 to execute and update the balance from $12 to $17.

To give some better perspective of the overall execution of this process: the request is received, a function is called, the function has to wait for the balance from the DB, the function then calculates the new balance and updates the db, after which execution is completed.

So I have a few questions on this. The first being, how does node handle incoming requests when it is waiting for an asynchronous request like a MySQL database read. Given the results I have observed, I assume that when the first request is waiting for the DB, the second request can commence being processed? Otherwise I am uncertain of how such asynchronous behaviour is experienced within a single threaded environment like node.

Secondly, how do I go about controlling this and preventing it. I had wanted to use a MySQL transaction with a forUpdate lock, but it turns out it seems not possible due to the way the code is currently written. Is there a way to tell node that a certain block of code can not be executed "in parallel"? Or any other alternatives?

Answer 1

The first being, how does node handle incoming requests when it is waiting for an asynchronous request like a MySQL database read

The event loop of nodejs makes this happens, otherwise you'll have a totally sync programm with super-low performances.

• Every single async function invocked in a context will be executed after the context itself has been executed.

• Between the finish of execution of the context and the execution of the async function, other async functions can be scheduled for been executed (this "insertion" is managed by the event loop).

• If an async function is awaited, the remaining code of the context is scheduled somewhere after the execution of the async function.

Is more clear when playing with it. Example 1:

// Expected result: 1, 3, 4, 2    

function asyncFunction(x) {
  // setTimeout as example of async operation
  setTimeout(() => console.log(x), 10)
}

function context() {
  console.log(1)
  asyncFunction(2)
  console.log(3)
}

context()

console.log(4)

Example 2:

// Expected result: 1, 2, 3    

function asyncFunction(x) {
  // Promise as example of async operation
  return new Promise((resolve) => {
    console.log(x)
    resolve()
  })
}

async function context() {
  console.log(1)

  await asyncFunction(2)

  console.log(3)
}

context()

Example 3 (more similar to your situation):

// Expected result: 1, 2, 4, 5, 3, 6

function asyncFunction(x) {
  // Promise as example of async operation
  return new Promise((resolve) => {
    console.log(x)
    resolve()
  })
}

async function context(a, b, c) {
  console.log(a)

  await asyncFunction(b)

  console.log(c)
}

context(1, 2, 3)
context(4, 5, 6)

In your example:

• when the server receive a connection, the execution of the handler is scheduled

• when the handler is executed, it schedule the execution of the query, and the remaining portion of the handler context is scheduled after that

In between scheduled executions everything can happen.

Answer 2

You are right, while node waits for the database query to return, it will handle any incoming requests and start that requests database call before the first one finishes.

The easiest way to prevent this IMO would be to use queues. Instead of processing the balance update directly in the route handler, that route handler could push an event to a queue (in Redis, in AWS SQS, in RabbitMQ etc) and somewhere else in your app (or even in a completely different service) you would have a consumer that listens to new events in that queue. If an update fails, add it back to the beginning of the queue, add some wait time, and then try again.

This way, no matter how many times your first request fails, your balance will be correct, and pending changes to that balance will be in the correct order. In case of an event in the queue failing repeatedly, you could even send an email or a notification to someone to have a look at it, and while the problem is fixed pending changes to the balance will be added to the queue, and once it's fixed, everything will be processed correctly.

You could even read that queue and display information to your user, for instance tell the user the balance has pending updates so it might not be accurate.

Hope this helps!