sql update query not perfectly updating with node js and socket.io

[SaqibMehmood08]

I'm using Socket.io and Node.js for the backend and React Native for the frontend, sending 1000 requests in 100 seconds to deduct 100, 1000 etc from the "beans" column in the users' table. Despite switching to asynchronous code and MySQL2 promises, there are still issues with accurate deductions. Other insertions are perfect. Here is my code:

socket.on('/user/bit/play', async ({ user_id, card, amount }) => {
        try {
    
          const [userResult] = await pool2.execute('SELECT beans FROM users WHERE id = ?', [user_id]);
          const user_beans = userResult[0].beans;
          const [bitResult] = await pool2.execute('SELECT bit_beans FROM users WHERE id = ?', [user_id]);
          const bit_beans = bitResult[0].bit_beans;
          let upcoming_beans=bit_beans+amount;
          if (bit_beans > user_beans ) {
             socket.emit('beansStatus', { message: "You do not have enough Beans" });
            return;
          }
              if (upcoming_beans > user_beans ) {
              socket.emit('beansStatus', { message: user_beans-bit_beans });
              return;
             }
            

          const results = await Promise.all([
            pool2.execute('UPDATE users SET bit_beans = bit_beans - ? WHERE id = ?', [amount, user_id]),
            pool2.execute('INSERT INTO card_bit_users (user_id, card, amount) VALUES (?, ?, ?)', [user_id, card, amount]),
            pool2.execute('INSERT INTO total_user_bit_cards (user_id, card, amount) VALUES (?, ?, ?)', [user_id, card, amount])
          ]);
    
          if (results[0][0].affectedRows === 1 && results[1][0].affectedRows === 1) {
            const totalSum = await updateTotalBitSum(card);
            
           
            // Emit the total sum to the client
            socket.emit('success', { message: `Total sum for card ${card}: ${totalSum}` });
          } else {
            socket.emit('Error', { message: "Transaction failed" });
          }
        } catch (error) {
          // Handle any errors
          console.error(error);
          socket.emit('Error', { message: `Error: ${error.message}` });
        }
});

react native emit is : emit('/user/bit/play', {1,'A',100});

I switched from synchronous to asynchronous programming using MySQL2 promises. I want the "beans" to deduct based on the number I emit. For example, emitting 100 five times should deduct a total of 500 beans but this deduct 400 or 300.

EDIT by @darrachequesne: code formatting

[darrachequesne]

@SaqibMehmood08 hi! I think you need to wrap your queries inside a transaction, in order to prevent any race condition.

References:

• https://dev.mysql.com/doc/refman/8.0/en/commit.html
• https://github.com/sidorares/node-mysql2/blob/master/lib/connection.js#L730-L740

Note: this issue does not seem related to Socket.IO, please use StackOverflow the next time 👼